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.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

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
1,066
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
1,066
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
1,066
I don't see that piece of code, which site have you taken that from? Please just post your current code.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,863
Messages
5,489,323
Members
407,686
Latest member
Chuck1960

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top