Get result of Macro in the cell in VBA-CodePudding

My request

When I run the below code I get its result in the immediate window as I'm using Debug.Print (cookieValues) in the last 2nd line of code, but I need that result to come in the A1 cell of Sheet1.

It might be a basic question, but I have already done my all research on the internet but am not able to get it as there might be technical words for searching it and as a new into VBA I'm not aware of it.

I used Msgbox (cookieValues) in the place of Debug.Print (cookieValues) by thinking that first I will get value into msgbox and then get it into cell A1, but that too failed.

Any help you can give as an experienced VBA person OR any recommended resources that can help me to get result in the cell.

What my Code does

It fetches cookies value from a site and gives OUTPUT as shown below in result

The Code

Public Function NSEDataCall(website, setCookies) As String

Dim XMLHTTP As WinHttp.WinHttpRequest

'Initialize XMLHttp Object
'Use the best/proper XMLHttp object available on your system
Set XMLHTTP = CreateObject("WinHttp.WinHttpRequest.5.1") ' needs Microsoft WinHTTP Services 5.1 reference
    
' XMLHTTP.Option(WinHttpRequestOption_EnableRedirects) = False ' WinHttpRequestOption_EnableRedirects=6
        
XMLHTTP.Open "GET", website, False
    
' Set headers.
XMLHTTP.setRequestHeader "REFERER", website
XMLHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)"
XMLHTTP.setRequestHeader "Accept", "text/xml,application/xml,application/xhtml xml,text/html;q=0.9,text/plain;q=0.8,image/png,*/*;q=0.5"
XMLHTTP.setRequestHeader "Accept-Language", "en-us,en;q=0.5"
XMLHTTP.setRequestHeader "Accept-Charset", "ISO-8859-1,utf-8;q=0.7,*;q=0.7"

' Set cookie value - used for second call
If Len(setCookies) > 0 Then
   XMLHTTP.setRequestHeader "cookie", setCookies
Else
End If

XMLHTTP.send

If Len(setCookies) > 0 Then

    ' Get response headers
    response = XMLHTTP.getAllResponseHeaders
    ' Debug.Print response

    ' Split by new line
    responseArray = Split(response, vbCrLf)
    ' Debug.Print responseArray(7)

    ' Helps to identify dataType - output comes as code numbers
    ' MsgBox (VarType(Trim(Split(Split(responseArray(5), ";")(0), ":")(1)) & "; " & Trim(Split(Split(responseArray(6), ";")(0), ":")(1))

    ' Return the sv_bm cookie in response array from indices 7 (indices start from 0)
    NSEDataCall = setCookies & "; " & Trim(Split(Split(responseArray(7), ";")(0), ":")(1))
   
Else

    ' Get response headers
    response = XMLHTTP.getAllResponseHeaders
    ' Debug.Print response

    ' Split by new line
    responseArray = Split(response, vbCrLf)

    ' Helps to identify dataType - output comes as code numbers
    ' MsgBox (VarType(Trim(Split(Split(responseArray(5), ";")(0), ":")(1)) & "; " & Trim(Split(Split(responseArray(6), ";")(0), ":")(1))

    ' Return the cookies in response array from indices 5 to 9
    NSEDataCall = Trim(Split(Split(responseArray(5), ";")(0), ":")(1)) & "; " & Trim(Split(Split(responseArray(6), ";")(0), ":")(1)) & "; " & Trim(Split(Split(responseArray(7), ";")(0), ":")(1)) & "; " & Trim(Split(Split(responseArray(8), ";")(0), ":")(1)) & "; " & Trim(Split(Split(responseArray(9), ";")(0), ":")(1))
    'Debug.Print (responseArray(5)   responseArray(6))

End If
End Function


       ' My Macro
Sub GetNSECookies()

Dim website As String
Dim cookieValues As String
Dim website2 As String
Dim cookieValuesFinal As String

' First call
website = "https://www.nseindia.com/market-data/securities-lending-and-borrowing"
cookieValues = NSEDataCall(website, cookieValues)
' Debug.Print (cookieValues)

' Second call for sv_bm cookie
website = "https://www.nseindia.com/market-data/securities-lending-and-borrowing"
cookieValues = NSEDataCall(website, cookieValues)


'Shows value in Immediate window
Debug.Print (cookieValues)

End Sub

The result of code shown in the Immediate window

nsit=p8XRMHoQSM5uEQUM7XIJdT8B; nseappid=eyJhbGciOiJIUzI1NiIsInUl2PkrpgUZp9w8r1UF-yXGo4Os; AKA_A2=A; ak_bmsc=520CE4F35658A3B15048CCCE60A4E7547D38DEAjbZNKcjEdm4LTLidgmXX0=; bm_mi=C5AE845425DB55CAB9626B7A4DD0F7FD~D8U6FxMuj0HFHR1iPY=; bm_sv=E2881456097AB72A45E379FB86952E6f7nV/M=

Thanks in advance to you!

CodePudding user response：

Replace this line Debug.Print (cookieValues) with

Sheets("Sheet1").Range("A1")= cookieValues