Using Data from Web, how to login?

Question

I have a macro that uses the "Data from Web" function. I have logged into the website (in Internet Explorer) that I'm pulling data from - but the results I get in Excel just keep telling me I'm not logged in.

Is there a special way to login via Excel for "Data from Web"? I know it works, as I used the Macro Recorder to learn how Excel gets the data - and doing so manually, the website asked me to login in the "Excel IE Browser window"...but it's been over an hour, so I was logged out. How do I log in again to use it?

here's the applicable data pull code if it helps (the URL works fine, once logged in):

 With ActiveSheet.QueryTables.Add(Connection:="URL;" & theURL, Destination:=webInfoWS.Range("$A$2"))
        .name = cel.Value & " hex"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "3"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

Is this login a web based form (external database validation) or can credentials be passed in directly through the URL (SAM user validation)? — , Aug 31 '15 at 22:26
@Jeeped - I found an answer that's getting me on the way, but it's not quite working (see below answer and edits). — BruceWayne, Sep 01 '15 at 15:11
Unfortunately, it has been my experience that unless the actual URL and credentials can be shared, these problems are a rabbit hole that I prefer not to venture into. In the long run, I'd recommend an xmlHTTP based solution but without full examination of the site, it is unknown whether you have to deal with a JSESSIONID and/or apache token. — , Sep 01 '15 at 15:16
@Jeeped - ah okay, understood! It's odd though, because yesterday I logged in manually, and it worked...I guess I'll just ask the user to do that once, then see what happens. Oh well, thanks! (I'll look in to xmlHTTP). — BruceWayne, Sep 01 '15 at 15:17
If you can share the credentials/site URL privately, contact me at the email address [here](http://answers.microsoft.com/en-us/profile/a05bdd23-6c19-4f50-9b4a-3c6ae7d72590?sort=lastreplydate&dir=desc&tab=qna&forum=&filter=All&page=1&tm=1374165688591&auth=1#user-profile-tab-profile) and I'll see what I can come up with. — , Sep 01 '15 at 15:23
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/88496/discussion-between-brucewayne-and-jeeped). — BruceWayne, Sep 01 '15 at 15:37

BruceWayne · Answer 1 · 2018-10-30T00:20:21.903

For the mean time, I found a work around (mainly from this thread):

Adding this after Debug.Print "Opening " & theURL and just before With ActiveSheet.QueryTables.Add(...)

    ''' Log in to the web stuff
    Set ie = CreateObject("InternetExplorer.Application")
    With ie
        .Visible = True
        .Navigate theURL
        Do Until .READYSTATE = 4
            DoEvents
        Loop
        If Left(.Document.Title, 5) <> "Welcome!" Then
            .Document.all.Item("username").Value = "My User Name"
            .Document.all.Item("password").Value = "MyPassword"
            .Document.forms(0).submit
        End If
      '   .Quit
    End With
    ''''''

What this does is actually opens the IE Window, then (automated) puts in my username and password, and submits.

However, if I run the macro again (meaning that I already did log in), this gives me an error, because there is no username/password entry form.

Thoughts on how to get around - use On Error Goto Next, but I don't like using that, but it might be the best option. I think I'll try instead to get the Window title (via HTML) and check if that is the login form or not...

Edit: A note on how to know that the .Item("____") is "username" and "password". That just comes from the HTML Input ID tag:

You'll notice in the post I found this in, the text in the .Item() is different - I assume because that HTML ID is different as well.

EDIT 2: This doesn't work! I'm able to log in, see the web page in IE, but when I get to .Refresh BackgroundQuery:=False, the resulting information is the text saying I need to login :/

Using Data from Web, how to login?

1 Answers1