Web Query for a password protected website

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.
 

syates

New Member
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!
 

jkpieterse

Well-known Member
From the VBA editor, select Tools, References and find that reference and place a checkmark in its box.
 

gelsner

New Member
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.
 

gelsner

New Member
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.






 

jkpieterse

Well-known Member
Don't you have an existing url you need to pull the data from with which you can do the testing?
 

gelsner

New Member
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.
 

gelsner

New Member
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
 

jkpieterse

Well-known Member
I don't see that piece of code, which site have you taken that from? Please just post your current code.
 

Some videos you may like

This Week's Hot Topics

Top