Submitting form and reading results using Excel VBA and InternetExplorer

Question

I'm submitting a form using Excel VBA while using an InternetExplorer object. Once submitted, I can see the URL change on screen. However, when I attempt to output the URL (to confirm that it changed and the code knows it), I get the same URL.

In both debug statements below, they output the same URL.

Code:

Dim username As String
Dim password As String
Dim server_ip As String

username = "aaa"
password = "bbb"
server_ip = "ip_here"

Dim ie As New InternetExplorer

Dim doc As HTMLDocument
Set doc = New MSHTML.HTMLDocument

Dim url As String

ie.Visible = True
ie.navigate "my_url"

'wait
Do
DoEvents
Loop Until ie.readyState = READYSTATE_COMPLETE
Set doc = ie.document
Debug.Print "url: " & doc.url ' is /abc.html

'set credentials
doc.all.username.Value = username
doc.all.password.Value = password

'submit
ie.document.getElementsByTagName("form")(0).submit

Debug.Print "submitted..."

'wait
Do
DoEvents
Loop Until ie.readyState = READYSTATE_COMPLETE

Set doc = ie.document
Debug.Print "url: " & doc.url 'should be /def.html, but returns /abc.html

The object still remains the same as when you started so the url will remain the same. You need to update the object before getting updated info. — Mech, Sep 17 '20 at 18:16
@Mech I'm using two distinct calls to Set doc = ie.document. I need to do more than this? — TechFanDan, Sep 17 '20 at 18:17
Reset the second wait with `Application.Wait (Now + TimeSerial(0, 0, 5))`. That waits for 5 seconds. You can lower the time to your needs. I think your code doesn't wait after submit the form, because the status of the IE is already `complete`. — Zwenn, Sep 18 '20 at 09:15
@Zwenn that seems to be the fix for my issue. If you put up the answer, I can accept it. — TechFanDan, Sep 18 '20 at 13:16

Zwenn · Accepted Answer · 2020-09-20T13:59:37.340

The query on readystate_complete works only once in this way. After that the status remains the same. Therefore you can work with a manual pause if necessary.

'The last three values are hours, minutes, seconds
'This waits for 5 seconds
Application.Wait (Now + TimeSerial(0, 0, 5))

Another way is to wait with a loop until a known html element is found. Look at this example for more information:
Online search bar values after export from excel not clicking automatically tag identify wrong

One more example for using a loop:
Excel VBA - Web Scraping - Inner Text of HTML Table Cell

Submitting form and reading results using Excel VBA and InternetExplorer

1 Answers1

Linked