puperzenkarl
12-12-2016, 02:41 AM
I have written a VBA Add-In for Excel which calls a SOAP Web Service and prints the XML response data to a worksheet. I am using the SOAP toolkit, but I need a replacement. It is not supported any longer and it does not perform very well on big responses. SoapClient30 and lower APIs like XMLHTTP do not support HTTP chunking. Therefore the full response is loaded into memory and then parsed into a DOM. Not very fast and inefficient.
My idea: I would like to use WinHttp 5.1 which works with HTTP chunking. My Web Service now supports HTTP chunking and "streams" the data to the VBA application. While the data is coming in, I would like to use a SAX reader to read the response and process the data. This should be faster and consume less memory.
Public Sub SoapTest()
Dim pRequest As New WinHttpRequest
pRequest.Open "POST", "serverUrl", True
pRequest.SetRequestHeader "SOAPAction", """soapAction"""
pRequest.SetRequestHeader "Content-Type", "text/xml; charset=""UTF-8"""
pRequest.SetRequestHeader "User-Agent", "FIRST Excel Add-In"
pRequest.Send CreateGetListMessage
pRequest.WaitForResponse (100)
Dim pReader As New SAXXMLReader60
Dim pContentHandler As New SAXHandlerImplPrintSheet
pReader.Parse pRequest.ResponseStream
End Sub
I can see the SOAP request in my network tracing tool and the outgoing XML response. SAXHandlerImplPrintSheet implements IVBSAXContentHandler and should print out some debug statements to console when the document/element starts/ends, but it does nothing. I am afraid, that there are some limitations to VBA, so that my concept will never work. Is it event possible?
My idea: I would like to use WinHttp 5.1 which works with HTTP chunking. My Web Service now supports HTTP chunking and "streams" the data to the VBA application. While the data is coming in, I would like to use a SAX reader to read the response and process the data. This should be faster and consume less memory.
Public Sub SoapTest()
Dim pRequest As New WinHttpRequest
pRequest.Open "POST", "serverUrl", True
pRequest.SetRequestHeader "SOAPAction", """soapAction"""
pRequest.SetRequestHeader "Content-Type", "text/xml; charset=""UTF-8"""
pRequest.SetRequestHeader "User-Agent", "FIRST Excel Add-In"
pRequest.Send CreateGetListMessage
pRequest.WaitForResponse (100)
Dim pReader As New SAXXMLReader60
Dim pContentHandler As New SAXHandlerImplPrintSheet
pReader.Parse pRequest.ResponseStream
End Sub
I can see the SOAP request in my network tracing tool and the outgoing XML response. SAXHandlerImplPrintSheet implements IVBSAXContentHandler and should print out some debug statements to console when the document/element starts/ends, but it does nothing. I am afraid, that there are some limitations to VBA, so that my concept will never work. Is it event possible?