Vba – How to set and get JSESSIONID cookie in VBA

cookiesjsessionidsession-cookiesvbaweb services

I'm writing a VBA web service client in Excel 2010 using MSXML2.XMLHTTP60 for my Java REST web services hosted on Tomcat 8.5.5.

In VBA, I want to snag the string JSESSIONID=E4E7666024C56427645D65BEB49ADC11 from a response and set it in a subsequent request.
(if Excel crashes, it seems that this cookie is lost and the user has to authenticate again. I want to set the last stored session ID for the user, so if the session is still alive on the server, they don't have to re-authenticate in the Excel client.)

I saw some online resources according to which the following will pull the JSESSIONID cookie, but the last line always prints empty:

Dim httpObj As New MSXML2.XMLHTTP60
With httpObj
    .Open "POST", URL, False
    .SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)"
    .SetRequestHeader "Connection", "keep-alive"
    .Send
End With
Debug.Print "Response header Cookie: " & httpObj.GetResponseHeader("Cookie")  'This should pull the JSESSIONID cookie but is empty

When I print httpObj.GetAllResponseHeaders I do not see any headers that hold JSESSIONID.

In the same resources, the following should set the desired cookie, but it doesn't (I print out the headers of the incoming request on the server and see that my attempt did not override the JSESSIONID value).

httpObj.SetRequestHeader "Cookie", "JSESSIONID=blahblah"

I may be missing the mechanism for how JSESSIONED is transmitted, and how and when VBA pulls it and sets it.

Best Answer

Try to use MSXML2.ServerXMLHTTP to get control over cookies. The code below shows how to retrieve and parse cookies, and make request using that cookies:

Option Explicit

Sub Test_ehawaii_gov()

    Dim sUrl, sRespHeaders, sRespText, aSetHeaders, aList

    ' example for https://energy.ehawaii.gov/epd/public/energy-projects-map.html
    ' get cookies
    sUrl = "https://energy.ehawaii.gov/epd/public/energy-projects-map.html"
    XmlHttpRequest "GET", sUrl, Array(), "", sRespHeaders, sRespText
    ParseResponse "^Set-(Cookie): (\S*?=\S*?);[\s\S]*?$", sRespHeaders, aSetHeaders
    ' get projects list
    sUrl = "https://energy.ehawaii.gov/epd/public/energy-projects-list.json?sEcho=2&iColumns=5&sColumns=&iDisplayStart=1&iDisplayLength=0&mDataProp_0=0&mDataProp_1=1&mDataProp_2=2&mDataProp_3=3&mDataProp_4=4&sSearch=&bRegex=false&sSearch_0=&bRegex_0=false&bSearchable_0=true&sSearch_1=&bRegex_1=false&bSearchable_1=true&sSearch_2=&bRegex_2=false&bSearchable_2=true&sSearch_3=&bRegex_3=false&bSearchable_3=true&sSearch_4=&bRegex_4=false&bSearchable_4=true&iSortCol_0=0&sSortDir_0=asc&iSortingCols=1&bSortable_0=true&bSortable_1=true&bSortable_2=true&bSortable_3=true&bSortable_4=true"
    XmlHttpRequest "GET", sUrl, aSetHeaders, "", "", sRespText
    ' parse project names
    ParseResponse "\[""([\s\S]*?)""", sRespText, aList
    Debug.Print Join(aList, vbCrLf)

End Sub

Sub XmlHttpRequest(sMethod, sUrl, aSetHeaders, sPayload, sRespHeaders, sRespText)
    Dim aHeader
    With CreateObject("MSXML2.ServerXMLHTTP")
        .SetOption 2, 13056 ' SXH_SERVER_CERT_IGNORE_ALL_SERVER_ERRORS
        .Open sMethod, sUrl, False
        For Each aHeader In aSetHeaders
            .SetRequestHeader aHeader(0), aHeader(1)
        Next
        .Send (sPayload)
        sRespHeaders = .GetAllResponseHeaders
        sRespText = .ResponseText
    End With
End Sub

Sub ParseResponse(sPattern, sResponse, aData)
    Dim oMatch, aTmp, sSubMatch
    aData = Array()
    With CreateObject("VBScript.RegExp")
        .Global = True
        .MultiLine = True
        .Pattern = sPattern
        For Each oMatch In .Execute(sResponse)
            If oMatch.SubMatches.Count = 1 Then
                PushItem aData, oMatch.SubMatches(0)
            Else
                aTmp = Array()
                For Each sSubMatch In oMatch.SubMatches
                    PushItem aTmp, sSubMatch
                Next
                PushItem aData, aTmp
            End If
        Next
    End With
