Multiple Account API Calls

[darklord5113]

I am running into trouble with a basic authorization API call:

I have multiple accounts through the data source. When I run the below code, it will prompt me once for a singular login/password, but not again for the subsequent procedures. And while the initial API call works , it then assumes each call after is for the same account and not recognizing the other credentials for the other procedures.

Attached is the code for one account, that I then have for each account with different variables/file names:

Dim Request As Object
Dim url As String
Dim Username As String
Dim Password As String
Dim oStream As Object
Const Str_file As String = "filepath/filename.csv"
Dim startdate As Variant
Dim EndDate As Variant



startdate = Format(Range("F3").Value, "mm/dd/yyyy")
EndDate = Format(Range("F5").Value, "mm/dd/yyyy")


Username = "username"
Password = "password"
Set Request = CreateObject("Microsoft.XMLHTTP")

url = "url.here"

    Request.Open "GET", url, False
    Request.SetRequestHeader "Authorization", "Basic" & ("Username" & ":" & "Password")
    Request.Send
    Debug.Print Request.Status
    
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write Request.responseBody
    oStream.Savetofile Str_file, 2
    oStream.Close

Two questions:
1- How do I get the other procedures to utilize their unique credentials, or do I have to run these all separately?
2- Ideally, what can I write in here to automatically input the specific credentials into the prompt once I have it running correctly for each account.

Thanks!

 

[John_w]

    Request.SetRequestHeader "Authorization", "Basic" & ("Username" & ":" & "Password")

You aren't specifying the username and password correctly because the above line is using the literal strings "Username" and "Password" instead of the variables. And there should be a space after "Basic". Try this instead:

    Request.setRequestHeader "Authorization", "Basic " & Username & ":" & Password

Also are you certain that the username and password should be specified as plain text? Some APIs require them to be encoded as Base64.

 

[darklord5113]

You aren't specifying the username and password correctly because the above line is using the literal strings "Username" and "Password" instead of the variables. And there should be a space after "Basic". Try this instead:

    Request.setRequestHeader "Authorization", "Basic " & Username & ":" & Password

Also are you certain that the username and password should be specified as plain text? Some APIs require them to be encoded as Base64.

First off, thank you for the help, so greatly appreciated.

The code now works with no prompt, but again, whichever procedure of the accounts is run first, even encoded as base64, it won't run the additional accounts as their own. Is there a difference in the code I should be specifying other than different usernames/filenames for the end result? Or is this just a problem with my data source?

 

[John_w]

I think you might need to clear the XMLhttp authentication (IE cache) before running for each different username. See VBA XMLHTTP clear authentication?

 

[darklord5113]

I think you might need to clear the XMLhttp authentication (IE cache) before running for each different username. See VBA XMLHTTP clear authentication?

Perfect, clearing the cache was the ticket. Thank you so much!