VBA Run-time error '91': Object variable or With block variable not set Internet Explorer Controls

[chrissnead]

Hello,

I am having trouble with the below code. For some reason it will give a run-time error sometimes, but it is completely random. Unfortunately I can't provide the URL since it is internal but I'm thinking this error is due to a timing issue. As you can see below was my attempt at fixing this by putting Application.Wait delays and DoEvents. Nothing seems to work and Application.Wait just makes it slow. I'm wanting this macro to work consistently rather than randomly like it does now. It will only work when I step into the code but not when it is run. Can someone help out?




'Set these references via Tools -> References in VBA editor:
'Microsoft Internet Controls
'Microsoft HTML Object Library
'Microsoft Shell Controls and Automation


Option Explicit


'https://msdn.microsoft.com/en-us/library/office/gg264421.aspx
'64-Bit Visual Basic for Applications Overview


#If VBA7 Then
'New VBA version 7 compiler, therefore >= Office 2010
'PtrSafe means function works in 32-bit and 64-bit Office
'LongPtr type alias resolves to Long (32 bits) in 32-bit Office, or LongLong (64 bits) in 64-bit Office
Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
Private Declare PtrSafe Function SetForegroundWindow Lib "user32.dll" (ByVal hwnd As LongPtr) As LongPtr
Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
#Else
'Old VBA version 6 or earlier compiler, therefore <= Office 2007
Private Declare Sub Sleep Lib "kernel32" (ByVal milliseconds As Long)
Private Declare Function SetForegroundWindow Lib "user32.dll" (ByVal hwnd As Long) As Long
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
#End If


Sub EOM()


Dim IE As InternetExplorerMedium ' This object (the "medium" variety as opposed to "InternetExplorer") is necessary in our security climate
Dim targetURL As String
Dim sh
Dim eachIE
Dim HTMLdoc As HTMLDocument
Dim resultsDiv As HTMLDivElement
Dim i As Integer
Dim shipID As String, trailer As String, ppnote As String


Range("A3").Select


i = 3


Do Until IsEmpty(Range("A" & i).Value)


targetURL = "myWebsite"
Set IE = New InternetExplorerMedium
IE.Visible = False ' Set to true to watch what's happening
IE.navigate targetURL

While IE.Busy
DoEvents
Wend

Do
Set sh = New Shell32.Shell
For Each eachIE In sh.Windows
If InStr(1, eachIE.LocationURL, targetURL) Then
Set IE = eachIE
IE.Visible = False 'This is here because in some environments, the new process defaults to Visible.
Exit Do
End If
Next eachIE
Loop

Set eachIE = Nothing
Set sh = Nothing

While IE.Busy ' The new process may still be busy even after you find it
DoEvents
Wend

'Application.Wait Now() + TimeSerial(0, 0, 10) ' delay of 5 seconds

'IE.Visible = True

Set HTMLdoc = IE.document

'IE.Visible = False ' Hide IE window until page has updated, otherwise VBA incorrectly references a 2nd IE window (hidden) with no results

With HTMLdoc
' Go into load
.all("eomSearchMain:baseSearchVal").innerText = Range("A" & i).Value
.all("eomSearchMain:advOrderSearch").Click
While .readyState <> "complete": DoEvents: Wend
End With

Do While IE.readyState <> 4: DoEvents: Loop
'Application.Wait Now() + TimeSerial(0, 0, 5) ' delay of 5 seconds

With HTMLdoc
.getElementById("frmOrderListing:lOrderListing:0ptxtOrderNumberActionFocusLink").Click
While .readyState <> "complete": DoEvents: Wend
End With

Do While IE.readyState <> 4: DoEvents: Loop
'Application.Wait Now() + TimeSerial(0, 0, 5) ' delay of 5 seconds

IE.Visible = True ' Show IE window again

' Extract results
shipID = HTMLdoc.getElementById("eomOrderDetail:shipid").Value
trailer = HTMLdoc.getElementById("eomOrderDetail:equipNumber").Value
ppnote = HTMLdoc.all("eomOrderDetail:_id556").Value

Range("B" & i).Value = shipID
If trailer <> "Number " Then
Range("C" & i).Value = trailer
End If
Range("D" & i).Value = ppnote

i = i + 1
IE.Quit
Set IE = Nothing
Loop


End Sub

 

[chrissnead]

Could someone help me find out why this errors out when running?

 

[John_w]

Create and destroy the IE object outside the loop, not inside it. What is the Shell.Windows loop for? It seems you could delete that, but maybe you need it for some reason.

Please use CODE tags - the # icon in the message editor.

 

[chrissnead]

Okay I have done the two things you told me and it is still giving me the error. I'm very new to using VBA in IE so forgive me if my code doesn't look the best.

If I walk through the code step by step (F8) it works fine. But if I run it, it gives me the run-time error. This tells me it has something to do with the speed of the process. When I look to see what happened, it skipped over the VBA that tells IE to click a link on the page to go to a new page. Not sure if this makes sense or not.

 

[John_w]

Which line causes the error?

 

[chrissnead]

It skips over any of the .Click HTMLdoc elements at random then errors at the next HTML element. I assume it skips over because the page hasn't loaded yet so it thinks the element doesn't exist, but I can't find how to work around this.

 

[John_w]

Try something like this to wait until the element is available before clicking it. I've used HTMLGeneric element because I don't know what type of elements you are dealing with.

Dim element As HTMLGenericElement

Do
    Set element = HTMLdoc.all("eomSearchMain:advOrderSearch")
    DoEvents
Loop While element Is Nothing
element.Click
'IE busy And readyState wait here
'Document.readyState wait here

Do
    Set element = HTMLdoc.getElementById("frmOrderListing:lOrderListing:0:optxtOrderNumberActionFocusLink")
    DoEvents
Loop While element Is Nothing
element.Click
'IE busy And readyState wait here
'Document.readyState wait here

 

[chrissnead]

This worked perfectly, thank you so much John_w!