How to Convert XML to Excel

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.

You don't have to use code to import XML documents.

Manually Importing

Step

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

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

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

Programmatically Importing

Step

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

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

Insert the following code, replacing the text inside the pointed brackets with the relevant filename and location, and style sheet:

Workbooks.OpenXML "<http://Myserver/Myfolder/xml filename.xml>", _