Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Web Query for a password protected website

This is a discussion on Web Query for a password protected website within the Excel Questions forums, part of the Question Forums category; I read on the Microsoft website that you can't run a Web Query on a password protected website so I've ...

  1. #1
    New Member
    Join Date
    Nov 2005
    Posts
    1

    Default 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.

  2. #2
    New Member
    Join Date
    Mar 2006
    Posts
    1

    Default

    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!

  3. #3
    Board Regular jkpieterse's Avatar
    Join Date
    Dec 2007
    Location
    Weert
    Posts
    635

    Default Re: Web Query for a password protected website

    From the VBA editor, select Tools, References and find that reference and place a checkmark in its box.
    Regards,

    Jan Karel Pieterse
    MS Excel MVP
    jkp-ads.com

  4. #4
    New Member
    Join Date
    Nov 2012
    Posts
    16

    Default Re: Web Query for a password protected website

    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.

  5. #5
    Board Regular jkpieterse's Avatar
    Join Date
    Dec 2007
    Location
    Weert
    Posts
    635

    Default Re: Web Query for a password protected website

    Regards,

    Jan Karel Pieterse
    MS Excel MVP
    jkp-ads.com

  6. #6
    New Member
    Join Date
    Nov 2012
    Posts
    16

    Default Re: Web Query for a password protected website

    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.







  7. #7
    Board Regular jkpieterse's Avatar
    Join Date
    Dec 2007
    Location
    Weert
    Posts
    635

    Default Re: Web Query for a password protected website

    Don't you have an existing url you need to pull the data from with which you can do the testing?
    Regards,

    Jan Karel Pieterse
    MS Excel MVP
    jkp-ads.com

  8. #8
    New Member
    Join Date
    Nov 2012
    Posts
    16

    Default Re: Web Query for a password protected website

    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.

  9. #9
    New Member
    Join Date
    Nov 2012
    Posts
    16

    Default Re: Web Query for a password protected website

    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

  10. #10
    Board Regular jkpieterse's Avatar
    Join Date
    Dec 2007
    Location
    Weert
    Posts
    635

    Default Re: Web Query for a password protected website

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

    Jan Karel Pieterse
    MS Excel MVP
    jkp-ads.com

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com