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.
 

syates

New Member
Joined
Mar 31, 2006
Messages
1
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
Joined
Dec 3, 2007
Messages
986
From the VBA editor, select Tools, References and find that reference and place a checkmark in its box.
 

gelsner

New Member
Joined
Nov 18, 2012
Messages
16
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
Joined
Nov 18, 2012
Messages
16
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
Joined
Dec 3, 2007
Messages
986
Don't you have an existing url you need to pull the data from with which you can do the testing?
 

gelsner

New Member
Joined
Nov 18, 2012
Messages
16
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
Joined
Nov 18, 2012
Messages
16
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
Joined
Dec 3, 2007
Messages
986
I don't see that piece of code, which site have you taken that from? Please just post your current code.
 

Forum statistics

Threads
1,085,309
Messages
5,382,892
Members
401,807
Latest member
xlWatcher

Some videos you may like

This Week's Hot Topics

Top