CODEWITHSUNDEEP
excelvbainternet-explorer-9
user1444796
user1444796

Reputation: 21

How does one wait for an Internet Explorer 9 frame to load using VBA Excel?

There are many online resources that illustrate using Microsoft Internet Explorer Controls within VBA Excel to perform basic IE automation tasks. These work when the webpage has a basic construct. However, when webpages contain multiple frames they can be difficult to work with.

I need to determine if an individual frame within a webpage has completely loaded. For example, this VBA Excel code opens IE, loads a webpage, loops thru an Excel sheet placing data into the webpage fields, executes search, and then returns the IE results data to Excel (my apologies for omitting the site address).

The target webpage contains two frames:

1) The searchbar.asp frame for search value input and executing search

2) The searchresults.asp frame for displaying search results

In this construct the search bar is static, while the search results change according to input criteria. Because the webpage is built in this manner, the IEApp.ReadyState and IEApp.Busy cannot be used to determine IEfr1 frame load completion, as these properties do not change after the initial search.asp load. Therefore, I use a large static wait time to avoid runtime errors as internet traffic fluctuates. This code does work, but is slow. Note the 10 second wait after the cmdGO statement. I would like to improve the performance by adding solid logic to determine the frame load progress.

How do I determine if an autonomous frame has finished loading?

' NOTE: you must add a VBA project reference to "Internet Explorer Controls"
' in order for this code to work
Dim IEapp As Object
Dim IEfr0 As Object
Dim IEfr1 As Object

' Set new IE instance
Set IEapp = New InternetExplorer

' With IE object
With IEapp
    ' Make visible on desktop
    .Visible = True
    ' Load target webpage
    .Navigate "http://www.MyTargetWebpage.com/search.asp"
    ' Loop until IE finishes loading
    While .ReadyState <> READYSTATE_COMPLETE
        DoEvents
    Wend
End With

' Set the searchbar.asp frame0
Set IEfr0 = IEapp.Document.frames(0).Document 

' For each row in my worksheet
For i = 1 To 9999                

    ' Input search values into IEfr0 (frame0)
    IEfr0.getElementById("SearchVal1").Value = Cells(i, 5)
    IEfr0.getElementById("SearchVal2").Value = Cells(i, 6)

    ' Execute search
    IEfr0.all("cmdGo").Click        

    ' Wait a fixed 10sec
    Application.Wait (Now() + TimeValue("00:00:10"))

    ' Set the searchresults.asp frame1
    Set IEfr1 = IEapp.Document.frames(1).Document

    ' Retrieve webpage results data
    Cells(i, 7) = Trim(IEfr1.all.Item(26).innerText)
    Cells(i, 8) = Trim(IEfr1.all.Item(35).innerText)

Next

Upvotes: 2

Views: 8310

Answers (3)

Jon Dresser
Jon Dresser

Reputation: 1

this is a Rrrreeally old thread, but I figured I would post my findings, because I came here looking for an answer...

Looking in the locals window, I could see that the "readystate" variable was only "READYSTATE_COMPLETE" for the IE App itself. but for the iframe, it was lowercase "complete"
So I explored this by using a debug.print loop on the .readystate of the frame I was working with. 

Dim IE As Object
Dim doc As MSHTML.HTMLDocument
Set doc = IE.Document
Dim iframeDoc As MSHTML.HTMLDocument
Set iframeDoc = doc.Frames("TheFrameIwasWaitingFor").Document

' then, after I had filled in the form and fired the submit event, 

Debug.Print iframeDoc.readyState
   Do Until iframeDoc.readyState = "complete"
     Debug.Print iframeDoc.readyState 
     DoEvents
   Loop

So this will show you line after line of "loading" in the immediate window, eventually showing "complete" and ending the loop. it can be abridged to remove the debug.prints of course.

another thing: 

debug.print iframeDoc.readystate ' is the same as...
debug.print doc.frames("TheFrameIwasWaitingFor").Document.readystate
' however, you cant use...
IE.Document.frames("TheFrameIwasWaitingFor").Document.readystate ' for some reason...

forgive me if all of this is common knowledge. I really only picked up VBA scripting a couple days ago...

Upvotes: 0

rds1975
rds1975

Reputation: 1

I used loop option to check the field value until its populated like this

Do While IE.Document.getElementById("USERID").Value <> "test3" IE.Document.getElementById("USERID").Value = "test3" Loop

Upvotes: 0

danielpiestrak
danielpiestrak

Reputation: 5439

As @JimmyPena said. it's a lot easier to help if we can see the URL.

If we can't, hopefully this overview can put you in the right direction:

  1. Wait for page to load (IEApp.ReadyState and IEApp.Busy)
  2. Get the document object from the IE object. (done)
  3. Loop until the document object is not nothing.
  4. Get the frame object from the document object.
  5. Loop until the frame object is not nothing.

Hope this helps!

Upvotes: 0

Related Questions