Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Establishing an API session with xmlHttp in VBA

I apologize if my question's title is incorrect - I'm used to the idea of PHP sessions with APIs.

I'm trying to accomplish the same feat in VBA with the following code:

'Login
strLogin = "https://URL.COM/authenticateUser?login=username&apiKey=password"
Set xmlHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
xmlHttp.Open "GET", strLogin
xmlHttp.setRequestHeader "Content-Type", "text/xml"
xmlHttp.send

'Save the response to a string
strReturn = xmlHttp.responseText


'Open URL and get JSON data

strUrl = "https://URL.COM/Search/search?searchTerm=" & Keyword & "&mode=beginwith"
Set xmlHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
xmlHttp.Open "GET", strUrl
xmlHttp.setRequestHeader "Content-Type", "text/xml"
xmlHttp.send

'Save the response to a string
strReturn = xmlHttp.responseText


Sheets(1).Cells(20, 2).Value = strReturn

With this API, I need to login first prior to executing any calls that will return data.

My problem is that I cannot determine how to "stay logged in" so that my second call works.

after I login, strReturn is populated with the following string:

{"Status":{"Code":"2","Message":"Authentication Succeeded","Success":"true"}}

However, when I go to utilize strUrl, I get the following message:

{"Status":{"Code":"1","Message":"Invalid User Name Or Password","Success":"false"}}

I have used this code in prior projects where I needed to supply an API key along with the URL for each request to the server - so this obviously worked fine. I'm not sure how to achieve the concept of "establishing a session" though with xmlHttp.

like image 370
Brian Powell Avatar asked Jul 15 '15 15:07

Brian Powell


1 Answers

So, to anyone else who runs across this, the simple solution was to remove the following line from the second call:

Set xmlHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")

By not creating a new object, vba is able to keep and use the cookie from the first login call.

The final code looks like this:

'Login
strLogin = "https://URL.COM/authenticateUser?login=username&apiKey=password"
Set xmlHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
xmlHttp.Open "GET", strLogin
xmlHttp.setRequestHeader "Content-Type", "text/xml"
xmlHttp.send

'Save the response to a string
strReturn = xmlHttp.responseText


'Open URL and get JSON data

strUrl = "https://URL.COM/Search/search?searchTerm=" & Keyword & "&mode=beginwith"
xmlHttp.Open "GET", strUrl
xmlHttp.setRequestHeader "Content-Type", "text/xml"
xmlHttp.send

'Save the response to a string
strReturn = xmlHttp.responseText


Sheets(1).Cells(20, 2).Value = strReturn

And the API which requires a login is able to keep the session established.

like image 108
Brian Powell Avatar answered Oct 16 '22 20:10

Brian Powell