duluter
10-08-2009, 06:27 PM
Hi, all.
There is a website I visit that allows users to query a database and retrieve the results in a downloadable xml file. What I am trying to do is save the contents of the xml file to a MSXML2.DOMDocument object in VBA so I can work with the data.
The problem is that the xml generated by the site is not well formed, so it doesn't load into my object correctly and I can't work with it (the object essentially remains empty as far as I can tell). If I go to the website in a browser, manually save the xml file to disk, open it in a text editor, edit it to make it well-formed, and then try to load that into a DomDocument variable, all is well. But I can't figure out how to do all this programmatically within VBA.
If the xml were already well-formed, here's the working code:
Dim xmldoc As MSXML2.DOMDocument
Set xmldoc = New MSXML2.DOMDocument
'Load entire Document before moving on
xmldoc.async = False
'Don't Validate
xmldoc.validateOnParse = False
xmldoc.Load ("http://www.examplesite.com/requestthatreturnssomexml")
What I need is a way to get the xml from the URL into a string variable, edit it, then load that cleaned xml into the xmldoc variable.
How can I grab the xml returned by my URL and put it into a string variable?
Thanks,
Duluter
There is a website I visit that allows users to query a database and retrieve the results in a downloadable xml file. What I am trying to do is save the contents of the xml file to a MSXML2.DOMDocument object in VBA so I can work with the data.
The problem is that the xml generated by the site is not well formed, so it doesn't load into my object correctly and I can't work with it (the object essentially remains empty as far as I can tell). If I go to the website in a browser, manually save the xml file to disk, open it in a text editor, edit it to make it well-formed, and then try to load that into a DomDocument variable, all is well. But I can't figure out how to do all this programmatically within VBA.
If the xml were already well-formed, here's the working code:
Dim xmldoc As MSXML2.DOMDocument
Set xmldoc = New MSXML2.DOMDocument
'Load entire Document before moving on
xmldoc.async = False
'Don't Validate
xmldoc.validateOnParse = False
xmldoc.Load ("http://www.examplesite.com/requestthatreturnssomexml")
What I need is a way to get the xml from the URL into a string variable, edit it, then load that cleaned xml into the xmldoc variable.
How can I grab the xml returned by my URL and put it into a string variable?
Thanks,
Duluter