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

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: For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

   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:

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

REFERENCES

For more information about Extensible Markup Language (XML) and Extensible Stylesheet Language (XSL), see the following Microsoft Web site:

back to the top