Is there any other way in VBA other than Msxml2.ServerXMLHTTP.6.0/3.0 to make a HTTP GET request?

kartik 0
Viewed 42

I am trying to make an API call in my VBA macro, using the below code -

Dim objRequest As Object
Set objRequest = CreateObject("Msxml2.ServerXMLHTTP.6.0")
   
With objRequest
    .Open "GET", "somewebpage", True
    .setRequestHeader "Cache-Control", "no-cache"
    .setRequestHeader "Pragma", "no-cache"
    .Send
    While objRequest.readyState <> 4
        DoEvents
    Wend
    Debug.Print .responseText
End With
Set objRequest = Nothing

The code above works perfectly in majority of excel versions (2007/10/13/16/19/365), but not in this particular version - Excel 2013 (15.0.4569.1504) MSO (15.0.4569.1506) 32-bit. I've had four of my clients report to me that the Macro is not running (means api is not sending request), and when I asked them to send me their excel build version, turns out they all had the same 15.0.4569.1504 version of Excel 2013. I've tried both Microsoft XML v6.0 & v3.0 References, neither of them are working. Does this particular version of excel not support Msxml2.ServerXMLHTTP requests? If so, I need to use another way of calling the API...

I'll be using the below command to check the build version of excel, and if it's 15.0.4569.1504, I'll run the alternative API request command, and if not then normal MsXML one.

If CreateObject("Scripting.FileSystemObject").GetFileVersion(Application.Path & "\WINWORD.exe") = "15.0.4569.1504" Then
' use the alternative api calling method
Else
' use standard Msxml method to call api
End If

So is there any other way other than Msxml2.ServerXMLHTTP.6.0/3.0 I can use to make a HTTP GET Request (a simple request, with url-encoded params & no body) and fetch the response when it arrives? Kindly guide me... Thanks! :)

msxml excel-2013 get vba excel
1 Answers

You can use more ways to for http request:

Dim req As Object Set req = CreateObject("WinHttp.WinHttpRequest.5.1")

req.Option(6) = False ' No redirects'

    Set IE = New InternetExplorer with IE.Document (=html page)
CreateObject("Msxml2.ServerXMLHTTP.6.0")
Set http = CreateObject("Microsoft.XMLHTTP") 'seems to work when 
msxml2 isn't
Set http = CreateObject("MSXML2.XMLHTTP")

this is a great api function:

Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, _
ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
Private Declare Function DeleteUrlCacheEntry Lib "wininet.dll" Alias "DeleteUrlCacheEntryA" (ByVal lpszUrlName As String) As Long
userfVEl10
Related