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 t开发者_如何学JAVAabular 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?

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!