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.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
hello, sorry to butt in, but this sounds very useful - if only I knew how to add a web browser!

could you please add simple instructions for the simple people?

many thanks!
 
Upvote 0
From the VBA editor, select Tools, References and find that reference and place a checkmark in its box.
 
Upvote 0
OK, I'm hung up ---- I have inserted the "Microsoft Web Browser" control in my worksheet.
But when the author above, says to use "the following code" with it. How do I do that?

I have the code in a VBA macro --- but how do I use the code with the control?

Can you explain this to me or if not, is there a good/(with example) tutorial on using the Microsoft Web Browser control?

Your help will be greatly appreciated!!

/ Gary E.
 
Upvote 0
That is a good example, but the more I study it and try to implement it -- I find out that the URL it is trying to download the data from is no longer functioning. So, I can't fully utilize it as an example.

I'd really like to move ahead with this project; since it would save me lots of time -- every day! :)

So, a couple of ideas that may make it possible for me to move ahead:
1) Do you know of an example (maybe more recent) that is still functioning?
2) Since I don't mind logging on myself (with RoboForm); is there an example script for downloading data from a password protected site that has already been logged into?

All help on this will be greatly appreciated!!

/ Gary E.






 
Upvote 0
Don't you have an existing url you need to pull the data from with which you can do the testing?
 
Upvote 0
Sure, I tried that first; but cannot get the code to work for my own site that requires username and password. So, after much time of trying to get it to work; I went back to his example and tried to run it and discovered that his site no longer exists.

So, now I'm thinking I would like to try to implement code similar to his to just bring in the data. I don't mind logging in myself first.

But I'm sure open to any other examples or other suggestions!

My regards,

/ Gary E.
 
Upvote 0
OK, I believe I have identified my problem in using his example code to automatically log onto my site.
I believe it is this line of code
With ie.Document.login

Question -- is the "login" above the html page?
The html page that I have for logging in is "index.jsp"

And when I change this line of code to
With ie.Document.index

It produces the error message: "Object doesn't support this property or method"
Suggestions on how to fix this line of code?

/ GE
 
Upvote 0
I don't see that piece of code, which site have you taken that from? Please just post your current code.
 
Upvote 0

Forum statistics

Threads
1,212,936
Messages
6,110,764
Members
448,297
Latest member
cocolasticot50

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