Async XmlHttp calls

Based on a recent interaction with Jan Karel, I thought about resurrecting this subject which I find very interesting.

Currently the web community is all hyped about Ajax, because of the enhancements that the combination of javascript, DHTML and the XMLHTTP object can bring to the user experience.

In Excel development we can also take advantage of the XMLHTTP object, but it is generally used in a synchronized way, that is, the code must wait until the object finishes loading the page before it can continue. This is usually not a problem, but when you are reading a big page, this wait can become a problem.

The only direct way of dealing with this problem is to put the XMLHTTP object inside of a Do / Loop cycle, but we can create a truly async solution by using a helper class module.

Let’s start with a simple example. This is the RSS feed of the recent tracks that I have listened to.

http://ws.audioscrobbler.com/1.0/user/juanpg/recenttracks.rss

Now, open a new workbook in Excel, go to the VBE and insert a new standard module.

Option Explicit

Public xmlHttpRequest As MSXML2.XMLHTTP

Sub Test()
   On Error GoTo FailedState
   If Not xmlHttpRequest Is Nothing Then Set xmlHttpRequest = Nothing

   Dim MyXmlHttpHandler As CXMLHTTPHandler
   Dim url As String
   
   url = “http://ws.audioscrobbler.com/1.0/user/juanpg/recenttracks.rss”
   
   Set xmlHttpRequest = New MSXML2.XMLHTTP

   ‘ Create an instance of the wrapper class.
  Set MyXmlHttpHandler = New CXMLHTTPHandler
   MyXmlHttpHandler.Initialize xmlHttpRequest

   ‘ Assign the wrapper class object to onreadystatechange.
  xmlHttpRequest.OnReadyStateChange = MyXmlHttpHandler

   ‘ Get the page stuff asynchronously.
  xmlHttpRequest.Open “GET”, url, True
   xmlHttpRequest.send “”

   Exit Sub

FailedState:
   MsgBox Err.Number & “: “ & Err.Description
End Sub

From there you can see that we’ll create an object called CXMLHTTPHandler. This is the class module that handles the status change of the XmlHttp object. So, insert a new class module and rename it ‘CXMLHTTPHandler’ and add the following code to it

Option Explicit

Dim m_xmlHttp As MSXML2.XMLHTTP

Public Sub Initialize(ByRef xmlHttpRequest As MSXML2.XMLHTTP)
   Set m_xmlHttp = xmlHttpRequest
End Sub

Sub OnReadyStateChange()
   Debug.Print m_xmlHttp.readyState
   If m_xmlHttp.readyState = 4 Then
      If m_xmlHttp.Status = 200 Then
         MsgBox m_xmlHttp.responseText
      Else
         ‘Error happened
     End If
   End If
End Sub

We’re almost done at this point. There is something that needs to be addressed first though; note this line in the standard module

   ‘ Assign the wrapper class object to onreadystatechange.
  xmlHttpRequest.OnReadyStateChange = MyXmlHttpHandler

In Javascript we can assign functions without a problem, but this is not as easy in VBA. We first need to add a default property to our own class, which will be, of course, the

OnReadyStateChange()

sub.

Chip Pearson explains how to do just that here. But it is just a matter of following these steps:

  1. Export and remove the ‘CXMLHTTPHandler’ class to a known directory
  2. Open that file with notepad
  3. Find the ‘OnReadyStateChange’ sub, and add this text after the signature:
    <span class="text">Attribute OnReadyStateChange.VB_UserMemId = 0</span>
  4. Save, close and reimport the class module into the project

And that’s it ! when you run the ‘Test’ sub you should see a message box with an xml document, similar to this one (Anyone up for ‘Pastora’ ?)

Async call to XMLHTTP

It is possible to expand this concept by including a Public Event in the class module that can be fired when the OnReadyStateChanged() sub finishes, that can be captured by another object.

