Microsoft KB Archive/305795

= HOW TO: Import and Export XML in Excel =

Article ID: 305795

Article Last Modified on 10/27/2002

-

APPLIES TO


 * Microsoft Office XP Developer Edition

-



This article was previously published under Q305795





IN THIS TASK
SUMMARY
 * Requirements
 * Manually Exporting XML Data
 * Programmatically Exporting XML Data
 * Manually Importing XML Data
 * Programmatically Importing XML Data
 * Verifying it Works



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
 In an Excel Visual Basic for Applications project, insert a new module. In the module create a subroutine called ExportXML.  Type or paste the following code: '  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:=&quot;&quot;, _ 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
 In an Excel Visual Basic for Applications project, insert a new module.</li> In the module create a subroutine called ImportXML.</li>  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 &quot;<http://Myserver/Myfolder/xml filename.xml>&quot;, _

</li></ol>

back to the top

Verifying that it Works

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

back to the top

Keywords: kbhowto kbhowtomaster KB305795

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.