gb123
06-27-2018, 03:02 PM
Hi all
Very new to the art of VBA and trying to develop some web scraping over a supermarket site to return current prices from a spreadsheet list of items. I have the below but can't get the data to return properly - am seeing the Runtime Error 438 at the point I'm trying to get data from the class element "pricePerUnit". Any help gratefully received!
Thanks
Sub test()
Dim objIE As InternetExplorer 'special object variable representing the IE browser
Dim aEle As HTMLLinkElement 'special object variable for an <a> (link) element
Dim bEle As HTMLLinkElement 'special object variable for an <b> (link) element
Dim y As Integer 'integer variable we'll use as a counter
Dim result As String 'string variable that will hold our result link
'initiating a new instance of Internet Explorer and asigning it to objIE
Set objIE = New InternetExplorer
'make IE browser visible (False would allow IE to run in the background)
objIE.Visible = True
'navigate IE to this web page (a pretty neat search engine really)
objIE.navigate "https://www.sainsburys.co.uk/webapp/wcs/stores/servlet/gb/groceries"
'"search?query=" & Sheets("Sheet1").Range("A" & y).Value
'wait here a few seconds while the browser is busy
Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop
Application.ScreenUpdating = False
With Worksheets("Prices")
row_no = 2
Do Until .Cells(row_no, 2) = ""
Application.Wait (Now + TimeValue("0:00:03"))
'in the search box put cell "A2" value, the word "in" and cell "C1" value
objIE.document.getElementById("search").Value = _
Sheets("Prices").Range("G" & row_no).Value
'click the 'go' button
'objIE.document.getElementsByClass("submit").Click
objIE.document.forms(0).submit
'wait again for the browser
Do While objIE.readyState <> 4: DoEvents: Loop
Application.Wait (Now + TimeValue("0:00:03"))
'for each <a> element in the collection of objects with class of 'result__a'...
For Each aEle In objIE.document.getElementsByClassName("pricePerUnit")
'...get the text within the element and print it to the sheet in col D
Sheets("Prices").Range("H" & row_no).Value = aEle.innerText
Debug.Print aEle.innerText
Next
Application.Wait (Now + TimeValue("0:00:03"))
'for each <a> element in the collection of objects with class of 'result__a'...
For Each bEle In objIE.document.getElementsByClassName("pricePerMeasure")
'...get the text within the element and print it to the sheet in col D
Sheets("Prices").Range("I" & row_no).Value = bEle.innerText
Debug.Print bEle.innerText
Next
row_no = row_no + 1
Loop
End With
'close the browser
objIE.Quit
End Sub
Very new to the art of VBA and trying to develop some web scraping over a supermarket site to return current prices from a spreadsheet list of items. I have the below but can't get the data to return properly - am seeing the Runtime Error 438 at the point I'm trying to get data from the class element "pricePerUnit". Any help gratefully received!
Thanks
Sub test()
Dim objIE As InternetExplorer 'special object variable representing the IE browser
Dim aEle As HTMLLinkElement 'special object variable for an <a> (link) element
Dim bEle As HTMLLinkElement 'special object variable for an <b> (link) element
Dim y As Integer 'integer variable we'll use as a counter
Dim result As String 'string variable that will hold our result link
'initiating a new instance of Internet Explorer and asigning it to objIE
Set objIE = New InternetExplorer
'make IE browser visible (False would allow IE to run in the background)
objIE.Visible = True
'navigate IE to this web page (a pretty neat search engine really)
objIE.navigate "https://www.sainsburys.co.uk/webapp/wcs/stores/servlet/gb/groceries"
'"search?query=" & Sheets("Sheet1").Range("A" & y).Value
'wait here a few seconds while the browser is busy
Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop
Application.ScreenUpdating = False
With Worksheets("Prices")
row_no = 2
Do Until .Cells(row_no, 2) = ""
Application.Wait (Now + TimeValue("0:00:03"))
'in the search box put cell "A2" value, the word "in" and cell "C1" value
objIE.document.getElementById("search").Value = _
Sheets("Prices").Range("G" & row_no).Value
'click the 'go' button
'objIE.document.getElementsByClass("submit").Click
objIE.document.forms(0).submit
'wait again for the browser
Do While objIE.readyState <> 4: DoEvents: Loop
Application.Wait (Now + TimeValue("0:00:03"))
'for each <a> element in the collection of objects with class of 'result__a'...
For Each aEle In objIE.document.getElementsByClassName("pricePerUnit")
'...get the text within the element and print it to the sheet in col D
Sheets("Prices").Range("H" & row_no).Value = aEle.innerText
Debug.Print aEle.innerText
Next
Application.Wait (Now + TimeValue("0:00:03"))
'for each <a> element in the collection of objects with class of 'result__a'...
For Each bEle In objIE.document.getElementsByClassName("pricePerMeasure")
'...get the text within the element and print it to the sheet in col D
Sheets("Prices").Range("I" & row_no).Value = bEle.innerText
Debug.Print bEle.innerText
Next
row_no = row_no + 1
Loop
End With
'close the browser
objIE.Quit
End Sub