Microsoft KB Archive/295005

= HOW TO: Export an XML Spreadsheet File into Generic XML in Excel 2002 =

Article ID: 295005

Article Last Modified on 12/13/2006

-

APPLIES TO


 * Microsoft Excel 2002 Standard Edition

-



This article was previously published under Q295005



IN THIS TASK
SUMMARY
 * How to Add a Reference to the Microsoft XML Version 2.0 Object Library
 * How to Create the Sub Procedure to Create a Generic XML File from an XML Spreadsheet

REFERENCES



SUMMARY
This step-by-step article describes how you can create a generic Extensible Markup Language (XML) file from an XML Spreadsheet file by using an Extensible Stylesheet Language (XSL) template.

back to the top

How to Add a Reference to the Microsoft XML Version 2.0 Object Library
You can use Microsoft Visual Basic for Applications to transform an XML spreadsheet file into a generic XML file. To do this, you can use a customized XSL file to define the order of the data in the new XML file.

The following example uses the Microsoft XML version 2.0 object model to access the DOMDocument object. This procedure demonstrates how to reference the Microsoft XML version 2.0 object library to use the DOMDocument object.
 * 1) Start the Visual Basic Editor.
 * 2) On the Tools menu, click References.
 * 3) Under Available References, click to select the Microsoft XML, version 2.0 check box, and then click OK.

back to the top

How to Create the Sub Procedure to Create a Generic XML File from an XML Spreadsheet
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

https://partner.microsoft.com/global/30000104

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

Sub ConvertToGeneric

'Declare three DOMDocument objects. Dim SourceXML As New DOMDocument Dim SourceXSL As New DOMDocument Dim GenericXML As New DOMDocument

'Load the XMLSpreadsheet file into a DOMDocument object. SourceXML.Load &quot;C:\XMLSpreadhseetFile.xml&quot;

'Load the XSLStyleSheet file into a DOMDocument object. SourceXSL.Load &quot;C:\XSLSytleSheetFile.xsl&quot;

'Apply the XSL style sheet to the XML spreadsheet file and send it     'to the GenericXML DOMDocument object. SourceXML.transformNodeToObject Stylesheet:=SourceXSL, _ OutputObject:=GenericXML

'Save the generic XML file as C:\GenericXMLFile.xml GenericXML.Save (&quot;C:\GenericXMLFile.xml&quot;)

End Sub NOTE: Microsoft Excel security is set at &quot;high&quot; by default. Unsigned Visual Basic for Applications macros do not run in this environment. Change the macro security level to &quot;medium&quot; to run this code. To do this, follow these steps:
 * 1) On the Tools menu, point to Macro, and then click Security.
 * 2) Click the Security Level tab, click Medium, and then click OK.
 * 3) On the File menu, click Exit to quit Microsoft Excel.

NOTE: You must quit and restart Microsoft Excel for the security level change to take effect.

back to the top