How can I get access to readyState 3 with VBA "AJAX"?

January 8, 2013 10:35 AM

I'm writing some VBA code (in Excel) which uses a reference to Microsoft XML, v6.0, and creates a MSXML2.XMLHTTP object. I used the clsXMLHttpMonitor example from this page as a starting-off point. How can I access the response text when readyState = 3?

The example works fine for smallish requests.
But when the response is sufficiently large, Excel will freeze up into a "(Not Responding)" state and sometimes come back and sometimes seem to hang forever.

I know about readyState 3, but when I check for it, it seems that responseStream is either empty or "non-readable".

Is there something I am missing about how to get access to the stream of data as it arrives with this object?

OR...

Is there an alternative object I can use from within VBA to accomplish the desired result?
Preferably with code examples?

My stackexchange-fu has failed on this one; my similar question there has been met with silence.

posted by jozxyqk to computers & internet (10 answers total)

When I try to read the value of responseStream or responseText when readyState = 3, the error is infinitely described as:

"The data necessary to complete this operation is not yet available."
posted by jozxyqk at 11:36 AM on January 8

Yikes.. this error message is confirmed here, so I am definitely looking for the "alternative" answer.
posted by jozxyqk at 11:51 AM on January 8

Could you just kick off a separate process, like a PowerShell script for example, that does the HTTP fetch and writes it to a temp file?
posted by XMLicious at 1:14 PM on January 8

I don't understand what your goal is here. The documentation you linked says:

(3) INTERACTIVE   Some data has been received. Calling the responseBody and responseText properties at this state to obtain partial results will return an error, because status and response headers are not fully available.

In other words, the underlying data request is in progress, but not yet complete so the XML object can't be properly parsed, so isn't yet available. Presumably this fires periodically as the content downloads (in case you want to, I don't know, show a progress bar). Ignore it, and wait for ready state 4, at which point responseText/responseBody are available.
posted by axiom at 1:57 PM on January 8

@XMLicious: That might be a possibility, but I would rather it not get to the point where I need to include another executable in the process. This is for a distributable VBA "add-in" which we want to distribute with as few files as possible.

@axiom: I'm talking about retrieving a huge amount of data without freezing up Excel while filling up a static buffer. In AJAX (with actual JavaScript, which I've used before), this is what readyState 3 is all about. Reporting progress, and also processing data in chunks as it arrives by actually having a handle on the stream.
Apparently it doesn't work in Internet Explorer, and by extension it doesn't work with anything using the MSXML2 DLL.
posted by jozxyqk at 2:48 PM on January 8

Another possibility (sorry about the question-camping) is for me to write an intermediary php script between the VBA and the ultimate target URL, and use "long-polling" and sessions to create an artificial buffer... but I'm not sure how well that will work.
posted by jozxyqk at 2:51 PM on January 8

As a point of clarification: Are you receiving (multiple) readyState 3 events (which presumably only see an error once you try to access the responseBody/responseText properties), or is it ONLY hanging (no events)? And if it's hanging, I assume it eventually ends (i.e., once the file is fully fetched).

I'm no VBA expert, but I'm betting this is an interaction between XML parsing needing access to the entire file and the file being very large (thus slow to download). Your Excel app hangs because VBA isn't multithreaded and it's waiting for the file to download; once it does, Excel unfreezes, right? I think you need a separate thread of control to manage the download, which means writing a COM object to handle it or something along those lines. I don't write VBA stuff so I'm not particularly well equipped to get into specifics about how to do this.
posted by axiom at 6:31 PM on January 8

axiom, this has nothing to do with multithreading. It has to do with the ability to have a "pointer" (not necessarily literally) to the stream of HTTP data as it comes in.
If you look at Javascript examples of how to read data while in readyState 3, you'll see that I'm not completely insane, but that it is a limitation of Internet Explorer and the MSXML2 library specifically.
I am looking for an alternative library that gives the ability to read the stream.
posted by jozxyqk at 3:02 AM on January 9

What about WinHttpRequest? Anyone have experience with it?
I found it here and it claims that the stream is accessible. I want to try it out but maybe someone has some wise words...
posted by jozxyqk at 5:21 AM on January 9

For the future people searching metafilter for an answer: I've gotten a great response to my stackoverflow version of the question.
posted by jozxyqk at 6:32 AM on January 10