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.
source to share
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.
source to share