Excel VBA import xml file

Question

I've used google but i can't seem to find a simple answer to the following question:

How do I import a XML file into an Excel worksheet using VBA. So for example, i have a file named test.xml in the folder c:\test. I want excel to copy the data of test.xml to the second sheet using VBA

Answer 1

On the Data tab >> Get External Data >> From other sources >> From XML data import

Recording a macro while doing this gave me:

ActiveWorkbook.XmlImport URL:= "C:\Stuff\test.xml", ImportMap:=Nothing, _
                         Overwrite:=True, Destination:=Range("$A$1")

Start with that and see if it works for your file. If you run into problems you'll need to post a sample of the actual XML.