CODEWITHSUNDEEP
vbaexcel
ItzDvl
ItzDvl

Reputation: 3

VBA: Finding a newly created HTML element

I'm trying to grab information from a web page using an InternetExplorerMedium object. The basic procedure for getting the information is as follows:

  1. Load the web page.
  2. Enter a start and end date into 2 editable text fields.
  3. Press a "submit" button. The page then runs some javascript and loads a table into a previously existing container. Inside this container are the table as well as a menu bar with buttons for next page, export, refresh, etc.
  4. Wait 5 seconds for the javascript to execute and display the table.
  5. Click the export button inside this container to download an excel file of the listed data.

And the code:

Sub GetReport()
Dim frm As Variant
Dim TxtRng As Range

'Load IE page
Set IE = New InternetExplorerMedium
IE.Visible = True
IE.Navigate "http://example.com/page"

'Wait until page is ready
Do Until IE.ReadyState = 4

Loop

'Enter data into fields, press submit
IE.Document.getElementsByName("userField1").Item(0).Value = Date
IE.Document.getElementsByName("userField2").Item(0).Value = Date
IE.Document.getElementsByName("submitButton").Item(0).Click

'Wait for table to load, find export button
Application.Wait (Now + TimeValue("00:00:05"))
Set frm = IE.Document.getElementByID("exportButton")

'Testing to see if frm was successfully grabbed
Set TxtRng = ActiveWorkbook.Sheets("Sheet2").Range("A1")
TxtRng.Value = frm.getAttribute("innerHTML")

End Sub

I've implemented steps 1-4, but when trying to find the HTML element for the export button, I receive an error:

Run-time error '01': Object variable or With block variable not set

I've done some troubleshooting and found out that:

So it looks like getElementbyID and getElementsbyName can't find the newly created elements. None of the elements are tagged, so I can't use getElementsbyTagName... Do I need to somehow "refresh" the InternetExplorerMedium object so it can detect the changes made to the page? I've look at the object reference and didn't see anything along those lines. Does the ReadyState change while the page is loading new elements?

Thank you for your help.

Upvotes: 0

Views: 611

Answers (2)

QHarr
QHarr

Reputation: 84465

After IE.Document.getElementsByName("submitButton").Item(0).Click, as a minimum you should wait until the browser has indicated the page has loaded which means you need to add 

While IE.Busy Or IE.readyState < 4: DoEvents: Wend

Then have your additional wait time if you wish.

Furthermore, you could add a max time to loop for, and then loop until the object has been set. This, without the timeout looks like:

Do
DoEvents
On Error Resume Next
Set frm = IE.Document.getElementByID("exportButton")
On Error GoTo 0
Loop While frm Is Nothing

Upvotes: 0

darbid
darbid

Reputation: 2731

The problem with your code is that you use Application.Wait. The problem with this is that InternetExplorer and Excel are both working on the same thread as you are controlling them both. So when you call Application.Wait which freezes Excel it will also freeze InternetExplorer and thus the insertion of new elements.

The answer is you need to let the thread run and for InternetExplorer to do its work. In VBA this is very difficult as you only have one thread. If you could move this to VB.net you could use threads.

My suggestion is that after you click the submit button you set up a timer which ticks every 1000 milliseconds. When this timer ticks check to see if the table you want is present. If not present let the timer go again, if present, end the timer and continue to find the button you want.

I have dealt with similar situations where the HTMLDocument is not refreshed but elements are added to it. In my case I was unable to find an event to wait for, I too could only set a timer and wait for it to tick and then check to see if the new elements are there.

If you do need help with finding this new element then you are going to need to either post a link or post some HTML. As you are using InternetExplorerMedium Im guessing it is an intranet site, so in that case try posting part of the html, even if it is just the new part inserted.

Upvotes: 0

Related Questions