Help "error 91 Object variable or with block variable not set" appears occasionally

[sylvia111]

Hi Everyone

I am a beginner on VBA. What I want to do is very simple: use VBA to copy the car registration number to a website called https://www.moneyadviceservice.org.u...sts-calculator. Then extarct the data about the car to my excel s/s. My code worked fine in hte past two days (I probably made about 30 calls to the url). but since today, I occationaly got the above error messages i.e. it still works sometimes but sometimes not. I asked my colleagues to try my codes. The VBA works fine in the first 5 times and started to appear the same error afterwards. But again, if we leave it for a moment, it will work again. When I debug it, it shows the line which cause the problem is ReDim data(1 To .Rows.Length, 1 To .Rows(1).Cells.Length). Can anyone help me with his? Thank you very much in advance!

Sub SearchBot()
    Dim objIE As InternetExplorer
    Dim tagx As Object
    Dim tags As Variant
    Dim x As Long, y As Long
    Dim oRow As Object, oCell As Object
    Dim data() As String
    Dim tbl As Variant

    Set objIE = New InternetExplorer
    objIE.Visible = False
    'navigate IE to this web page (a pretty neat search engine really)
    objIE.navigate "https://www.moneyadviceservice.org.uk/en/tools/car-costs-calculator"
    'wait here a few seconds while the browser is busy
    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop
    'in the inbox type in your registration number
    objIE.document.getElementById("vrm").Value = _
      Sheets("Sheet1").Range("E11").Value
    
    Set tags = objIE.document.getElementsByTagName("button")
    For Each tagx In tags
    If tagx.innerText = "Add car" Then
        tagx.Click
        Exit For
    End If
    Next
  'wait again for the browser
    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop
 'extract the table content to the excel
    y = 1: x = 1
    Set tbl = objIE.document.getElementsByTagName("table")(0)
    With tbl
        ReDim data(1 To .Rows.Length, 1 To .Rows(1).Cells.Length)
        For Each oRow In .Rows
            For Each oCell In oRow.Cells
                data(x, y) = oCell.innerText
                y = y + 1
            Next oCell
            y = 1
            x = x + 1
        Next oRow
    End With
 Sheets(2).Cells(1, 1).Resize(UBound(data), UBound(data, 2)).Value = data
 objIE.Quit
End Sub

[Bobbles]

