Basic Authentication with Excel VBA "WebQuery"?

Question

In VBA I am trying to leverage WebQuery for a tool I am writing that retrieves financial data.
I create a WebQuery with similar code to this:

    url = "URL;http://my.url.com/"
    With Worksheets("Sheet1").QueryTables.Add(Connection:=url, Destination:=Worksheets("Sheet1").Range("A1"))
    .PostText = "Param1=Foo&Param2=Bar"
    .Name = "My Query"
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 1
    .WebSelectionType = xlEntirePage
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With

The problem I am coming across is that my.url.com requires a Basic Authentication userid and password.
If I simply run this code, I get a Runtime Error 1004; it does not prompt for authentication credentials.

But if I go into the Excel "Data" ribbon, select "From Web", and then go to http://my.url.com/ in their mini web browser, I am prompted for userid and password.
After closing this window and then re-running my VBA code without changing anything, the query works. But upon closing Excel and re-running, it fails again unless I go through this extremely ugly manual authentication step. Even if I have told Internet Explorer to save these credentials permanently.

How can I get VBA to programmatically authenticate a WebQuery with Basic Authentication so I don't need to take this extra step?

(Edit: Either this, or is there a setting within Excel to force this prompt to happen, regardless of how the WebQuery was initiated?)

I am using Excel 2010 (32-bit), on Windows 7, but my target users might be using a variety of Excel versions (all on Windows).

score 0 · Answer 1 · answered Dec 15 '15 at 20:48

0

You have to go into the source code and find id's for the button, username textbox and password textbox then something like:

 If objExist("LoginButton id", IE) Then
                    .Document.getElementById("username textbox id").Value = "username"
                    .Document.getElementById("password textbox id").Value = "password"
                    .Document.getElementById("LoginButton id").Click 
 End If

answered Dec 15 '15 at 20:48

justkrys

300
3
13

Ick. Really? There's no direct way to do this, and keep authentication persistent even when the spreadsheet is reloaded? I think I'll have to take my alternative option, which is to write a stupid PHP in the middle that takes the user and password as POST arguments, talks to the "real" script with curl using basic auth, and spits back the results to the WebQuery. Disappointing. – Josh Dec 15 '15 at 21:18
I'm sure there are probably lots of other ways to do it, but VBA is not known for its eloquence. That just happens to be a pretty easy way to have the macro log on for you. – justkrys Dec 15 '15 at 21:24

score 0 · Answer 2 · answered Dec 15 '15 at 22:09

If you want to use QueryTables, authentication is tricky. So much so that it's not even worth it, IMO. Excel uses different cookies than IE. So even if you authenticated via IE, the QueryTable wouldn't recognize it. If you authenticate via the Web Query interface, then it sets the proper cookie and you can automate.

The right way is to avoid the QueryTable and do it all yourself through the MS XML object library. Create an HTTP Request object, send a POST request, etc. If it's not RESTFUL, I think you have to use a HTTPServer object. See JP's comment at http://dailydoseofexcel.com/archives/2011/03/08/get-data-from-website-that-requires-a-login/ for more information on using XML.

Of course that's a lot more work because you have to do all the stuff the querytable would normally do. And you have to handle refreshes if you want that function.

Yeah this is entirely something I'm doing to be able to Refresh simple requests in the background without rewriting it in VBA and probably screwing something up. It's part of a larger codebase that already uses MSXML for "non-refreshing" requests which works fine. — Josh, Dec 15 '15 at 22:12

score 0 · Answer 3 · answered Dec 15 '15 at 22:15

Judging by other answers, I think my correct course of action is not to even try and get WebQuery to authenticate, and to use a server-side script in the middle that takes userid and password as POST arguments and communicates through to the "real" back-end over Basic Auth.

I've already written and tested one, and this seems to solve the actual problem I am trying to address.

Basic Authentication with Excel VBA "WebQuery"?

3 Answers3