How to get cookie information using excel vba

Question

I am trying to get cookie information from under Network --> RequestHeader of below page https://www.nseindia.com/market-data/equity-derivatives-watch All I am getting from the below code is Response headers "set-cookie", but I need "cookie" information under request headers. Below is the code I've tried.

Function GetCookie(strUrl)
    With CreateObject("WinHttp.WinHttpRequest.5.1")
        .Open "GET", strUrl, False
        .SetRequestHeader "REFERER", strUrl
        .SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)"
        .SetRequestHeader "Accept", "text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,*/*;q=0.5"
        .SetRequestHeader "Accept-Language", "en-us,en;q=0.5"
        .SetRequestHeader "Accept-Charset", "ISO-8859-1,utf-8;q=0.7,*;q=0.7"
        .Send
        strCookie = .GetAllResponseHeaders

        strCookie = Split(strCookie, vbCrLf)

        Debug.Print strCookie

'        strCookie = Split(strCookie, ";")(0)
'        GetCookie = strCookie
    End With
End Function



Sub Demo()
Debug.Print GetCookie("https://www.nseindia.com/market-data/equity-derivatives-watch")
End Sub

I need the below cookie information

score 3 · Accepted Answer · edited Feb 18 '21 at 06:50

The problem is the cookies get expire every 1 hr so we have to refresh them.

Function GetCookie(strUrl)
With CreateObject("WinHttp.WinHttpRequest.5.1")
    .Open "GET", strUrl, False
    .SetRequestHeader "REFERER", strUrl
    .SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)"
    .SetRequestHeader "Accept", "text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,*/*;q=0.5"
    .SetRequestHeader "Accept-Language", "en-us,en;q=0.5"
    .SetRequestHeader "Accept-Charset", "ISO-8859-1,utf-8;q=0.7,*;q=0.7"
    .Send
    strCookie = .getAllResponseHeaders
    strCookie = Split(strCookie, vbCrLf)
    GetCookie = Trim(Split(Split(strCookie(5), ";")(0), ":")(1)) & "; " & Trim(Split(Split(strCookie(6), ";")(0), ":")(1))
End With
End Function

Sub get_cookies()
Msgbox(GetCookie("https://www.nseindia.com/market-data/equity-derivatives-watch"))
End Sub

Try this it will return nsit and nseappid

(Eg:- nsit=zk3-uf45niHVOsW_yaK2kIdWg; nseappid=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiONKJklmlmNIOJkjnkoivhvIiwiYXVkIjoiYXBpLm5zZSIsImlhdCI6MTVHJVHuihnjyuGyufhJNkoioxNjEzNjI3MjMyfQ.PDBf7k6fGsYicbM7sYeJrhE0OtGENf_e5YdRENkAHq0)

Worked for me and I have automated it. The link which we need the cookies for is the NSE site not the specific strike or script.

I got the same after running your code i.e. nsit and nseappid, now how to use them for some meanginful purpose ? — cyberspider789, Oct 22 '22 at 08:06

kshkarin · Answer 2 · 2020-10-10T13:12:24.080

-1

To get the cookie value get the Set-Cookie header from the response:

strCookie = .GetResponseHeader("Set-Cookie")

Then to use the same cookie on following requests:

.SetRequestHeader("cookie", strCookie)

edited Oct 10 '20 at 13:12

answered Oct 10 '20 at 12:07

kshkarin

574
4
9

that gives information on ResponseHeader, but I need the information under RequestHeader. – Kiran Oct 10 '20 at 13:08
@Kiran get the initial cookie from the first response, then on following requests set the header `.SetRequestHeader("cookie", strCookie)` – kshkarin Oct 10 '20 at 13:11
actually I need the session details too like _csrf paramenter in that cookie field – Kiran Oct 10 '20 at 13:47
@Kiran that wasn't in the question, please accept this answer if it helped you with your original question, open a new one for the new issue. – kshkarin Oct 10 '20 at 13:49
If u check the question again, I've requested to retrieve the information in the "cookie" field under the request header and not the set-cookie information. – Kiran Oct 10 '20 at 13:52
@Kiran you're mixing up different things, the `Set-Cookie` header will get you all the information you need, since you have multiple cookies in that header you need to split them into an array, you already have that in your question - `Split(strCookie, ";")` will get you an array with all the cookies. – kshkarin Oct 10 '20 at 13:59
I am getting different results under set-cookie, which are not useful and also I need specific information under cookie field, hence I want to retrieve the data in cookie under request header – Kiran Oct 10 '20 at 14:03
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/222821/discussion-between-kshkarin-and-kiran). – kshkarin Oct 10 '20 at 14:03

How to get cookie information using excel vba

2 Answers2

Linked