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.

Olle Sjögren
– Olle Sjögren

2013-04-14 20:15:16 +00:00
Commented Apr 14, 2013 at 20:15 — Olle Sjögren
– Olle Sjögren, Commented Apr 14, 2013 at 20:15

Dick Kusleika · Accepted Answer · 2013-04-15 18:28:10Z

1

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.

answered Apr 15, 2013 at 18:28

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Reading XML Websites in VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related