Article ID: 295005 - Last Review: December 13, 2006 - Revision: 2.3

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

View products that this article applies to.

This article was previously published under Q295005

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.

Start the Visual Basic Editor.
On the Tools menu, click References.
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 Microsoft Advisory Services. For more information, visit these Microsoft Web sites:

Microsoft Certified Partners - https://partner.microsoft.com/global/30000104 (https://partner.microsoft.com/global/30000104)

Microsoft Advisory Services - http://support.microsoft.com/gp/advisoryservice (http://support.microsoft.com/gp/advisoryservice)

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 (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 "C:\XMLSpreadhseetFile.xml"

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

      '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 ("C:\GenericXMLFile.xml")

   End Sub

NOTE: Microsoft Excel security is set at "high" by default. Unsigned Visual Basic for Applications macros do not run in this environment. Change the macro security level to "medium" to run this code. To do this, follow these steps: