October 1, 2003
MSXML and Excel XP
I know people complain - even myself - about Microsoft. OSes plagued with security flaws; products with tons of bugs… But MS does have some good technologies in my opinion.
MSXML is one of them. Very easy to work with in IE, VB and the Office XP products.
For example, in 10 - 15 lines of code (and a XSL file), I created an Excel macro to post data to our WebService servlet. (I can’t post the XSL file yet since our API isn’t public yet - still working out the details.)
The basic code is something like this:
Dim sourcexml As New MSXML.DOMDocument
Dim sourcexsl As New MSXML.DOMDocument
Dim transformxml As New MSXML.DOMDocument
Set sourcexml = New MSXML.DOMDocument
Set sourcexsl = New MSXML.DOMDocument
Set transformxml = New MSXML.DOMDocument
sourcexml.async = False
sourcexsl.async = False
sourcexml.loadXML ActiveSheet.Range(”A1:D1″).Value(xlRangeValueXMLSpreadsheet)
sourcexsl.Load “[local xsl file]”
transformxml.loadXML (sourcexml.transformNode(sourcexsl))
Dim xmlhttp As MSXML.XMLHTTPRequest
Set xmlhttp = New MSXML.XMLHTTPRequest
xmlhttp.Open “POST”, “[WebServiceURL]”, False
xmlhttp.send transformxml.XML
MsgBox xmlhttp.responseBody
End Sub