End Sub

Sub PushItem(aList, vItem)
    ReDim Preserve aList(UBound(aList) + 1)
    aList(UBound(aList)) = vItem
End Sub

You can see the result of cookies parsing in Locals window on breakpoint, first element contain nested array, representing JSESSIONID:

Generally the above example scrapes project names from http://energy.ehawaii.gov/epd/public/energy-projects-list.html (question):

Another one example is for https://netforum.avectra.com/eweb/ (question). Just add the below Sub:

Sub Test_avectra_com()

    Dim sUrl, sRespHeaders, sRespText, aSetHeaders

    ' example for https://netforum.avectra.com/eweb/
    sUrl = "https://netforum.avectra.com/eweb/DynamicPage.aspx?Site=NEFAR&WebCode=IndResult&FromSearchControl=Yes"
    XmlHttpRequest "GET", sUrl, Array(), "", sRespHeaders, sRespText
    ParseResponse "^Set-(Cookie): (\S*?=\S*?);[\s\S]*?$", sRespHeaders, aSetHeaders

End Sub

You can also see the cookies in Locals window, either not JSESSIONID, but others showing the method:

Note it's simplified method, it parses all cookies regardless path, domain, Secure or HttpOnly options.

Related Solutions

Javascript – How to set/unset a cookie with jQuery

Update April 2019

jQuery isn't needed for cookie reading/manipulation, so don't use the original answer below.

Go to https://github.com/js-cookie/js-cookie instead, and use the library there that doesn't depend on jQuery.

Basic examples:

// Set a cookie
Cookies.set('name', 'value');

// Read the cookie
Cookies.get('name') => // => 'value'

See the docs on github for details.

Before April 2019 (old)

See the plugin:

https://github.com/carhartl/jquery-cookie

You can then do:

$.cookie("test", 1);

To delete:

$.removeCookie("test");

Additionally, to set a timeout of a certain number of days (10 here) on the cookie:

$.cookie("test", 1, { expires : 10 });

If the expires option is omitted, then the cookie becomes a session cookie and is deleted when the browser exits.

To cover all the options:

$.cookie("test", 1, {
   expires : 10,           // Expires in 10 days

   path    : '/',          // The value of the path attribute of the cookie
                           // (Default: path of page that created the cookie).

   domain  : 'jquery.com', // The value of the domain attribute of the cookie
                           // (Default: domain of page that created the cookie).

   secure  : true          // If set to true the secure attribute of the cookie
                           // will be set and the cookie transmission will
                           // require a secure protocol (defaults to false).
});

To read back the value of the cookie:

var cookieValue = $.cookie("test");

UPDATE (April 2015):

As stated in the comments below, the team that worked on the original plugin has removed the jQuery dependency in a new project (https://github.com/js-cookie/js-cookie) which has the same functionality and general syntax as the jQuery version. Apparently the original plugin isn't going anywhere though.

Javascript – Get cookie by name

One approach, which avoids iterating over an array, would be:

function getCookie(name) {
  const value = `; ${document.cookie}`;
  const parts = value.split(`; ${name}=`);
  if (parts.length === 2) return parts.pop().split(';').shift();
}

Walkthrough

Splitting a string by token will produce either, an array with one string (same value), in case token does not exist in a string, or an array with two strings , in case token is found in a string .

The first (left) element is string of what was before the token, and the second one (right) is what is string of what was after the token.

(NOTE: in case string starts with a token, first element is an empty string)

Considering that cookies are stored as follows:

"{name}={value}; {name}={value}; ..."

in order to retrieve specific cookie value, we just need to get string that is after "; {name}=" and before next ";". Before we do any processing, we prepend the cookies string with "; ", so that every cookie name, including the first one, is enclosed with "; " and "=":

"; {name}={value}; {name}={value}; ..."

Now, we can first split by "; {name}=", and if token is found in a cookie string (i.e. we have two elements), we will end up with second element being a string that begins with our cookie value. Then we pull that out from an array (i.e. pop), and repeat the same process, but now with ";" as a token, but this time pulling out the left string (i.e. shift) to get the actual token value.

Best Answer

Related Solutions

Javascript – How to set/unset a cookie with jQuery

Javascript – Get cookie by name

Related Topic