Web Query for a password protected website

Joined
Nov 27, 2005
Messages
1
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.
 
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.
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,212,929
Messages
6,110,741
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top