Runtime Error 91. Object variable or with block variable not set

Question

it'd be awesome if you could help me with this persistent error. I'm trying to scrape a table from a website on IE and insert it into a 2 dimensional array.

The code is as follows:

Dim matrix(25, 7) As Variant
Set doc = ie.Document

Set Tbl = doc.getElementsByTagName("td").Item(6)
matrix(0, 1) = Tbl.innerText

Set Tbl = doc.getElementsByTagName("td").Item(7)
matrix(0, 2) = Tbl.innerText

Set Tbl = doc.getElementsByTagName("td").Item(20)
matrix(0, 3) = Tbl.innerText [Error Points to this line]

I've had several tries at troubleshooting. No luck. It works till item is up to 12--item(12). What exactly am I getting wrong or missing here? This is just a part of the code. There's more but it doesn't proceed further as the error occurs on the last line of this bunch. thanks.

For instance, I know it should work and that (20) has data because this test loop works perfectly.

Dim a As Integer

a = 0

Do While a < 200

MsgBox (a)

q = ie.Document.getElementsByTagName("td").Item(a).innerText

MsgBox (q)

a = a + 1

Loop

When a = 20, Msgbox displays an address that is on the table of the webpage.

Maybe doc.getElementsByTagName("td").Item(20) i.e. Tbl is Nothing? — Olivier Jacot-Descombes
Thanks for the input @OlivierJacot-Descombes. (20) does have data. In actuality, the list goes right up to (202). I've done a debug_print and msg_box till (202) and it works. — Billy Norbu
I have encountered this type of issue (missing expected data) when grabbing data from a webpage. In some cases, I can 'wait' and it will be ok, in other cases I have had to reissue the grab. After the request, I use a loop of 'DoEvents' until ReadyState = 4. Next, if length = 0, I give up. I search the content for a string that I know should exist AFTER all the data I need, and if not found, I reissue the request. I have also used a timer delay and if I set to 5 seconds, pages seem to always work. If I go with 1 second, works about 85% of the time. — Wayne G. Dunn
@WayneG.Dunn, thanks. I've had a feeling that it could be a wait issue too. Will let you know if it works after implementing your ideas. — Billy Norbu
@WayneG.Dunn. It worked after I posted an Access Msg_box after the sixth line. I'll give you credit for pointing out an immediate workaround. This still isn't a solution though. Having to click a Msg_box would mean it's not a complete automation, automation being my goal. — Billy Norbu

Wayne G. Dunn Wayne G. Dunn · Accepted Answer · 2015-12-17T22:48:45

Here is some code that can be modified to wait for as long as you want. It came from http://www.exceltrick.com/formulas_macros/vba-wait-and-sleep-functions/

Option Compare Database
Option Explicit
#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 SleepTest()
    'MsgBox "Execution is started"
    Sleep 1000 'delay in milliseconds (1000 = 1 second)
    'MsgBox "Execution Resumed"
End Sub

Runtime Error 91. Object variable or with block variable not set

1 Answers