No doubt there are some in this forum that know Excel VBA (I'm not one of them)
If you are in a hurry, you may get a quicker response from the Office/VBA forum -
http://www.vbforums.com/forumdisplay...ce-Development
Rob

[DataMiser]

It would help if you told us which line was throwing the error

[SamOscarBrown]

He does, DM:

When I debug it, it shows the line which cause the problem is ReDim data(1 To .Rows.Length, 1 To .Rows(1).Cells.Length).

[sylvia111]

It would help if you told us which line was throwing the error

sure. the line is ReDim data(1 To .Rows.Length, 1 To .Rows(1).Cells.Length).

Thank you very much!

[sylvia111]

Yes thats the line. do you know how to fix it? do you know what causes the error? thank you very much

[sylvia111]

Hi DataMiser, the problem line is ReDim data(1 To .Rows.Length, 1 To .Rows(1).Cells.Length)

[SamOscarBrown]

No, wish I did.
What is tbl? I noticed you dimmed as Variant. If this is your table name, dim it as a String. See if that helps....like Rob, you'd probably get better responses from the other part of this forum. I will ask Moderator to review and see if it needs to be moved there.

[DataMiser]

If the error is happening sometimes and not others then my first thought would be that you are processing before the page has loaded completely and tbl is not set to anything.

[DataMiser]

Formatted the code from the OP so as to make it easier to read

Sub SearchBot()
    Dim objIE As InternetExplorer
    Dim tagx As Object
    Dim tags As Variant
    Dim x As Long, y As Long
    Dim oRow As Object, oCell As Object
    Dim data() As String
    Dim tbl As Variant
    
    Set objIE = New InternetExplorer
    objIE.Visible = False
    'navigate IE to this web page (a pretty neat search engine really)
    objIE.navigate "https://www.moneyadviceservice.org.uk/en/tools/car-costs-calculator"
    'wait here a few seconds while the browser is busy
    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop
    'in the inbox type in your registration number
    objIE.document.getElementById("vrm").Value = _
    Sheets("Sheet1").Range("E11").Value
    
    Set tags = objIE.document.getElementsByTagName("button")
    For Each tagx In tags
        If tagx.innerText = "Add car" Then
            tagx.Click
            Exit For
        End If
    Next
    'wait again for the browser
    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop
    'extract the table content to the excel
    y = 1: x = 1
    Set tbl = objIE.document.getElementsByTagName("table")(0)
    With tbl
        ReDim data(1 To .Rows.Length, 1 To .Rows(1).Cells.Length)
        For Each oRow In .Rows
            For Each oCell In oRow.Cells
                data(x, y) = oCell.innerText
                y = y + 1
            Next oCell
            y = 1
            x = x + 1
        Next oRow
    End With
    Sheets(2).Cells(1, 1).Resize(UBound(data), UBound(data, 2)).Value = data
    objIE.Quit
End Sub

[Shaggy Hiker]

Thread moved to Office Development, which is the right place for VBA questions.

I also edited your post to add [CODE][/CODE] tags. You can do this by pressing the # button and pasting the code between the resulting tags.

[sylvia111]

Hi SamOscarBrown, I tried to dim tbl as string but it still gives the same error.

[sylvia111]

Thank you. do you have any idea how to solve this error?

[westconn1]

you can test like

   Set tbl = objIE.document.getElementsByTagName("table")(0)
    if tbl is nothing then msgbox "no table"
    With tbl

though this may well not work in later versions of internet explorer, as i had to change code that relied on looping wile tbl is nothing, to wait for an element to load, but it will move the error to the new line

i well look later how i changed to resolve the problem in later versions

[sylvia111]

Hi Pete. Thanks for your help. I added your codes to test if table is nothing. I got an error message box saying no table. However the external webiste always provide a table if the correct car registration number is provided. do you know what has cause this? is there any solutions to fix it? Regards Sylvia

[westconn1]

what has cause this?

generally not all of the elements have loaded before the code runs, therefore tbl is not yet available as an object

when the code breaks on error, if you move the point back up a line to again try to get the table object [Set tbl = objIE.document.getElementsByTagName("table")(0)] does the code then run correctly?

if so, you could try putting some pause in the code (sleep or other), else loop on error like

on error resume next
do
  err.clear
  Set tbl = objIE.document.getElementsByTagName("table")(0)
  mytest = tbl is nothing  ' only used to find if error occurs 
loop while not err.number = 0
' return to previous error handling
' continue with your code

it was better in earlier versions where you could just loop while tbl is nothing, rather than deliberately trying to cause an error


edit: if you use any sort of a loop, you should include a time out, so that the loop can not run forever, try 2 seconds or 5 seconds, depending how long you think the max time should be

possibly the url hs been taking more hits lately, making it slower to serve out pages
note, the page may have not finished loading, even though the ie object in no longer busy and it's readystate is complete
you should check the t&cs to see if what you are doing complies

[sylvia111]

Thanks Pete. The line: Set tbl = objIE.document.getElementsByTagName("table")(0) seems work fine.

isnt the part "Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop" allows the webiste to load fully?

I just added your suggested codes but it still doesnt work.

[sylvia111]

I added Application.Wait (Now + TimeValue("0:00:20")) to apuse the macro to get it wait for 20 seconds. but still does not work i.e. error 91 still appears.

[westconn1]

I added Application.Wait

did you add before or after the line
Set tbl = objIE.document.getElementsByTagName("table")(0)?
needs to be before

i can not test the code as i have no idea of a valid reg number, but even working manually with the page, i kept getting internal server error, which could be what is causing your problem

isnt the part "Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop" allows the webiste to load fully?

yes but sometimes parts of page (usually in frames) load in background even after those two methods of the ie object are done

[sylvia111]

Oh, I added your code after the set. let me try it again!

[westconn1]

if the problem is caused by a server error, nothing you can do in code will fix it

all you can do is handle the error, then either do the navigation again, or close the ie object exit sub and try again from beginning

[sylvia111]

Hi westconn1, your solution works perfectly. I also tried to add a line to pause Macro for 15 seconds. This solution also works perfectly. Thank you very much!