Excel 2002-03 range2xml question

**stanl** · 10-20-2005, 07:21 AM

I have attached a zip file which contains a workbook and macro to produce xml from a range within the workbook. What I am trying to understand is how Excel handles a range2xml procedure with an ADO Stream. The macro in the workbook

Sub MakeXML()
Dim cFile, cFile1, oStream, oRS
cFile = "C:\Test\text.xml"
cFile1 = "C:\Test\stream.xml"
If FileExists(cFile) Then Kill cFile
If FileExists(cFile1) Then Kill cFile1
'create output as standard Ascii
Open cFile For Output As 1
Print #1, ActiveSheet.Range("A2.H23").Value(xlRangeValueMSPersistXML)
Close #1
'create and persist an ADO Stream
Set oStream = CreateObject("ADODB.Stream")
'the default charset is Unicode
'but for readibility set to ascii
oStream.Charset = "ascii"
oStream.Type = 2
oStream.Open
oStream.WriteText ActiveSheet.Range("A2.H23").Value(xlRangeValueMSPersistXML)
oStream.SaveToFile cFile1, 2
oStream.Close
oStream = 0
 
'now treat both files as ADO Recordset
Set oRS = CreateObject("ADODB.Recordset")
oRS.CursorLocation = adUseClient
oRS.Open cFile, "Provider=MSPersist;", 1, 4, 256
MsgBox "Field 1 Value " & oRS.Fields(0).Value
'this next line should cause an error even though it appears
'to open as a valid recordset
 
oRS.Save cFile, 1
oRS.Close
oRS.Open cFile1, "Provider=MSPersist;", 1, 4, 256
MsgBox "Field 1 Value " & oRS.Fields(0).Value
 
'this next line should cause an error
oRS.Save cFile1, 1
oRS.Close
oRS = 0
End Sub
Private Function FileExists(fname) As Boolean
' Returns TRUE if the file exists
	Dim x As String
	x = Dir(fname)
	If x <> "" Then FileExists = True _
		Else FileExists = False
End Function

Text.xml and stream.xml are almost identical, but both include a PivotCache element in the xml

<xml xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
<x:PivotCache>
<x:CacheIndex>1</x:CacheIndex>
<s:Schema id="RowsetSchema">
<s:ElementType name="row" content="eltOnly">
	<s:attribute type="Col1"/>
	<s:attribute type="Col2"/>
	<s:attribute type="Col3"/>
	<s:attribute type="Col4"/>
	<s:attribute type="Col5"/>
	<s:attribute type="Col6"/>
	<s:attribute type="Col7"/>
	<s:attribute type="Col8"/>
	<s:extends type="rs:rowbase"/>
</s:ElementType>
<s:AttributeType name="Col1" rs:name="G5523">
	<s:datatype dt:maxLength="255"/>
</s:AttributeType>
<s:AttributeType name="Col2" rs:name="2004">
	<s:datatype dt:type="int"/>
</s:AttributeType>
<s:AttributeType name="Col3" rs:name="Acura">
	<s:datatype dt:maxLength="255"/>
</s:AttributeType>
<s:AttributeType name="Col4" rs:name="TL">
	<s:datatype dt:maxLength="255"/>
</s:AttributeType>
<s:AttributeType name="Col5" rs:name="IMPORT">
	<s:datatype dt:maxLength="255"/>
</s:AttributeType>
<s:AttributeType name="Col6" rs:name="Silver">
	<s:datatype dt:maxLength="255"/>
</s:AttributeType>
<s:AttributeType name="Col7" rs:name="14,039">
	<s:datatype dt:type="int"/>
</s:AttributeType>
<s:AttributeType name="Col8" rs:name="11/30/2004">
	<s:datatype dt:type="dateTime"/>
</s:AttributeType>
</s:Schema>
<rs:data>
<z:row Col1="G5524" Col2="2004" Col3="Chrysler" Col4="Pacifica"
	Col5="DOMESTIC" Col6="White" Col7="24392" Col8="2004-12-01T00:00:00"/>
<z:row Col1="G5532" Col2="2004" Col3="Dodge" Col4="Intrepid" Col5="DOMESTIC"
	Col6="Silver" Col7="67807" Col8="2004-12-01T00:00:00"/>
