How to Convert XML to Excel

By John Lister

Microsoft Excel offers two main ways to import XML documents. The manual import will be easiest for most users. The programmatical import may be quicker if you want to import two or more files and you are both quick and comfortable with typing or inserting code.

Manually Importing

Step 1

Open Excel, then select the "File" menu and "Open." Select "XML files" from the list of file types. Select the file you want to open and then click "Open."

Step 2

If prompted, select a style sheet from the list of options, or click "Cancel" if you don't want to use a style sheet.

Step 3

Select "Save" and choose "XLS (Excel)" or the XLSX format to make the conversion permanent.

Programmatically Importing

Step 1

Make a note of the server location and XML file name that you want to import. Alternatively, make a note of the file's location if it is on your hard drive. You won't be able to browse for it, so you'll need to know the full directory location.

Step 2

Open a Visual Basic for Applications project in Excel and insert a new module. Create a subroutine named "ImportXML." (You can use a different name if you like, but using "ImportXML" will make future reference easier.)

Step 3

Insert the following code, replacing the text inside the pointed brackets with the relevant filename and location, and style sheet:Workbooks.OpenXML "", _