How to Import XML file in Excel
Manually Importing XML Data
Start Excel and then click Open on the File menu.
In the Files of type list, select XML files.
Select the XML file you want to open and then click Open.
If the file has multiple XSL style sheets, you are prompted to select one of the style sheets. Select a style sheet if you want to appy a style sheet. Or, click Cancel to not select a style sheet.
Programmatically Importing XML Data
In an Excel Visual Basic for Applications project, insert a new module.
In the module create a subroutine called ImportXML.
Type or paste the following code:
' represents the Web server
' location and the name of the XML file you want to save. You can also
' specify a folder on your hard disk rather than a URL.
' If the XML file references more than one style sheet,
' use the StyleSheets parameter to specify one or more style
' sheets. If you want to apply only one referenced style sheet,
' specify the index number of that sheet reference in the document.
' If you want to apply multiple style sheets, enter all
' the index numbers as members of an array, similar to this:
' Array(1, 2)
' In this case, the first and second style sheets are referenced in
' the XML file.
Workbooks.OpenXML "", _