How to do http basic authentication from Excel with VBA

I had some questions about authentication from users of the Excel Rest library. Typically Rest queries are authorized through the use of a developer key issued to registered users, but some sites need authorization as well.

HTTP basic authorization is accomplished by encoding a username and password into the headers of a request, and although there is plenty of information out there about how to do this with jQuery and javaScript and other languages, tracking down the solution from VBA was a little more difficult.

The solution is as below, with acknowledgement to

Public Function httpGET(fn As String, _
        Optional authUser As String = vbNullString, _
        Optional authPass As String = vbNullString) As String
    pHtml = fn
    Dim oHttp As Object
    Set oHttp = CreateObject("Microsoft.XMLHTTP")
    Call oHttp.Open("GET", pHtml, False)
    If (authUser <> vbNullString) Then
    ' need to do basic authentication
    ' acknowledgement to
        oHttp.SetRequestHeader "Content-Type", "application/json"
        oHttp.SetRequestHeader "Accept", "application/json"
        oHttp.SetRequestHeader "Authorization", "Basic " + _
            Base64Encode(authUser + ":" + authPass)
    End If
    Call oHttp.Send("")
    httpGET = oHttp.ResponseText
    Set oHttp = Nothing
End Function


This means that the Excel Rest Library just needs an optional username and password for sites that require authentication. The code below executes a rest query, and populates an excel sheet with the result after authenticating with the given username and password.
Public Sub testneedsPass()
    Dim cr As cRest
    Set cr = restQuery("needsPass", "needsPass", _
    , , , , , True, , , True, , "someuser", "somepassword")
End Sub


You can find out more about this and also get the full code at Basic HTTP Authentication from VBA
About brucemcp 225 Articles
I am a Google Developer Expert and decided to investigate Google Apps Script in my spare time. The more I investigated the more content I created so this site is extremely rich. Now, in 2019, a lot of things have disappeared or don’t work anymore due to Google having retired some stuff. I am however leaving things as is and where I came across some deprecated stuff, I have indicated it. I decided to write a book about it and to also create videos to teach developers who want to learn Google Apps Script. If you find the material contained in this site useful, you can support me by buying my books and or videos.