15 Comments

  1. jkpieterse says:

    Hi Juan,

    Thanks, good stuff.

  2. Matt Vidas says:

    Hi Juan,

    I can’t tell you how much I appreciate you posting this! You’ve shown me how to multi-thread via VBA alone. Incredible to me! I have a few projects that retrieve/save multiple html files from the net, and now I don’t have to resort to an ActiveX EXE to get them concurrently. After a couple small changes/additions to your code, I now have a completely working solution for me. A quick test just downloaded 345 pages in a little over a minute; doing them consecutively would have taken me at least 5 minutes. I wasn’t sure how to use the code tags here, so I exported and zipped up my modified code to http://www.hastalavidas.com/JPGInet.zip should anyone wish to see.

    As a side note for others reading this, don’t forget to add a reference to Microsoft XML.

    Thank you thank you thank you!!
    Matt

  3. Doug Glancy says:

    Juan Pablo,

    I kind of understand how this works. Can you do a brief explanation of why wrapping this in a class makes it asynchronous? Thanks.

  4. Juan Pablo Gonzalez says:

    Doug,

    The XMLHTTP has the option to be synchronous or asynch built in. It’s the third argument in the .open method:

    xmlHttpRequest.Open “GET”, url, True

    which is actually

    xmlHttpRequest.Open method, url, asynchronous, [username], [password]

    What happens in javascript is that you fire the request to the url, and then assign another function to continue with the code when the call is complete, something similar in VBA would be to use the Application.OnTime to fire a specific procedure at a specific time.

    So in javascript you have:

    function startURL() {
       var xmlHttp;
       //some code to initiate the object
       xmlHttp.onreadystatechange = cleanUp;
       //some other code
    }

    function cleanUp() {
       alert(‘I”m finished !’);
    }

    With VBA, you can’t assign a function to this property of the object, nor a string, nor anything else. But it will take an object, any object apparently, and it will call its default property when the readyState change property changes.

    That’s why you need to build the extra class module, to be able to control when the object has changed its status.

    You don’t actually need the class module to make it async, as I said, you could just stick it inside of a Do Loop like

    Do While xmlHttpRequest.readyState <> 4
       DoEvents
    Loop

    but this is not very async IMO…

  5. Doug Glancy says:

    Juan Pablo,

    Thanks for the explanation – that’s interesting.

  6. Mike says:

    This works great. However: I experienced problems running into cache / caching. If you keep getting the same response, when you know your feed has been updated, add this line after the Open method and before the Send method:
    xmlHttpRequest.setRequestHeader “If-Modified-Since”, “Sat, 1 Jan 2000 00:00:00 GMT”

  7. Jack says:

    Good stuff! Except that only up to two async calls to each FQDN can be launched at once (the rest get blocked), due to a Microsoft WinInet limitation http://support.microsoft.com/kb/183110

  8. Jack says:

    Just found out that changing XMLHTTP to ServerXMLHTTP overcomes this limit.

  9. Jack says:

    A final note that setTimeouts might need to be called to override the default timeout of 30 seconds if you use ServerXMLHTTP

  10. Dave says:

    This is great, but I’m having trouble get the asynchronous xmlhttp to work with excel.

    Instead of the msgbox, I would like it to return the response text (the html) to the calling cell once it has been loaded. I’ve tried many things, but they don’t seem to work.

    I’m assuming that I need to pass a reference to the CXMLHTTPHandler onreadystatechange method, but I’m not sure which one.

    Ideally I would like a function Test(pURL as String) which returns the html text found at pURL
    Before it is loaded it could return “still loading”, although this is not necessary

    Thank you for your help.

  11. P says:

    an error just appear in my VB 2010
    when I run the test module
    user-defined type not defined

  12. [...] I’ve prepaired a test file, you can download at here. Open VBA source by press Atl + F8, you will see class module CXMLHTTPHandler, that I copied from this guide [...]

  13. Tamas Feher says:

    Hello, potentially a quick question, I couldn’t get this to work, potentially because I wasn’t sure where to stick this line in on the notepad: Attribute OnReadyStateChange.VB_UserMemId = 0

    Could someone help please? Thank you very much in advance!

  14. Dick Kusleika says:

    Tamas: Right below the Sub line.

    Sub OnReadyStateChange()
    Attribute OnReadyStateChange.VB_UserMemId = 0
       Debug.Print m_xmlHttp.readyState
       If m_xmlHttp.readyState = 4 Then
          If m_xmlHttp.Status = 200 Then
             MsgBox m_xmlHttp.responseText
          Else
             ‘Error happened
         End If
       End If
    End Sub

    I always assumed it didn’t matter where you put it (within the sub) but I’ve never really tested it.

  15. Tamas Feher says:

    That’s excellent, thank you very much! It works now, I “just” need to understand how :)

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: