Haphazardly Posted Excel Information and Other Stuff

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.

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

Visual Basic

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

OptionExplicit

PublicxmlHttpRequest AsMSXML2.XMLHTTP

SubTest()

On ErrorGoToFailedState

IfNotxmlHttpRequest IsNothingThenSetxmlHttpRequest=Nothing

DimMyXmlHttpHandler AsCXMLHTTPHandler

Dimurl AsString

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

SetxmlHttpRequest=NewMSXML2.XMLHTTP

' Create an instance of the wrapper class.

SetMyXmlHttpHandler=NewCXMLHTTPHandler

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""

ExitSub

FailedState:

MsgBox Err.Number&": "&Err.Description

EndSub

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

Visual Basic

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

OptionExplicit

Dimm_xmlHttp AsMSXML2.XMLHTTP

PublicSubInitialize(ByRefxmlHttpRequest AsMSXML2.XMLHTTP)

Setm_xmlHttp=xmlHttpRequest

EndSub

SubOnReadyStateChange()

Debug.Print m_xmlHttp.readyState

Ifm_xmlHttp.readyState=4Then

Ifm_xmlHttp.Status=200Then

MsgBox m_xmlHttp.responseText

Else

'Error happened

EndIf

EndIf

EndSub

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

Visual Basic

1

2

' 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:

Export and remove the ‘CXMLHTTPHandler’ class to a known directory

Open that file with notepad

Find the ‘OnReadyStateChange’ sub, and add this text after the signature:Attribute OnReadyStateChange.VB_UserMemId = 0

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’ ?)

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.

Post navigation

21 thoughts on “Async XmlHttp calls”

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.

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.

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

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”

I created two class modules, CXMLHTTPHandler and CExcelEventReceivers. The code for each is below. I commented most of the OnReadyStateChange code and included an event (my first event). However, my event never gets fired. Any help would be appreciated.

When making multiple async requests simultaneously I am having difficulty determining what parameters generated the response so I can process it correctly. I am requesting data for multiple dates but the JSON response does not specify the date in the data. Is there a way I can tag the request so that the CXMLHTTPHandler can determine the parameters? Or can I read the URL from within CXMLHTTPHandler?

Johan: You’ll have a new instance of CXMLHTTPHandler for each request right? Make a new property in that class and send the input parameters into the class. That way when the OnReadyStateChange event is fired, you can just read that property to know the input parameters.