VBA basic script for reading xml data?? Resource for learning this method?

Question

So I was just looking for basic examples of reading xml structure data with visual basic subs/scripts.

specifically, if possible, how can i take structure data of xml, read the data with some vba, and lay it into excel spreadsheets?

or just a learning resource for this type of thing. thanks very much!

There are two basic ways you might approach this given what you explained: 1) Use VBA from within Excel to read an XML doc and populate a spreadsheet or 2) Use VB.NET and COM interop from outside Excel to create an Excel document. The latter would probably be easier. Which method do you want to use? — Ben McCormack
– Ben McCormack, Commented Feb 2, 2010 at 15:12
@Ben, to be honest which ever makes more sense to use is fine, but I would like to learn both actually... — Justin
– Justin, Commented Feb 3, 2010 at 3:13
The more overlooked option is to use ADODB to create a spreadsheet (for those situations when you don't have Excel installed). This is a handy secret to know when you are running scripts on PC's that may not have Excel installed. — Tester101
– Tester101, Commented Feb 4, 2010 at 3:06

Jason Rowe · Accepted Answer · 2010-02-02 04:47:54Z

4

These three topics will help you get started.

1) Load the XML using MSXML hint - XMLDOC.Load ("D:\folder\yourXML.xml")

2) Read the data with XPath or XML navigator

3) Build Excel Spreadsheet Using Microsoft Excel Object Model

answered Feb 2, 2010 at 4:47

Jason Rowe

6,2961 gold badge35 silver badges36 bronze badges

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

Comments

Dick Kusleika · Accepted Answer · 2010-02-02 16:19:06Z

3

http://www.dailydoseofexcel.com/archives/2009/06/16/reading-xml-files-in-vba/

http://www.dailydoseofexcel.com/archives/2010/01/21/culling-you-rss-feeds/

These aren't tutorials, just a couple of examples of XML in Excel that might help you identify the objects properties and methods. The first one from 2009 was my first attempt (read: it's terrible) but the comments were helpful for me so they might be helpful for you too.

answered Feb 2, 2010 at 16:19

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

1 Comment

Ben McCormack Over a year ago

@dkus I'm impressed that you even attempted to read XML from within VBA. Very well done and thanks for the link!

Collectives™ on Stack Overflow

VBA basic script for reading xml data?? Resource for learning this method?

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related