Altova Mailing List Archives
>microsoft.public.xml Archive Home
>Thread Next - Re: Trouble using XMLHTTP in VBA (Excel Add-in)
Trouble using XMLHTTP in VBA (Excel Add-in)
Date: 8/5/2003 6:43:00 AM
Hi, I'm making an add-in for Excel which connects to a web application to fetch data and place into spreadsheets. I use the XMLHTTP object to make my requests. It's fairly straightforward, yet I'm struggling with some (to me at least) strange problems. After executing the request I assign a module-level variable with the xmlhttp.responseXML and use this to look up results for individual cells in the spreadsheet: With xmlHttp .open "POST", serviceUrl, false .send requestDoc Set responseDoc = .responseXML End With Here's the funny thing. If I put a breakpoint at the Set statement, and either step trough the code that follows or just runs again, then all is fine. But if I don't have any breakpoint there, responseDoc is Nothing (VB's null). Unlike the DOMDocument, the xmlHttp doesn't have any async property (or equivalent functionality). I tried to set xmlhttp.responseXML.async = false, but it makes no difference. I figured if xmlhttp.send returned asyncronously it could be fixed by setting the async of the document false. After all, either send() or the responseXML accessor must load the response body in order to return the document! I have no idea what is going on here and even started to wonder briefly if the xmlhttp object might itself set async true before loading the xml! (If so, it's restoring the state again - async is still false after I read the responseXML property.) My final attempt to work around this (instead of understanding it and correcting it maybe..?) was to insert a waiting loop, to guarantee that no further code runs before the readystate of the document is complete (4 in MSXML...), but this doesn't make any difference either..: Do ' absolutely nothing.... Loop Until xmlHttp.responseXML.readystate = 4 ANY ideas??? Help would be greatly appreciated.