Using VBA to open XML in Excel

[Kirsten Richardson]

I'm having trouble finding how to open an XML document in
Excel 2003 via VBA. What I have is a form on sheet 1
that has a couple parameters and a "go" button. Upon
pressing the button, the VBA code creates and sends a
soap request and receives an XML response which is stored
in a MSXML.DOMDocument object.

I haven't been able to figure out how to display selected
elements back in the sheet. Most of the examples I've
found are for converting spreadsheets to XML, not the
other way around.

Can anyone point me in the right direction?

Thanks!

 

[Stephen Bullen]

Hi Kirsten,

I'm having trouble finding how to open an XML document in
Excel 2003 via VBA. What I have is a form on sheet 1
that has a couple parameters and a "go" button. Upon
pressing the button, the VBA code creates and sends a
soap request and receives an XML response which is stored
in a MSXML.DOMDocument object.

I haven't been able to figure out how to display selected
elements back in the sheet. Most of the examples I've
found are for converting spreadsheets to XML, not the
other way around.

There are two options:

1. Parse the DOMDocument yourself, populating whichever cells need it.

2. If you have Excel 2003 standalone or from Office Professional,
click on Data > XML > XML Source to bring up the XML Source task pane.
Click the XML Maps button at the bottom of the task pane and load the
xsd file that describes your xml. If you don't have an xsd file, load
a sample xml file and let Excel guess the structure. The task pane
will then display a tree view of all the elements in the xml. Drag and
drop each element onto cells in the worksheet to map the elements to
the cells. Once you've done that, you can import any xml data into the
same workbook using code like:

oWorkbook.XMLMaps(1).ImportXML sMyXMLString
or
oWorkbook.XMLMaps(1).Import sMyXMLFileName

So if the XML response from the soap request is in a string variable,
you can probably bypass the DOMDocument.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie

 

[Kirsten Richardson]

Thanks Stephen. I'm trying to go with option #2, which I
think should be really easy. I have the map set up,
however, the response I get from the soap request is in a
DOMDocument. I'm then taking that response and
transforming it (via transformNode) using an XSL
document. So, I think I'm stuck with the resulting
DOMDocument unless transformNode can output a string? Is
there a way to convert a DOMDocument variable to a string
variable?

Thanks!

 

[Kirsten]

I figured out I could define an IXMLDOMNode variable and
set it equal to xmlVar.documentElement.parentNode then
set the string variable equal to the .xml parameter of
the node variable.

Dim nodeVar As MSXML2.IXMLDOMNode
Set nodeVar = xmlVar.documentElement.parentNode
StringVar = nodeVar.xml

Thanks for your help!

 

[Stephen Bullen]

Hi Kirsten,

Is
there a way to convert a DOMDocument variable to a string
variable?

Isn't that just DOMDocument.XML?

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie