CODEWITHSUNDEEP
xmlvbaexcel
Nathan
Nathan

Reputation: 115

Read XML using VBA Excel Macro and customize the generated table

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?

Upvotes: 1

Views: 17215

Answers (1)

Simon Cowen
Simon Cowen

Reputation: 1903

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!

Upvotes: 1

Related Questions