HOW TO: Import and Export XML in Excel

Article translations Article translations
Article ID: 305795 - View products that this article applies to.
This article was previously published under Q305795
Expand all | Collapse all

On This Page

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.

Requirements

You should have knowledge of the following:
  • The basics of Excel.
  • Programming concepts.
  • XML concepts.

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.

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
    					

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.

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>
    
    					

Verifying that it Works

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













Properties

Article ID: 305795 - Last Review: October 27, 2002 - Revision: 1.0
APPLIES TO
  • Microsoft Office XP Developer Edition
Keywords: 
kbhowto kbhowtomaster KB305795

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com