VBA error when navigating with Internet Explorer

Question

I am trying to download a table of proprietary investments/positions/pricing from Nationwide. The code seems to do what I want, EXCEPT for producing an "object required" error when I attempt to select a particular account (click)

I thought I had the proper code to tell my macro to wait until IE was ready to go on, but clearly I am missing something.

In the code, the relevant line is highlighted. If I enter a STOP above the error line, I can wait until I "see" the link appear, then "continue" the code and it runs as expected.

Because this goes to my financial accounts, I cannot provide the user name and password to allow someone to replicate the exact problem, but here is the code, and the error message and highlight. Suggestions appreciated.

Option Explicit
'set Reference to Microsoft Internet Controls
Sub DownLoadFunds()
    Dim IE As InternetExplorer
    Dim sHTML
    Const sURL As String = "https://www.nationwide.com/access/web/login.htm"
    Const sURL2 As String = "https://isc.nwservicecenter.com/iApp/isc/app/ia/balanceDetail.do?basho.menuNodeId=12245"

    Dim wsTemp As Worksheet
    Set wsTemp = Worksheets("Scratch")

Set IE = New InternetExplorer
With IE
    .Navigate sURL
    .Visible = True  'for debugging
    Do While .ReadyState <> READYSTATE_COMPLETE
        DoEvents
    Loop
    Do While .Busy = True
        DoEvents
    Loop

    'Login:  User Name and Password "remembered" by IE
    .Document.all("submitButton").Click

    Do While .ReadyState <> READYSTATE_COMPLETE
        DoEvents
    Loop
    Do While .Busy = True
        DoEvents
    Loop

    'Select this account to show
    .Document.all("RothIRA_#########").Click  '<--Error at this line

    Do While .ReadyState <> READYSTATE_COMPLETE
        DoEvents
    Loop
    Do While .Busy = True
        DoEvents
    Loop

    .Navigate sURL2
    Do While .ReadyState <> READYSTATE_COMPLETE
        DoEvents
    Loop
    Do While .Busy = True
        DoEvents
    Loop

    Set sHTML = .Document.GetElementByID("fundByFundOnly")
    With wsTemp
        .Cells.Clear
        .Range("a2") = sHTML.innertext
    End With
    .Quit
End With
Set IE = Nothing
End Sub

This is the error message:

Error Message

This shows the highlighted line: Line for error

EDIT:

At Tim Williams suggestion, I added a loop to test for the presence of the desired element. This seems to work:

...
On Error Resume Next
    Do
        Err.Clear
        DoEvents
        Application.Wait (Time + TimeSerial(0, 0, 1))
        .Document.getelementbyid("RothIRA_#########").Click
    Loop Until Err.Number = 0
On Error GoTo 0

....

Dai · Accepted Answer · 2015-02-01 21:45:43Z

1

IE.Document.all("#RothIRA_....") is returning Nothing (null in more refined languages), so calling the Click method is causing the error.

Your code is the same as doing this:

Dim rothElement As Whatever
rothElement = IE.Document.all("#RothIRA_....")
rothElement.Click

...when you should do this:

Dim rothElement As Whatever
rothElement = IE.Document.all("#RothIRA_....")
If rothElement <> Nothing Then
    rothElement.Click
End If

I suggest using the modern document.GetElementById method instead of the deprecated (if not obsolete) document.All API.

answered Feb 1, 2015 at 21:45

Dai

158k31 gold badges314 silver badges438 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ron Rosenfeld Over a year ago

The problem remains the signal that the desired element is present on the page. Should I just loop until rothElement <> Nothing? Or is there something more elegant?

Ron Rosenfeld Over a year ago

Also, using .Document.getelementbyid("RothIRA_#########").Click causes the same "object required" error problem. But thanks for letting me know my original syntax has been deprecated.

Dai Over a year ago

@RonRosenfeld you should ensure that the id="" attribute of the document is correct.

Ron Rosenfeld Over a year ago

ID is correct. The problem, as I mentioned in my question, is that part of the page hasn't yet loaded at the time the command is executed. But Tim Williams has provided an approach that works. Thanks.

Tim Williams · Accepted Answer · 2015-02-02 06:39:21Z

0

It's possible/likely that the page is using script to dynamically load some content or generate some layout after your "wait" loop has finished. That loop only waits until all linked content/resources have been loaded - it does not wait for scripts on the loaded page to finish, etc.

One approach is to loop your code waiting for the desired element to be rendered:

Const MAX_WAIT_SEC as Long  = 5 'limit on how long to wait...
Dim t
t = Timer
Do While .Document.all("RothIRA_#########") Is Nothing
    DoEvents
    'or you can Sleep here
    If Timer - t > MAX_WAIT_SEC  Then Exit Do 
Loop
'carry on...

answered Feb 2, 2015 at 6:39

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

1 Comment

Ron Rosenfeld Over a year ago

Yes it seems a loop waiting for the desired element to load is what is required. Your suggestion, when I first tried it, resulted in an error message. So I took to checking, within a loop including ON ERROR RESUME NEXT, for the presence of the desired element.

Collectives™ on Stack Overflow

VBA error when navigating with Internet Explorer

....

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

....

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related