<z:row Col1="XG5488" Col2="2004" Col3="Dodge" Col4="Neon" Col5="DOMESTIC"
	Col6="White" Col7="16708" Col8="2004-10-15T00:00:00"/>
<z:row Col1="G5472" Col2="2004" Col3="Honda" Col4="Civic" Col5="IMPORT"
	Col6="White" Col7="7508" Col8="2004-09-23T00:00:00"/>
<z:row Col1="G5453" Col2="2004" Col3="Hyundai" Col4="Elantra" Col5="IMPORT"
	Col6="Gold" Col7="19734" Col8="2004-09-02T00:00:00"/>
<z:row Col1="XG5517" Col2="2004" Col3="Jeep" Col4="Liberty" Col5="TRUCK"
	Col6="Silver" Col7="13242" Col8="2004-11-10T00:00:00"/>
<z:row Col1="G5504" Col2="2004" Col3="Mitsubishi" Col4="Lancer (Mitsubishi)"
	Col5="IMPORT" Col6="Black" Col7="10218" Col8="2004-11-04T00:00:00"/>
<z:row Col1="G5475" Col2="2004" Col3="Toyota" Col4="Corolla" Col5="IMPORT"
	Col6="White" Col7="21000" Col8="2004-09-24T00:00:00"/>
<z:row Col1="ZH4469A" Col2="2003" Col3="Acura" Col4="TL" Col5="IMPORT"
	Col6="Black" Col7="45493" Col8="2004-09-11T00:00:00"/>
<z:row Col1="G5184" Col2="2003" Col3="Audi" Col4="TT" Col5="IMPORT"
	Col6="Blue" Col7="0" Col8="2003-12-31T00:00:00"/>
<z:row Col1="G5016" Col2="2003" Col3="Buick" Col4="Century" Col5="DOMESTIC"
	Col7="17744" Col8="2004-09-30T00:00:00"/>
<z:row Col1="ZP4399A" Col2="2003" Col3="Cadillac" Col4="CTS" Col5="DOMESTIC"
	Col6="Gold" Col7="48389" Col8="2004-07-31T00:00:00"/>
<z:row Col1="G5037" Col2="2003" Col3="Ford" Col4="Taurus" Col5="DOMESTIC"
	Col6="Green" Col7="20816" Col8="2004-09-30T00:00:00"/>
<z:row Col1="G5521" Col2="2003" Col3="Honda" Col4="Accord" Col5="IMPORT"
	Col6="Gold" Col7="22323" Col8="2004-11-15T00:00:00"/>
<z:row Col1="G5469" Col2="2003" Col3="Mazda" Col4="Mazda6" Col5="IMPORT"
	Col6="White" Col7="26864" Col8="2004-09-22T00:00:00"/>
<z:row Col1="G5465" Col2="2003" Col3="Mitsubishi" Col4="Galant" Col5="IMPORT"
	Col6="Red" Col7="25962" Col8="2004-09-10T00:00:00"/>
<z:row Col1="G5364" Col2="2003" Col3="Mitsubishi" Col4="Galant" Col5="IMPORT"
	Col6="Silver" Col7="13841" Col8="2004-05-28T00:00:00"/>
<z:row Col1="G5464" Col2="2003" Col3="Mitsubishi" Col4="Galant" Col5="IMPORT"
	Col6="Silver" Col7="24512" Col8="2004-09-10T00:00:00"/>
<z:row Col1="G5445" Col2="2003" Col3="Mitsubishi" Col4="Lancer (Mitsubishi)"
	Col5="IMPORT" Col6="White" Col7="41706" Col8="2004-08-30T00:00:00"/>
<z:row Col1="G5454" Col2="2003" Col3="Mitsubishi" Col4="Outlander"
	Col5="TRUCK" Col6="Silver" Col7="28248" Col8="2004-09-02T00:00:00"/>
<z:row Col1="G5531" Col2="2003" Col3="Pontiac" Col4="Grand Am"
	Col5="DOMESTIC" Col6="Red" Col7="65849" Col8="2004-12-01T00:00:00"/>
