Intermittent VBA Run-Time Error 91 in Excel 2003 Macro/Windows XP/IE 8.0

[tdh777]

Hi All,

I have looked for a day and a 1/2 and googled, read and experimented far and wide but am unable to sovle this problem.

I have a VBA routine in Excel that automates logging into a website as follows :

<code>
Sub Mysub()
Dim ie As Object
Set ie = CreateObject("internetexplorer.application")
ie.Visible = True

base_url = "https://www.somesite.com/"
ie.navigate base_url & "displayLogon.do?o=p&client=somename"

While ie.Busy
DoEvents
Wend

ie.document.getElementById("loginID").value = "my Login"
ie.document.getElementById("accessCode").value = "my Password"
ie.document.forms(0).submit

'trying early binding to see if that helps - same problem
'Dim ieObj As HTMLInputElement
'With ie
' Set ieObj = .document.getElementById("loginID")
' ieObj.Value = "my Login"
' Set ieObj = .document.getElementById("accessCode")
' ieObj.Value = "my Password"
' .document.Forms(0).submit
'End With

While ie.Busy
DoEvents
Wend

Exit Sub
</code>

The code sometimes works perfectly, and sometimes fails with a Run-Time Error 91 "Object variable or With Block variable not set".

It's horrible because it's intermittent.

If you run something like :

<code>
Sub tester()
For i = 1 To 6
Call Mysub
Next i
End Sub
</code>

..then it will for example work the first 2 times and then on the 3rd time it will return the 91 after having opened a new instance of IE and having navigated to the first window.

That is, the Run-time Error 91 occurs somewhere in the ie.document.getElementById area of the code.

Even more frustrating, if you set breakpoints and single-step it the code will ALWAYS work - it appears that either the time-delay or the invocation of the Debugger itself somehow causes this error not to occur .

Any and all help greatly appreciated !

Thanks,
Warren.

 

[tdh777]

Isn't it always the way that after looking for hours and hours you find a solution withing 20 minutes of giving up and posting on the Forum ??

The problem was, as suggested before, with the getElementById function.

Basically, sometimes the VBA Macro is too quick for the IE Application which has not yet built the DOM (Document Object model) by the time the getElementID function is called.

Thus getElementID was sometimes returning Nothing (No Object).

(The explict version of the syntax using ieObj makes it clearer that it's actually a get then set in one line of code I was trying to do).

I must admit I had assumed that the whole point of the ie.busy property and the While/Wend loop was that it would NOT return control to the Macro until IE had finished loading/rendering the page/building the DOM etc etc.

Silly me, to assume that Microsoft would do anything logical ...


Anwyay, the fix is simply to either :

a) Have a delay in the routine before looking for the Document Elements

b) Check if the getElementbyID has found anything and if not try again


The amended code below uses both methods :

<code>
Sub Mysub()
Dim ie As Object
Set ie = CreateObject("internetexplorer.application")
ie.Visible = True

base_url = "https://www.somesite.com/"
ie.navigate base_url & "displayLogon.do?o=p&client=somename"

While ie.Busy
DoEvents
Wend

Application.Wait Now + TimeValue("0:0:01") '** Delay for DOM to be rendered.

Dim ieObj As HTMLInputElement
tries = 0
tryagain:
With ie
Set ieObj = .document.getElementById("loginID")
If ieObj Is Nothing Then
''MsgBox ("could not get Element loginID !!!")
Application.Wait Now + TimeValue("0:0:01") '** Delay 1 second
tries = tries + 1
If tries < 5 Then
GoTo tryagain
else
Exit Sub
End If
End If
ieObj.Value = "my Login"
Set ieObj = .document.getElementById("accessCode")
ieObj.Value = "my Password"
.document.Forms(0).submit
End With

While ie.Busy
DoEvents
Wend

End Sub
</code>

Again I'm a bit embarrassed to have solved my own problem so soon after posting here, but hopefully this thread will help others in the future in the same way that I have founds answers to many Excel issues (both basic and advanced) on here.

Cheers,
Warren.

 

[tdh777]

And here's an even easier way to ensure that IE has finished building the DOM before you start asking it to look for specific elements.

<code>
...

ie.navigate someURL

Do While ie.Busy: DoEvents: Loop '** Wait til page loaded
Do While ie.readyState <> 4: DoEvents: Loop '** Wait til IE READY

'safe to interrogate DOM now as IE is Really finished loading the Page
...

</code>

Pity that more "using VBA to control a web page" posts on the Net don't have that criticial info...