Saving a Range of Cells as an XML Document

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>

0 0

Post a comment