Read XML using VBA Excel Macro and customize the generated table

Question

I am new to Excel VBA. My problem is that I have an XML file and I need to show its contents in a tabular format in Excel.

I have written a sample VBA code as follows:

Dim testFile As Workbook

Set testFile = Workbooks.OpenXML(
Filename:="D:\sample.xml",LoadOption:=XlXmlLoadOption.xlXmlLoadImportToList)

It generates the contents in a tabular format in Excel, but there are a lot of empty cells and some garbage data (data relevant in XML but not needed here). I need to delete the empty cells (both in rows and columns) and also remove some unwanted columns.

Can someone please suggest some approach on how to proceed in this?

Community · Accepted Answer · 2017-05-23 11:47:45Z

1

Depending on how comfortable with VBA you are, you could parse the file yourself. A reference to "Microsoft XML" (msxml#.dll) and a look at this question should be all you need to get you on your way!

edited May 23, 2017 at 11:47

CommunityBot

11 silver badge

answered Feb 2, 2011 at 17:26

Simon Cowen

1,90317 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

Read XML using VBA Excel Macro and customize the generated table

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