1

In my Excel file (with macros), which basically has a VBA form with an input-box and a Confirm button. When button is pressed it makes an API GET request call to my website with the details inputted in the box and fetches a response accordingly, which the code uses further ahead. I'm facing a runtime error in a particular version of Excel, which I've described in detail below -

Here's the problematic part of my code - (snippet from a function I have in the form)

Dim objRequest As Object
Dim strUrl As String
Dim blnAsync As Boolean
Dim strResponse As String
Dim randnum As Double
' .... other code
randnum = Rnd()
strUrl = "https://mywebsite.com/connect.php?data=" & actbox.Value & " | " & year & "&mode=search" & "&random=" & Split(CStr(randnum), ".")(1)

Set objRequest = CreateObject("Msxml2.ServerXMLHTTP.3.0")
blnAsync = True
With objRequest
    .Open "GET", strUrl, blnAsync
    .setRequestHeader "Cache-Control", "no-cache"
    .setRequestHeader "Pragma", "no-cache"
    .Send                                    'this gets highlighted
    While objRequest.readyState <> 4
        DoEvents
    Wend
    strResponse = .responseText
End With

Set objRequest = Nothing
' .... other code

The PC I am testing on has Office 2010 & Excel Version - 14.0.4756.1000. I am getting this error on .Send line in the code - "Runtime error : -2147024984 (80070002) The system cannot find the file specified." and my code stops. I debug-printed the URL i am combining and made an external GET request which worked; so the URL is fine here. I've never faced an error like this regarding an API call in any/all versions I've tested on; except in this particular version.

What could be causing this? Is this specific 14.0.4756.1000 version of Excel 2010 unable to make the Msxml2.ServerXMLHTTP.3.0 API Call? If so what can be an another method for the same? Kindly advice... Thanks!

Edit: I'm using these references in my code, from which I belive the last 2 are relevant for the above problematic code? -

I don't know for sure but there's a high change the machine of my user is 32bit and has broken DLL files. Which files out of the above references are available externally for 32bit and I need to add them to their computer? Kindly advice... Thanks!

kartik
  • 550
  • 1
  • 6
  • 19
  • 1
    If you have added reference then try `Set objRequest = New MSXML2.ServerXMLHTTP` – CDP1802 Aug 28 '23 at 13:49
  • 1
    I doubt it relates to Excel (the 'file' mentioned could be one of the files that makes up the web page). Try changing the inputs and see if anything works eg use a well-known already available website such as https://www.google.com/; try changing async to False; try without one or both request headers; try creating the MSXML2 object as `CreateObject("MSXML2.XMLHTTP")` ... all just possibles – JohnM Aug 28 '23 at 13:54
  • Thanks @CDP1802 & @JohnM for replying! I'll test out this once I have access to the PC again. Meanwhile, I wanted to ask this - I have a lot of API calls similar to this one, all over my worksheet. Should I replace all of them with non-3.0 ones or add an exception for this version, using an If condtion like `if version = 14.0.4576.1000 : use non-3.0 code : else use 3.0 code` - is this a good idea? Kindly let me know your thoughts on this...Thanks! – kartik Aug 28 '23 at 15:15
  • 1
    Suggest testing first. I think 'Microsoft WinHTTP Services' v5.1 is still latest. 'Microsoft XML' you should be using v6.0 if you (your client) have it available (see [this SO answer](https://stackoverflow.com/a/951841/11318818)). Likely I would use v6.0 for all your MSXML related code ... but testing is the key. If you think the DLL for v3.0 is broken then using v6.0 makes sense also ... it is a separate file so no reason to think it will also be broken?! – JohnM Aug 28 '23 at 15:44
  • 1
    Seems odd if you have Excel 2010 that you have Excel 15.0 Object Library ([2013](https://learn.microsoft.com/en-us/officeupdates/update-history-office-2013) ?). My 2010 has 14.0 Object Library with XML v3.0 and XML v6.0. – CDP1802 Aug 28 '23 at 15:46
  • @JohnM I'll test out the if-condition approach first on the user's machine, modifying the code like `if excel version is 14.0.4576.1000 then use MSXML2.XMLHTTP else use MSXML2.XMLHTTP.3.0` and let you know here. Thanks! – kartik Aug 28 '23 at 16:01
  • @CDP1802 The screenshot I added is from my machine which has Excel 2013 (my workbook's working there) and the user's machine with the 14.xx... version has Excel 2010 like I have mentioned. I don't know yet of the references available in my user's Excel 2010. I'll check removing 3.0 first and let you know here. Thanks! – kartik Aug 28 '23 at 16:06

0 Answers0