Reading XML Websites in VBA

Question

I have a webpage which is in xml and I'd like to read this information into an excel macro that I am making. However, I'm not quite sure how to go about it in visual basic. I've been coming at it like this...

Set wb = Workbooks.OpenXML(Filename:=requestURL, LoadOption:=xlXmlLoadImportToList)

Which is OK in a sense that I get the data into a workbook that I can then manipulate. However, I'd much prefer for the data not to be put into a workbook, i.e read it in directly. I've seen some people approach this by reading the webpage and storing it as a string first, but I'm wondering if there is a more direct approach built-in to VBA? Thanks.

See this [Q+A](http://stackoverflow.com/q/11305/1490783). – Olle Sjögren Apr 14 '13 at 20:15 — Olle Sjögren, Apr 14 '13 at 20:15

score 1 · Accepted Answer · answered Apr 15 '13 at 18:28

I'm not sure what approach you've seen, but the most direct way to handle this in VBA is to send a GET request via the MSMXL2.XMLHTTP object and get back the XML file. Then create an MSXML2.DOMDocument and fill it from the ResponseXML property of the XMLHTTP.

Reading XML Websites in VBA

1 Answers1