Web Query for a password protected website

[FPMonkeyorgangrinder]

I read on the Microsoft website that you can't run a Web Query on a password protected website so I've developed my own. I post it so that it may inspire some of you. I trawled the net for weeks and found documentelement.innertext by pure accident. I must say that I am only an amature so I apologise for my untidy programming.

You will need to add a "Microsoft Web Browser" to your sheet and use the following code........

Const READYSTATE_COMPLETE As Long = 4
dim x as integer

Public Sub WebQuery()

URL = "website to be queried"
Set ie = Sheet1.WebBrowser1
ie.Visible = 1
DoEvents
ie.Navigate URL
Do Until ie.readystate = READYSTATE_COMPLETE
DoEvents
Loop
With ie.Document.login
.loginid.Value = "username"
.password.Value = "password"
.submit
End With
Do Until ie.readystate = READYSTATE_COMPLETE
DoEvents
Loop
Application.Wait Now + Timevalue("00:00:03")
HTMLdata = Sheet1.WebBrowser1.Document.documentelement.innertext
HTMLdata = VBA.Split(HTMLdata, Chr(13)) 'change the split as required CHR(13) is for carriage return.
For x = 0 To UBound(HTMLdata)
Sheet1.Range("A" & (x + 1)) = HTMLdata(x)
Next x

End Sub

You may need to change the HTML references for the username and password. You may also want to change the CHR(13) to whatever you need as the delimiter. I have a small delay in the code to enable the page to fully load but sometimes it is better to replace it with the READYSTATE_LOADING constant.

Cheers.

 

[sdas86]

Hi, may I know the URL of the website you want to login? I can help you. I have a set of code which can login.

 

[gelsner]

The code is the one you recommended on Nov. 22nd; here's the link:

Daily Dose of Excel » Blog Archive » Get Data from Website that Requires a Login

The code that I am trying to run is:
(I've changed the log on info to "fake" to protect the innocent.)

Public Sub WebQuery()
Const READYSTATE_COMPLETE As Long = 4
Dim x As Integer

URL = "https://www.retireonline.com/rpsparticipant/index.jsp"
Set ie = Sheet1.WebBrowser1 ' ???how to define WebBrowser1??
ie.Visible = 1
DoEvents
ie.Navigate URL
Do Until ie.readystate = READYSTATE_COMPLETE
DoEvents
Loop

With ie.Document.login

.USER.Value = "fake"
.Password.Value = "fake"
.submit
End With


Do Until ie.readystate = READYSTATE_COMPLETE
DoEvents
Loop
Application.Wait Now + TimeValue("00:00:03")
HTMLdata = Sheet1.WebBrowser1.Document.DocumentElement.innertext
HTMLdata = VBA.Split(HTMLdata, Chr(13)) 'change the split as required CHR(13) is for carriage return.
For x = 0 To UBound(HTMLdata)
Sheet1.Range("A" & (x + 1)) = HTMLdata(x)
Next x

End Sub

 

[gelsner]

JK, The code is the one you recommended on Nov 22:


Daily Dose of Excel » Blog Archive » Get Data from Website that Requires a Login

Here's a list of the modified code that I'm trying to run:
Public Sub WebQuery()
Const READYSTATE_COMPLETE As Long = 4
Dim x As Integer

URL = "https://www.retireonline.com/rpsparticipant/index.jsp"
Set ie = Sheet1.WebBrowser1 ' ???how to define WebBrowser1??
ie.Visible = 1
DoEvents
ie.Navigate URL
Do Until ie.readystate = READYSTATE_COMPLETE
DoEvents
Loop

With ie.Document.login

.USER.Value = "fake"
.Password.Value = "fake"
.submit
End With


Do Until ie.readystate = READYSTATE_COMPLETE
DoEvents
Loop
Application.Wait Now + TimeValue("00:00:03")
HTMLdata = Sheet1.WebBrowser1.Document.DocumentElement.innertext
HTMLdata = VBA.Split(HTMLdata, Chr(13)) 'change the split as required CHR(13) is for carriage return.
For x = 0 To UBound(HTMLdata)
Sheet1.Range("A" & (x + 1)) = HTMLdata(x)
Next x

End Sub

 

[coleman984]

This video shows how it isn't a reference but rather an object.
Web Browser Control in Microsoft Excel 2007 VBA - YouTube

 

[donfaison]

What if the "Microsoft Web Browser" is not listed in the references? (including Microsoft Active X...WB, MS WB, Microsoft Office WB, or just WB spellings)? I have made a brief search on web an cannot find it.

 

[jkpieterse]

It is not in references, you must right-click the control toolbox of a userform and select "More controls". There you will find the Microsoft web browser control. If it isn't there: Are you on a MAC?

 

[donfaison]

It is not in references, you must right-click the control toolbox of a userform and select "More controls". There you will find the Microsoft web browser control. If it isn't there: Are you on a MAC?

No, but neither am I using a userform... Also, I am using Mozilla Firefox not IE, though I have IE installed.

 

[jkpieterse]

In that case you need the Microsoft Internet Controls library. I don't think you can automate Firefoxz from VBA, but you may prove me wrong