</rs:data>
</x:PivotCache>
</xml>?

and notice the eof character... If I open the workbook externally via a .wsc and perform the same stream operation, the files are identical - however, the text file based on Output As.. resembles a a persisted recordset and can be both opened and saved without error.

<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
<s:ElementType name='row' content='eltOnly' rs:updatable='true'>
<s:AttributeType name='G5523' rs:number='1'>
<s:datatype dt:type='string' dt:maxLength='255'/>
</s:AttributeType>
<s:AttributeType name='c1' rs:name='2004' rs:number='2'>
<s:datatype dt:type='int' dt:maxLength='4' rs:fixedlength='true'/>
</s:AttributeType>
<s:AttributeType name='Acura' rs:number='3'>
<s:datatype dt:type='string' dt:maxLength='255'/>
</s:AttributeType>
<s:AttributeType name='TL' rs:number='4'>
<s:datatype dt:type='string' dt:maxLength='255'/>
</s:AttributeType>
<s:AttributeType name='IMPORT' rs:number='5'>
<s:datatype dt:type='string' dt:maxLength='255'/>
</s:AttributeType>
<s:AttributeType name='Silver' rs:number='6'>
<s:datatype dt:type='string' dt:maxLength='255'/>
</s:AttributeType>
<s:AttributeType name='c6' rs:name='14,039' rs:number='7'>
<s:datatype dt:type='int' dt:maxLength='4' rs:fixedlength='true'/>
</s:AttributeType>
<s:AttributeType name='c7' rs:name='11/30/2004' rs:number='8'>
<s:datatype dt:type='dateTime' rs:dbtype='timestamp' dt:maxLength='16' rs:scale='255' rs:fixedlength='true'/>
</s:AttributeType>
<s:extends type='rs:rowbase'/>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row G5523='G5524' c1='2004' Acura='Chrysler' TL='Pacifica' IMPORT='DOMESTIC' Silver='White' c6='24392' c7='2004-12-01T00:00:00'/>
<z:row G5523='G5532' c1='2004' Acura='Dodge' TL='Intrepid' IMPORT='DOMESTIC' Silver='Silver' c6='67807' c7='2004-12-01T00:00:00'/>
<z:row G5523='XG5488' c1='2004' Acura='Dodge' TL='Neon' IMPORT='DOMESTIC' Silver='White' c6='16708' c7='2004-10-15T00:00:00'/>
<z:row G5523='G5472' c1='2004' Acura='Honda' TL='Civic' IMPORT='IMPORT' Silver='White' c6='7508' c7='2004-09-23T00:00:00'/>
<z:row G5523='G5453' c1='2004' Acura='Hyundai' TL='Elantra' IMPORT='IMPORT' Silver='Gold' c6='19734' c7='2004-09-02T00:00:00'/>
<z:row G5523='XG5517' c1='2004' Acura='Jeep' TL='Liberty' IMPORT='TRUCK' Silver='Silver' c6='13242' c7='2004-11-10T00:00:00'/>
<z:row G5523='G5504' c1='2004' Acura='Mitsubishi' TL='Lancer (Mitsubishi)' IMPORT='IMPORT' Silver='Black' c6='10218'
c7='2004-11-04T00:00:00'/>
<z:row G5523='G5475' c1='2004' Acura='Toyota' TL='Corolla' IMPORT='IMPORT' Silver='White' c6='21000' c7='2004-09-24T00:00:00'/>
<z:row G5523='ZH4469A' c1='2003' Acura='Acura' TL='TL' IMPORT='IMPORT' Silver='Black' c6='45493' c7='2004-09-11T00:00:00'/>
<z:row G5523='G5184' c1='2003' Acura='Audi' TL='TT' IMPORT='IMPORT' Silver='Blue' c6='0' c7='2003-12-31T00:00:00'/>
<z:row G5523='G5016' c1='2003' Acura='Buick' TL='Century' IMPORT='DOMESTIC' c6='17744' c7='2004-09-30T00:00:00'/>
<z:row G5523='ZP4399A' c1='2003' Acura='Cadillac' TL='CTS' IMPORT='DOMESTIC' Silver='Gold' c6='48389' c7='2004-07-31T00:00:00'/>
<z:row G5523='G5037' c1='2003' Acura='Ford' TL='Taurus' IMPORT='DOMESTIC' Silver='Green' c6='20816' c7='2004-09-30T00:00:00'/>
<z:row G5523='G5521' c1='2003' Acura='Honda' TL='Accord' IMPORT='IMPORT' Silver='Gold' c6='22323' c7='2004-11-15T00:00:00'/>
<z:row G5523='G5469' c1='2003' Acura='Mazda' TL='Mazda6' IMPORT='IMPORT' Silver='White' c6='26864' c7='2004-09-22T00:00:00'/>
<z:row G5523='G5465' c1='2003' Acura='Mitsubishi' TL='Galant' IMPORT='IMPORT' Silver='Red' c6='25962' c7='2004-09-10T00:00:00'/>
<z:row G5523='G5364' c1='2003' Acura='Mitsubishi' TL='Galant' IMPORT='IMPORT' Silver='Silver' c6='13841' c7='2004-05-28T00:00:00'/>
<z:row G5523='G5464' c1='2003' Acura='Mitsubishi' TL='Galant' IMPORT='IMPORT' Silver='Silver' c6='24512' c7='2004-09-10T00:00:00'/>
<z:row G5523='G5445' c1='2003' Acura='Mitsubishi' TL='Lancer (Mitsubishi)' IMPORT='IMPORT' Silver='White' c6='41706'
c7='2004-08-30T00:00:00'/>
<z:row G5523='G5454' c1='2003' Acura='Mitsubishi' TL='Outlander' IMPORT='TRUCK' Silver='Silver' c6='28248' c7='2004-09-02T00:00:00'/>
<z:row G5523='G5531' c1='2003' Acura='Pontiac' TL='Grand Am' IMPORT='DOMESTIC' Silver='Red' c6='65849' c7='2004-12-01T00:00:00'/>
</rs:data>
</xml>

