HOW TO: Import and Export XML in Excel (305795)

SUMMARY

Use this step-by-step guide to using Microsoft Excel to manually and programmatically import and export XML formatted data.

XML is the defacto industry standard for transporting data around the Internet. Both Microsoft Excel and Microsoft Access support importing and exporting XML data.

back to the top

Requirements

You should have knowledge of the following:

• The basics of Excel.
• Programming concepts.
• XML concepts.

back to the top

Manually Exporting XML Data

1. On the File menu, click Save As.
2. In the Save as type list, select XML spreadsheet.
3. Name your file. If you are saving the file to a Web server, include the address. Click Save.

If there are features in the workbook that are unsupported in XML, such as Visual Basic for Applications code, a message indicating this will appear.

back to the top

Programmatically Exporting XML Data

1. In an Excel Visual Basic for Applications project, insert a new module.
2. In the module create a subroutine called ExportXML.
3. Type or paste the following code:

   ' <http://Myserver/Myfolder/xml filename.xml> 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.
   '
   ActiveWorkbook.SaveAs _
      Filename:="<http://Myserver/Myfolder/xml filename.xml>", _
      FileFormat:=xlXMLSpreadsheet
   					

back to the top

Manually Importing XML Data

1. Start Excel and then click Open on the File menu.
2. In the Files of type list, select XML files.
3. Select the XML file you want to open and then click Open.
4. 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.

back to the top

Programmatically Importing XML Data

1. In an Excel Visual Basic for Applications project, insert a new module.
2. In the module create a subroutine called ImportXML.
3. Type or paste the following code:

   ' <http://Myserver/Myfolder/xml filename.xml> 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 "<http://Myserver/Myfolder/xml filename.xml>", _
   	<style sheet>
   
   					

back to the top

Verifying that it Works

1. Create an XML file.
2. Run the code in Excel.

back to the top