1

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?

Gaffi
  • 4,307
  • 8
  • 43
  • 73
Nathan
  • 115
  • 2
  • 4
  • 6

1 Answers1

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!

Community
  • 1
  • 1
Simon Cowen
  • 1,903
  • 16
  • 20