in all cases, the file is created from:

ActiveSheet.Range("A2.H23").Value(xlRangeValueMSPersistXML)

So, why (1) the inability to re-save (2) the difference in schema format. This is not a purely academic question, as it refers to future applications involving persisting discreet ranges to multiple charsets.

TIA

Stan

**stanl** · 10-20-2005, 10:44 AM

This would seem to eliminate the error, but still leave the part of the question about the different schemas...

If FileExists(cFile1) Then Kill cFile1
xmlRange = ActiveSheet.Range("A2.H23").Value(xlRangeValueMSPersistXML)
'create output as standard Ascii
Open cFile For Output As 1
Print #1, Mid(xmlRange, 1, Len(xmlRange) - 1)
Close #1
 
'create and persist an ADO Stream
Set oStream = CreateObject("ADODB.Stream")
'the default charset is Unicode
'but for readibility set to ascii
oStream.Charset = "ascii"
oStream.Type = 2
oStream.Open
oStream.WriteText Mid(xmlRange, 1, Len(xmlRange) - 1)
oStream.SaveToFile cFile1, 2
oStream.Close
oStream = 0
 
'now treat both files as ADO Recordset
Set oRS = CreateObject("ADODB.Recordset")
oRS.CursorLocation = adUseClient
oRS.Open cFile, "Provider=MSPersist;", 1, 4, 256
MsgBox "Field 1 Value " & oRS.Fields(0).Value
oRS.Save cFile, 1
oRS.Close
oRS.Open cFile1, "Provider=MSPersist;", 1, 4, 256
MsgBox "Field 1 Value " & oRS.Fields(0).Value
oRS.Save cFile1, 1
oRS.Close
oRS = 0
End Sub

Stan

**stanl** · 10-20-2005, 01:28 PM

Ok, think I have it. The extra BSTR in the XML is peculiar to Excel. Once removed and re-persisted as a standard Recordset, the PivotCache elements are replaced by standard ADO schema. I'll mark this as solved, but a final question - do the VBA mid() and len() functions require augmentation of offsets to handle data persisted as Unicode?

again, Thanx

Stan

Thread: Excel 2002-03 range2xml question

Thread Tools

Display

Excel 2002-03 range2xml question

Posting Permissions