Runtime error 91 with VBA web scraping FROM TIME TO TIME

Question

I came across this post. I tried out the solution suggested by the accepted answer and came up with the following code.

Sub WebScraping()

Dim appIE As Object
Dim myValue As String
Dim iLastRow As Integer
Dim allRowOfData

Application.DisplayAlerts = False

Set appIE = CreateObject("internetexplorer.application")

With appIE
    .Navigate "http://uk.investing.com/rates-bonds/financial-futures"
    .Visible = False
End With

Do While appIE.Busy
    DoEvents
Loop

'Set allRowOfData = appIE.document.getElementById("pair_8907")

Worksheets("Web Scraping").Activate

'myValue = allRowOfData.Cells(7).innerHTML
myValue = appIE.document.getElementById("pair_8907").Cells(7).innerHTML

iLastRow = ActiveSheet.Range("B100000").End(xlUp).Row
ActiveSheet.Cells(iLastRow, 1).Select

If iLastRow = 1 Then
    ActiveCell.Offset(1, 0).Value = "US 30Y T-Bond"
    ActiveCell.Offset(1, 1).Value = myValue
    ActiveCell.Offset(1, 2).Value = Now()
    ActiveCell.Offset(1, 2).Select
ElseIf iLastRow > 1 Then
    ActiveCell.Copy Destination:=Cells(ActiveCell.Row + 1, 1)
    ActiveCell.Offset(1, 1).Value = myValue
    ActiveCell.Offset(1, 2).Value = Now()
    ActiveCell.Offset(1, 2).Select
End If

appIE.Quit
Set appIE = Nothing
Set allRowOfData = Nothing

End Sub

My script runs smoothly 9 out of 10 times but sometimes it throws an error:

When that happens the VBE debugger shows that the command stops at

myValue = appIE.document.getElementById("pair_8907").Cells(7).innerHTML

Maybe it is useful to do some error handling. Suppose the loading of the website is ready, which means appIE is no more busy, but the Website isn't loaded completely (e.g. due some error in the internet connection.) In this case there could be a valid appIE.document but possibly there is no element with ID "pair_8907" in that moment, therefore .Cells(7).innerHTML could throw an error. So to be safe all the time you should check every single scraping - is there a valid document after the do while? - has the document an element with the given id? - has this element 8 cells? — DaDirnbocher, Dec 13 '17 at 10:14

danieltakeshi · Answer 1 · 2017-12-18T10:51:28.803

I think you are not giving enough time for ie to load... So there is this function WaitIE to wait for the page to load, it is set to 5000 ms, if the error continue, you can give more time.

#If VBA7 Then  
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) 'For 64 Bit Systems  
#Else  
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds as Long) 'For 32 Bit Systems  
#End If 

Sub WebScraping()

Dim appIE As Object
Dim myValue As String
Dim iLastRow As Integer
Dim allRowOfData

Application.DisplayAlerts = False

Set appIE = CreateObject("internetexplorer.application")

With appIE
    .Navigate "http://uk.investing.com/rates-bonds/financial-futures"
    .Visible = False
End With

   WaitIE appIE, 5000

'Set allRowOfData = appIE.document.getElementById("pair_8907")

Worksheets("Web Scraping").Activate

'myValue = allRowOfData.Cells(7).innerHTML
myValue = appIE.document.getElementById("pair_8907").Cells(7).innerHTML

iLastRow = ActiveSheet.Range("B100000").End(xlUp).Row
ActiveSheet.Cells(iLastRow, 1).Select

If iLastRow = 1 Then
    ActiveCell.Offset(1, 0).Value = "US 30Y T-Bond"
    ActiveCell.Offset(1, 1).Value = myValue
    ActiveCell.Offset(1, 2).Value = Now()
    ActiveCell.Offset(1, 2).Select
ElseIf iLastRow > 1 Then
    ActiveCell.Copy Destination:=Cells(ActiveCell.Row + 1, 1)
    ActiveCell.Offset(1, 1).Value = myValue
    ActiveCell.Offset(1, 2).Value = Now()
    ActiveCell.Offset(1, 2).Select
End If

appIE.Quit
Set appIE = Nothing
Set allRowOfData = Nothing

End Sub

Sub WaitIE(IE As Object, Optional time As Long = 250)
'Code from: https://stackoverflow.com/questions/33808000/run-time-error-91-object-variable-or-with-block-variable-not-set
Dim i As Long
Do
    Sleep time
    Debug.Print CStr(i) & vbTab & "Ready: " & CStr(IE.READYSTATE = 4) & _
                vbCrLf & vbTab & "Busy: " & CStr(IE.Busy)
    i = i + 1
Loop Until IE.READYSTATE = 4 Or Not IE.Busy
End Sub

Thanks for the hints. I tried out Application.Wait, which is a similar function to Sleep and seems to have solved the problem. — RookieNoMore, Dec 18 '17 at 08:21

Runtime error 91 with VBA web scraping FROM TIME TO TIME

1 Answers1