CODEWITHSUNDEEP
excelvbainternet-explorerweb-scraping
TechFanDan
TechFanDan

Reputation: 3478

Submitting form and reading results using Excel VBA and InternetExplorer

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

Upvotes: 0

Views: 142

Answers (1)

Zwenn
Zwenn

Reputation: 2267

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

Upvotes: 1

Related Questions