Public Function GetPrice(url As String)
Dim x(1 To 1, 1 To 2)
Dim XMLHTTP As Object, html As HTMLDocument
Set XMLHTTP = CreateObject("MSXML2.serverXMLHTTP")
XMLHTTP.Open "GET", url, False
XMLHTTP.setRequestHeader "Content-Type", "text/xml"
XMLHTTP.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; rv:25.0) Gecko/20100101 Firefox/25.0"
XMLHTTP.send
Set html = CreateObject("htmlfile")
html.body.innerHTML = XMLHTTP.ResponseText
x(1, 1) = html.getElementsByClassName("product_title entry-title")(0).innerText
Set bb = html.getElementsByClassName("woocommerce-price organique-woo-price")(0).getElementsByTagName("meta")
'x(1, 2) = bb(1).Content & " " & bb(0).Content 'include currency designation as an alternative to line below.
x(1, 2) = CDbl(bb(0).Content)
GetPrice = x
End Function
Sub blah()
For Each cll In Selection.Cells
cll.Offset(, 1).Resize(, 2).Value = GetPrice(cll.Value)
Next cll
End Sub
So am I.ok. thanks a lot. BTW I am using Excel 2010.
Yes, as I said: "which will put the names and prices to the right of the currently selected cells (which should contain the urls)."Code:Sub blah() For Each cll In Selection.Cells cll.Offset(, 1).Resize(, 2).Value = GetPrice(cll.Value) Next cll End Sub
Through this macro I can update name and price. right?
Sub blah2()
Dim x(1 To 1, 1 To 2)
url = "https://demo.proteusthemes.com/organique/product/bio-crispy-flakes/"
Dim XMLHTTP As Object, html As HTMLDocument
Set XMLHTTP = CreateObject("MSXML2.serverXMLHTTP")
XMLHTTP.Open "GET", url, False
XMLHTTP.setRequestHeader "Content-Type", "text/xml"
XMLHTTP.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; rv:25.0) Gecko/20100101 Firefox/25.0"
XMLHTTP.send
Set html = CreateObject("htmlfile")
html.body.innerHTML = XMLHTTP.ResponseText
x(1, 1) = html.getElementsByClassName("product_title entry-title")(0).innerText
Set bb = html.getElementsByClassName("woocommerce-price organique-woo-price")(0).getElementsByTagName("meta")
'x(1, 2) = bb(1).Content & " " & bb(0).Content
x(1, 2) = CDbl(bb(0).Content)
MsgBox x(1, 1)
MsgBox x(1, 2)
MsgBox bb(1).Content
End Sub
I didn't ask you to add it - it shouldn't be necessary. I just wanted to know whether there was an entry for it, which would indicate to me whether MSXML was on your machine.Added XML reference 6.0