VBA Web Scraping Code Loop Fails After 3rd Iteration

Question

I'm trying to pull data from multiple webpages (different stock pages from the same site). I can get the data pulled for the first 3 times the loop is executed but on the 4th iteration it brings up error 91: Object Variable or With block Variable not set up.

I tried moving around the internet explorer opening command so that it opens a new browser at the beginning of each iteration, and closes it at the end of the loop, to make sure the IE object wasn't somehow failing. That didn't work, same issue.

Sub GetStock()


    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim cellnum As Range: Set cellnum = Range(ActiveCell.Address)
    Dim i As Integer
    Dim IE As Object
    Dim text As String

    i = 1

    Do Until i > 10
        Set IE = CreateObject("InternetExplorer.Application")
        IE.Visible = True

        cellnum = Range(ActiveCell.Offset(i, 7).Address)

        With IE
            .navigate cellnum.Value
            Do While .Busy And .readyState <> 4: DoEvents: Loop
            Sleep 1000
            text = .Document.getElementsByClassName("classname")(1).outerText
        End With

        ws.Cells(i, 12).Value = text
        i = i + 1

        IE.Quit
    Loop


End Sub

The links to the webpage are held within cells, hence the cellnum code. Finds the correct cell, retrieves the webpage within it, then moves on to the cell below it. The code is working perfectly for the first 3 iterations but for some reason fails on the 4th. The error code identifies the "text=.document.getElementsByClassName..." line as the error.

Have you tried to go to the link manually? By clicking on it yourself. Does it work? — alowflyingpig
– alowflyingpig, Commented May 13, 2019 at 0:03
The link does work, depending on what cell I start with in the loop sometimes the data will pull and sometimes it won't. — breadhead1
– breadhead1, Commented May 13, 2019 at 2:35
you are better off using a timed loop that attempts to find the element producing the error 91 which can exit as soon as found or do something else if not found within a specified time. — QHarr
– QHarr, Commented May 13, 2019 at 6:43

Uriel Katz · Accepted Answer · 2019-05-13 02:00:00Z

1

I think your issue is probably due to the element not existing on the webpage. If it does exist, are you sure you are pulling the right element from the collection?

Try running it with

.document.getElementsByClassName("classname")(0).outerText.

If that works then I would suggest looking at how many elements with the class "classname" are on the webpage. While on the other pages you may have 2 or more elements, it could be that on the 3rd page you only have one.

Can you post the webpages you are scraping?

answered May 13, 2019 at 2:00

Uriel Katz

3291 gold badge9 silver badges21 bronze badges

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

3 Comments

breadhead1 Over a year ago

What is weird is that no matter where I start in the list of websites its always 3 that work, then 4 doesn't. The link is tradingview.com/symbols/NYSE-MMM/technicals with the "MMM" changing based on what stock I am looking at.

breadhead1 Over a year ago

I've been trying more and it's not necessarily 3 times work, 4th fails. Sometimes the very first try, sometimes the 5th, etc. So the data will pull for a certain link on one attempt, then if I change the cell I'm starting with, sometimes that same link will pull, sometimes it won't.

Uriel Katz Over a year ago

I think your issue is you are being re-directed to the tradingview.com/badbrowser site before you have a chance to pull the data.

breadhead1 · Accepted Answer · 2019-05-13 03:05:06Z

1

Found the solution! The Sleep.1000 command wasn't providing enough time in all cases, and I guess the code was trying to pull data before a page was available. I thought the loop in there would solve that but I guess not (very new to this). Anyways, I changed it to Sleep.3000 to give my slow internet enough time to catch up and its working like a dream.

Thanks for all the help everyone.

answered May 13, 2019 at 3:05

breadhead1

312 bronze badges

Collectives™ on Stack Overflow

VBA Web Scraping Code Loop Fails After 3rd Iteration

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related