Running a VBA Loop with Selenium to scrape html data into Excel

Question

I was recently assisted in scraping data from a webpage by the guys at Stackoverflow. It's a great community. I was given a function that pulls data into excel from a cell containing a url. Unfortunately I'm running into some problems because I need a loop function so that Excel does not restart all my functions once I save or refresh the page.

So far I have tried to build this, but am next to useless in VBA. Wondering if anyone can provide a little extra assistance.

Sub POSTPageViews()
Dim InputSheet As Worksheet
Dim i As Long
Dim AllWords As Range
Dim text As String
Dim OutValue As String
Dim driver As SeleniumWrapper.WebDriver
On Error Resume Next

Set driver = New SeleniumWrapper.WebDriver
driver.Start "chrome", "https://re.po.st/"
driver.Open strLocation

Set InputSheet = Active
Set WorkRng = Application.Selection
WordListSheet.Range("E1") = "All Words"
InputSheet.Activate
r = 1

Do While Cells(r, 1) <> ""
  Cells(r, 1).Value = txt
OutValue =        driver.findElementById("sguidtotaltable").findElementByTagName("span").text
 Next i
r = r + 1
driver.stop 'Stops the browser
Loop
End Sub

But naturally it is not working... Anybody see what is wrong? Basically in Column E I have all the URLs and in column K I would like to see the accompanying values.

Thanks

Maybe I'm just missing it, but I see a Next i with out a For i or any other reference to i for that matter. — Matt Cremeens
– Matt Cremeens, Commented Aug 17, 2015 at 15:11
Also, getting rid of the On Error Resume Next, at least for the moment, will allow the editor to tell you where your code is going wrong and why. — Matt Cremeens
– Matt Cremeens, Commented Aug 17, 2015 at 15:16
mmhm, I've taken that out but still problematic. I'm thinking that I have to define StrLocation? to be Column E? — Mike L
– Mike L, Commented Aug 17, 2015 at 15:19
If strLocation is where your url is, then I would put it inside your loop. Maybe something like this: driver.Open Cells(r, 5).Value. and you can place driver.stop outside of the loop. The results could be in column F, maybe, like Cells(r, 6) = driver.findElementsById(... — Matt Cremeens
– Matt Cremeens, Commented Aug 17, 2015 at 15:26

Matt Cremeens · Accepted Answer · 2015-08-17 15:41:32Z

1

Does this work (in the spirit of my comments)?

Sub POSTPageViews()
Dim driver As SeleniumWrapper.WebDriver

Set driver = New SeleniumWrapper.WebDriver
driver.Start "chrome", "https://re.po.st/"

With Worksheets("Trial")

r = 2

Do While .Cells(r, 5) <> ""
driver.Open .Cells(r, 5).Value
.Cells(r, 11) =    driver.findElementById("sguidtotaltable").findElementByTagName("span").text
r = r + 1

Loop
driver.stop 'Stops the browser
End Sub

edited Aug 17, 2015 at 15:41

answered Aug 17, 2015 at 15:31

Matt Cremeens

5,2118 gold badges42 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Matt Cremeens Over a year ago

What is the name of the sheet with the urls? I think we can clean up where you are activating and selecting various sheets and ranges.

Mike L Over a year ago

Just says Object Required, can't figure out what is going wrong there.

Mike L Over a year ago

Name of sheet is "Trial" url column is E and output column would be K :)

Matt Cremeens Over a year ago

OK. I edited this code in a way that I think will help and get to the heart of the matter. Try it, please, and tell me what happens.

Mike L Over a year ago

Just give me a minute, Struggling with wifi (of course :/ ) maybe just a bit longer than a minute

|

Collectives™ on Stack Overflow

Running a VBA Loop with Selenium to scrape html data into Excel

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related