ADO Recordset to Excel 2007 Worksheet Cell Values via XML

[James Cox]

Given that Office 2007 has messed up some of the nicer ways to transfer query
results from Access to Excel, has anyone worked out a way to take an ADO
recordset in Access and convert it into the proper XML format to be written
to the XML file that holds the worksheet values in Excel 2007?

This has to be done in VBA and while Jan Karel Pieterse tells how to do a
lot of the footwork at

http://www.jkp-ads.com/Articles/Excel2007FileFormat02.asp

I'm wondering if there is an elegant/efficient way to convert the ADO
recordset into the kind of XML that Excel can accept. There are hints that
the recordset can be saved into a DOMDocument, but no information on how to
get it into the right format.

There is a refererence on how to do this in VB.Net at

http://support.microsoft.com/kb/319180

but that's way beyond me...

Anyone done this before?

James

 

[Patrick Molloy]

I haven't tried - I don't have access to Excel 2007 at work,but have you
tried dropping the table into Excel then saving the sheet as xml? or is that
the problem ...

 

[James Cox]

Well, I've now saved a sample ADO recordset in ADO recordset XML format and
opened that .XML file with Excel. Because there was no XML schema associated
with the .XML file, Excel created a schema and opened a new workbook.

The ADO recordset .XML looked like this (sorry about what line wrap may do)

<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:CommandTimeout='30'
rs:UniqueTable='tblActiveSafeObservers' rs:CustomResync='EXEC
sp_resyncexecutesql N'SELECT * FROM dbo.fn_cml_CardRateByAreaMonth ( 2009,
8);' , NULL, ? '
rs:ReshapeName='DSRowset1'>
<s:AttributeType name='EntryYear' rs:number='1' rs:writeunknown='true'>
<s:datatype dt:type='int' dt:maxLength='4' rsrecision='10'
rs:fixedlength='true' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='EntryMonth' rs:number='2' rs:writeunknown='true'>
<s:datatype dt:type='int' dt:maxLength='4' rsrecision='10'
rs:fixedlength='true' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='AreaName' rs:number='3' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='20'
rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='ObsNum' rs:number='4' rs:writeunknown='true'>
<s:datatype dt:type='int' dt:maxLength='4' rsrecision='10'
rs:fixedlength='true' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='CardCount' rs:number='5' rs:nullable='true'
rs:writeunknown='true'>
<s:datatype dt:type='int' dt:maxLength='4' rsrecision='10'
rs:fixedlength='true'/>
</s:AttributeType>
<s:AttributeType name='CardRate' rs:number='6' rs:nullable='true'>
<s:datatype dt:type='float' dt:maxLength='8' rsrecision='15'
rs:fixedlength='true'/>
</s:AttributeType>
<s:extends type='rs:rowbase'/>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row EntryYear='2009' EntryMonth='8' AreaName='CAS' ObsNum='19'
CardCount='7' CardRate='0.37'/>
<z:row EntryYear='2009' EntryMonth='8' AreaName='MH' ObsNum='26'
CardCount='1' CardRate='4.0000000000000001E-2'/>
<z:row EntryYear='2009' EntryMonth='8' AreaName='TXOL' ObsNum='12'/>
</rs:data>
</xml>

What appeared in Excel isn't easy to reproduce here, but it had the
following 24 column headings:

id
name
content
ns3:CommandTimeout
ns3:UniqueTable
ns3:CustomResync
ns3:ReshapeName
name2
ns3:number
ns3:writeunknown
ns3:nullable
ns1:type
ns1:maxLength
ns3recision
ns3:fixedlength
ns3:maybenull
ns3:dbtype
type
EntryYear
EntryMonth
AreaName
ObsNum
CardCount
CardRate

Where the first 18 had what appears to be information/metadata about what
the actual values - which showed up in the last 6 columns.

The first 18 columns had entries in the first 6 rows (corresponding to the
number of actual data columns) and data in the last 6 rows. The last 6
columns had no data / metadata in the first 6 rows, but had values in last 4
rows, which corresponds to the four records that the ADO recordset had in it.

Now - still being totally out of my depth, is there any way to edit the
schema that opening the .XML file in Excel created so that the extra metadata
is used (to format columns and whatever) but not actually written to the
cells?

That is, what I'm looking for is a nice 6 column by 5 rows (one row
headings, 4 rows data) import that echos what was in the recordset.

Any of that help, or just confuse the issue more?

James

 

[James Cox]

Sorry - just noticed some typos that need to be corrected. The elaboration
of what data / metadata / values appeared in the Excel workbook should read:

....where the first 18 columns had what appears to be information/metadata
about how to display the actual values and what the data types are - and the
actual values showed up in the last 6 columns.

The first 18 columns had entries in the first 6 rows (corresponding to the
number of actual data columns) and no info/metadata entries in the last 6
rows. The last 6 columns had no data / metadata in the first 6 rows, but had
the actual values in last 4 rows, which corresponds to the four records that
the ADO recordset had in it.

Now - still being totally out of my depth, is there any way to edit the
schema (which
swas created when Excel opened the .XML file) so that the info/metadata in
the first 18 columns and 6 rows are still used (to format columns and
whatever) but not actually written to the worksheet cells?

That is, what I'm looking for is a nice 6 column by 5 rows import (one row of
headings, 4 rows data) that echos what was in the recordset.

Any of that help, or just confuse the issue more?

 

[James Cox]

To report what I've found:

When you open an xml file via Office Orb | Open, if the xml file doesn't
reference a schema - and xml files created from ADO recordsets don't - Excel
will show you an 'Open XML' pop-up window that has three option buttons:

As an XML table
As a read-only workbook
Use the XML Source task pane

If you select the last option button, a new 'XML Source' task pane is opened
and from it you can drag and drop the fields you want to use into an Excel
worksheet - and that avoids having all the metadata show up on your worksheet.

To get a second ADO recordset's values into the same workbook, you can enter
in the VBA immediate pane the following:

activeworkbook.xmlmaps.Add schema:="\\server\share\ADO_RS_4.xml"

This will display a message that the xlm file does not reference a schema
and that Excel will create one from the structure of the xml file - but the
'Open XML' pop-up window will not be displayed. Go to Developer | XML |
Source and the 'XML Source' task pane will be displaying the new xml_Map
created from the ADO recordset XML file. Drag and drop items into the same
(or a different) worksheet and then rename the xml_Map to something
meaningful.

The above can be repeated as many times as needed...

To bring in the data from the ADO xml file, right click on any of the cells
associated with that recordset and select the XML item and click on the
Refresh sub-menu item.

Hope this helps someone else!

 

[JKP]

HiJames,

A bit late perhaps, but I found this thread only today.

Why would you want to push the recordset into Excel using XML? Do you
know you can use the CopyFromRecordset method to write a recordset to
Excel in one go?

Example (oADOConn is an object variable that holds the connection to
the database):

sSQL = "SELECT * FROM Table"
oRS.Open sSQL, oADOconn
For Each oFld In oRS.Fields
lCount = lCount + 1
oSh.Cells(1, lCount).Value = oFld.Name
Next
If Not oRS.EOF Then
oSh.Range("A2").CopyFromRecordset oRS
End If
oRS.Close
Set oRS = Nothing