VBA WinHTTP request timeout on VPN/Remote

[Abraham!@#]

Hi All,

I'm using winHTTP to connect to https://www.nseindia.com/ and get the cookies from the response header.
The code works fine in my personal network. But on Office VPN, it gets timed out.
I saw from https://chandoo.org/forum/threads/unable-to-set-proxy-for-winhttp-winhttprequest-5-1-object.38437/, that proxy setting are to be enabled.
Could you please tell me where I can find these proxy credentials that I need to set and how to set it in my code?

Here is my VBA code below. I have also commented the line on which I am getting the error.

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/"
cookieValues = NSEDataCall(website, cookieValues) ' function call
Debug.Print (cookieValues)

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


End Sub


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 = New WinHttp.WinHttpRequest
Set XMLHTTP = CreateObject("WinHttp.WinHttpRequest.5.1") ' needs Microsoft WinHTTP Services 5.1 reference
    
XMLHTTP.Option(6) = False ' WinHttpRequestOption_EnableRedirects=6
XMLHTTP.Option(4) = &H3300

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 "Connection", "keep-alive"
   XMLHTTP.setRequestHeader "cookie", setCookies
End If

Debug.Print website
XMLHTTP.send ' HERE is where the error is happening on remote
XMLHTTP.WaitForResponse
Debug.Print website 'checking if this will print

If Len(setCookies) > 0 Then

    ' Second call comes here - 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 at indices 7 (indices start from 0)
    NSEDataCall = setCookies & "; " & Trim(Split(Split(responseArray(7), ";")(0), ":")(1))
    

Else

    ' First call comes here - Get response headers
    response = XMLHTTP.getAllResponseHeaders
    ' Debug.Print response

    ' Split by new line
    responseArray = Split(response, vbCrLf)
    
    ' 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

 

[vletm]

Abraham!@#
Of course, I cannot test or verify, but ...
Have You checked XMLHTTP.send syntax?

send Method (IXMLHTTPRequest)

docs.microsoft.com

 

[Derek_the_Giant]

Is it true that WinHttpRequest in VBA only works if it is preceded by a call?

 

[Forrestinno]

You can use the MSXML2.serverhttp60 object instead of WinHTTP so that you can do more things with it, including set timeouts or setRequestHeader - it might be worth visiting the page for you, and if you get a Cookie page, analyze the coоkie, set the Cookie request header, and then use the same оbject tо resend the GET request. But first, if you have tested more than one VPN provider on your device, be sure to remove all previous VPN software packages and see if this helps solve your problem. If two VPN software packages are running simultaneously, it may disrupt the system you are currently working on and disrupt your internet connection.

 

[Marc L]

Is it true that WinHttpRequest in VBA only works if it is preceded by a call?

What do you mean ?​

​

But just see the many samples on web, whatever the blog / forum, like here … And its documentation as well.​

It's just a question of settings as you should pass some timeout like request header …​

​