Create API session with xmlHttp in VBA

Apologies if the title of the question is wrong - I'm used to the idea of ​​PHP sessions with an API.

I am 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 before making any calls that will return data.

My problem is that I cannot figure out how to "stay logged in" for my second call to work.

after logging in, strReturn

filled in with the following line:

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

      

However, when I go to use strUrl

, I get the following message:

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

I've used this code in previous projects where I needed to provide an API key along with a URL for every request to the server - so it obviously worked. I'm not sure how to achieve the concept of "session establishment" though using xmlHttp.

+3


source to share


1 answer


So for everyone else who comes across this, the simple solution was to remove the following line from the call to the second one :

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

      

Without creating a new object, vba can save and use cookies from the first login.



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 that requires a login can support the session.

+3


source







All Articles