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.
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."
If prompted, select a style sheet from the list of options, or click "Cancel" if you don't want to use a style sheet.
Select "Save" and choose "XLS (Excel)" or the XLSX format to make the conversion permanent.
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.
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.)
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>", _
This code instructs Excel to use the content of the designated XML file to create and open a new workbook file, formatted using the designated style sheet. A workbook file is made up of one or more related worksheets.
If you use programmatical importing, you can repeat the code using the filenames and locations of different XML documents.