Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Web Query for a password protected website

  1. #1
    New Member
    Join Date
    Nov 2005
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    771
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    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
    Excel MVP jkp-ads.com

  4. #4
    New Member
    Join Date
    Nov 2012
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    771
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Web Query for a password protected website

    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  6. #6
    New Member
    Join Date
    Nov 2012
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    771
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    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
    Excel MVP jkp-ads.com

  8. #8
    New Member
    Join Date
    Nov 2012
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    771
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    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
    Excel MVP jkp-ads.com

Some videos you may like

User Tag List

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
  •