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.
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 |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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
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.
Hi Juan,
Thanks, good stuff.
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
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.
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:
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
DoEvents
Loop
but this is not very async IMO…
Juan Pablo,
Thanks for the explanation – that’s interesting.
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”
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
Just found out that changing XMLHTTP to ServerXMLHTTP overcomes this limit.
A final note that setTimeouts might need to be called to override the default timeout of 30 seconds if you use ServerXMLHTTP
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.
an error just appear in my VB 2010
when I run the test module
user-defined type not defined
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!
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.
That’s excellent, thank you very much! It works now, I “just” need to understand how :)
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.
CXMLHTTPHandler
CExcelEventReceivers
Stef: It’s most likely because you’re declaring a XMLHTTPHandler object that’s different than the one that’s being created inside your EventReceivers class. In CExcelEventReceivers
Note the new property. Now in XMLHTTPHandler, I changed the RaiseEvent line so I wouldn’t have to actually instantiate the XMLHTTP object and get a status
Of course you shouldn’t change that unless you’re replicating my code. Finally, when I call it:
Notice how I don’t create a CXMLHTTPHandler variable. I have to use the one created in clsExcelEventReceivers because that’s the only one listening for events.
Hi Dick, this is an Excellent example you have shown here.
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?
Any help would be much appreciated!
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.
Maybe you add these lines to the class
And then in the standard module procedure
Then instead of
you could have
and put the URL into the title of the message box.
Thanks Dick, that worked. I have limited experience with classes so was unsure what the best approach was, but your’s worked like a charm!
For those with similar issues, another (similar) approach to Dick’s is shown here: https://hackexcel.com/2016/10/09/multi-session-asynchronous-web-request-with-vba-and-xmlhttprequest-object-part-2/
Hi, excellent thread addressing a problem I had for some time. I’m keen to get my head around this.
However, I ran into a problem I can’tfigure out: In my standard module, I copied this code from above:
If Not xmlHttpRequest Is Nothing Then Set xmlHttpRequest = Nothing
Dim MyXmlHttpHandler As CXMLHTTPHandler
‘ Create an instance of the wrapper class.
Set xmlHttpRequest = New MSXML2.XMLHTTP60
MyXmlHttpHandler.Initialize xmlHttpRequest
‘ Assign the wrapper class object to onreadystatechange.
xmlHttpRequest.OnReadyStateChange = MyXmlHttpHandler
When I run it, it fails on MyXmlHttpHandler.Initialize xmlHttpRequest with run time error 91 (object variable not set). But from what I see the object in the above code is set to Nothing at that time.
I have to add that I am using the MS XML V6 reference library, so my xmlHttpRequest is defined as
Public xmlHttpRequest As MSXML2.XMLHTTP60
What am I missing?
UPDATE: silly me, somehow I lost a line:
Set MyXmlHttpHandler = New CXMLHTTPHandler
Apologies, all is fine now. Thanks anyway!
Hi Juan and Dick,
Thanks for your article and answers to the questions raised by others. It was really helpful for me to create an asynchronous code in VBA reducing the time the code took to get responses from 1000 urls from a minimum of 15-20 minutes to around 5 secs. However, I have certain questions on further improvements for the code
What I am doing: I create a URL from a field in excel and send request to a API in loop. The API returns a result telling whether the object corresponding to the field in excel is present on the website and corresponding url for the object. The url gets updated in excel
What I want to do further:
If I have 10000 cases to check for presence on a website, I don’t want to bombard the API server with 10000 requests at one go. I want to send requests in batches of 500 requests, process response for those 500 and then send the next 500. This is where I get stuck in the following problems:
Unless and until execution of ‘all’ sub modules end, the OnReadyStateChange doesn’t kick into action. If I send large number of requests at once, some of the responses get ignored because of this. This problem is amplified if I send 500 request at a go then wait for sometime and then send a second batch. Is it possible to execute requests in asynchronous manner in batches?
Hello thanks for posting. I am beginner in VBA. i have to retrieve responsetext for 150 url and it takes 4 min. It is doing one after other. How to do for more url’s and reduce time? Please help..
Hello, your code is working perfectly for me. but i gave 150 request in for loop of function. How to return this function value after all request completed.
Thanks
VERSION 1.0 CLASS
BEGIN
MultiUse = -1 ‘True
END
Attribute VB_Name = “CXMLHTTPHandler”
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Attribute OnReadyStateChange.VB_UserMemId = 0
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
Above code shown when open in notepad
please solve this error
-2147483638: The data necessary to complete this operation is not yet available
This line is not executing
when run Test() sub No xml document shown
i add in thiis way
VERSION 1.0 CLASS
BEGIN
MultiUse = -1 ‘True
END
Attribute VB_Name = “CXMLHTTPHandler”
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Attribute OnReadyStateChange.VB_UserMemId = 0
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
xmlHttpRequest.OnReadyStateChange = MyXmlHttpHandler
Hello, I has been trying this strategy, but I get an error all the time “user-defined type not defined”. I do not know what could be happening I have follow all, step by step.
The code copied:
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 = “https://www.omie.es/es/file-access-list?parents%5B0%5D=/&parents%5B1%5D=Mercado%20Diario&parents%5B2%5D=1.%20Precios&dir=Precios%20horarios%20del%20mercado%20diario%20en%20Espa%C3%B1a&realdir=marginalpdbc”
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
xmlHttpRequest.OnReadyStateChange = MyXmlHttpHandler
Module Class:
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
Cls file:
VERSION 1.0 CLASS
BEGIN
MultiUse = -1 ‘True
END
Attribute VB_Name = “CXMLHTTPHandler”
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Dim m_xmlHttp As MSXML2.XMLHTTP
Public Sub Initialize(ByRef xmlHttpRequest As MSXML2.XMLHTTP)
Set m_xmlHttp = xmlHttpRequest
End Sub
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 saw the link was broken, so I tried also with https://www.omie.es/es/file-access-list?parents%5B0%5D=/&parents%5B1%5D=Mercado%20Diario&parents%5B2%5D=1.%20Precios&dir=Precios%20horarios%20del%20mercado%20diario%20en%20Espa%C3%B1a&realdir=marginalpdbc
. But i do not thing tha
t matters from this error.
That error usually means you haven’t set a reference, which I think was not in the instructions so if you didn’t know you needed it, it’s easy to miss. In the VBE, go to Tools – References and check next to Microsoft XML, v6.0.
I am using Post via VBA to send data to a Google form which then swnds onto a Googlesheet.
There are only 40 rows of 4 fields.
But *some* records are randomly not arriving.
this is despite adding a delay of 200ms between each record being sent.
Grateful for any solutions