Saving a Range of Cells as an XML Document

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

In the beginning of this chapter you learned how to save an Excel spreadsheet in the XML-SS spreadsheet format, both by using File | Save As and programmatically via the Save As method of the Workbook object and the special parameter xlXMLSpreadsheet. At times, however, it may be desirable to save only a specific range of cells in the XML format. While the user interface does not offer such an option, Excel 2002 now offers a Value property of the Range object with the xlRangeValueXMLSpreadsheet parameter to let you programmatically retrieve or set a range as an XML spreadsheet. Let's look at how you can persist an Excel range in an XML document. We will use the Equipment.xls file shown in Figure 17-2 earlier and demonstrate how to use VBA to save cell ranges in XML format.

1. Open the Equipment.xls file illustrated in Figure 17-2.

2. Switch to the Visual Basic Editor window and insert a new module.

3. Type the following procedure in a module:

Sub SaveRangeAsXML_Spreadsheet() Dim objFSO As Object Dim objTextFile As Object Dim myRange As Range Dim strGetThisRange As String Dim strFile As String strFile = "C:\myRange.xml"

Set objFSO = CreateObject("Scripting.FileSystemObject") Set objTextFile = objFSO.CreateTextFile(strFile, True)

Set myRange = Worksheets("Units").Range("A2:E6")

' retrieve the range as XML spreadsheet strGetThisRange = myRange.Value(xlRangeValueXMLSpreadsheet)

' write the string to the Immediate window Debug.Print strGetThisRange ' Write the string to a file objTextFile.Write strGetThisRange objTextFile.Close

'open the newly prepared XML document in Excel Workbooks.Open strFile End Sub

The procedure shown above retrieves the specified range of cells ("A2:E6") as an XML spreadsheet by passing the xlRangeValueXMLSpreadsheet parameter to the Value property of the Range object. The XML representation of a selected range of cells in the spreadsheet is printed to the Immediate window and saved to a file using the CreateTextFile method of the FileSystemObject (see Chapter 8 for details on creating and working with text files). The source code of the XML document (MyRange.xml) created by the SaveRangeAsXML_Spreadsheet procedure is presented below:

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40"> <Styles>

<Style ss:ID="Default" ss:Name="Normal"> <Alignment ss:Vertical="Bottom"/> <Borders/> <Font/> <Interior/> <NumberFormat/> <Protection/> </Style>

<Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/> </Style>

<Style ss:ID="s22"> <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/> <Font x:Family="Swiss" ss:Bold="1"/> <Interior ss:Color="#CCFFCC" ss:Pattern="Solid"/> </Style> </Styles>

<Worksheet ss:Name="Units">

<Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="5"> <Column ss:Index="3" ss:AutoFitWidth="0" ss:Width="89.25"/> <Column ss:Width="149.25"/> <Row ss:AutoFitHeight="0" ss:Height="24">

<Cell ss:StyleID="s22"><Data ss:Type="String">Category</Data></Cell> <Cell ss:StyleID="s22"><Data ss:Type="String">Type</Data></Cell> <Cell ss:StyleID="s22"><Data ss:Type="String">Model</Data></Cell> <Cell ss:StyleID="s22"><Data ss:Type="String">Description</Data></Cell> <Cell ss:StyleID="s22"><Data ss:Type="String">Units</Data></Cell> </Row> <Row>

<Cell ss:StyleID="s21"><Data ss:Type="Number">1</Data></Cell> <Cell ss:StyleID="s21"><Data ss:Type="String">WS</Data></Cell> <Cell ss:StyleID="s21"><Data ss:Type="String">GX 1/L</Data></Cell> <Cell ss:StyleID="s21"><Data ss:Type="String">Dell PIII 500 MHz</Data></Cell> <Cell><Data ss:Type="Number">12</Data></Cell> </Row> <Row>

<Cell ss:StyleID="s21"><Data ss:Type="Number">1</Data></Cell> <Cell ss:StyleID="s21"><Data ss:Type="String">WS</Data></Cell> <Cell ss:StyleID="s21"><Data ss:Type="String">GX110</Data></Cell> <Cell ss:StyleID="s21"><Data ss:Type="String">Dell PIII 533 MHz</Data></Cell> <Cell><Data ss:Type="Number">10</Data></Cell> </Row> <Row>

<Cell ss:StyleID="s21"><Data ss:Type="Number">1</Data></Cell>

<Cell ss:StyleID="s21"><Data ss:Type="Stnng">WS</Data></Cell> <Cell ss:StyleID="s21"><Data ss:Type="String">GX110</Data></Cell> <Cell ss:StyleID="s21"><Data ss:Type="Stnng">Dell PIII 667 MHz</Data></Cell> <Cell><Data ss:Type="Number">8</Data></Cell> </Row> <Row>

<Cell ss:StyleID="s21"><Data ss:Type="Number">1</Data></Cell> <Cell ss:StyleID="s21"><Data ss:Type="String">WS</Data></Cell> <Cell ss:StyleID="s21"><Data ss:Type="String">GX110</Data></Cell> <Cell ss:StyleID="s21"><Data ss:Type="String">Dell PIII 866 MHz</Data></Cell> <Cell><Data ss:Type="Number">4</Data></Cell> </Row> </Table> </Worksheet> </Workbook>

Was this article helpful?

0 0

Post a comment