2

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.

alowflyingpig
  • 730
  • 7
  • 18
breadhead1
  • 31
  • 2
  • Have you tried to go to the link manually? By clicking on it yourself. Does it work? – alowflyingpig May 13 '19 at 00: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 May 13 '19 at 02: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 May 13 '19 at 06:43

2 Answers2

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?

Uriel Katz
  • 319
  • 1
  • 8
  • 21
  • 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 https://www.tradingview.com/symbols/NYSE-MMM/technicals/ with the "MMM" changing based on what stock I am looking at. – breadhead1 May 13 '19 at 02:11
  • 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. – breadhead1 May 13 '19 at 02:43
  • 1
    I think your issue is you are being re-directed to the https://www.tradingview.com/badbrowser/ site before you have a chance to pull the data. – Uriel Katz May 13 '19 at 03:10
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.

breadhead1
  • 31
  • 2