Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
How to transfer data from an ADO Recordset to Excel with automation
Article ID: 246335 - View products that this article applies to.
This article was previously published under Q246335
You can transfer the contents of an ADO recordset to a Microsoft Excel worksheet by automating Excel. The approach that you can use depends on the version of Excel you are automating. Excel 97, Excel 2000, and Excel 2002 have a CopyFromRecordset method that you can use to transfer a recordset to a range. CopyFromRecordset in Excel 2000 and 2002 can be used to copy either a DAO or an ADO recordset. However, CopyFromRecordset in Excel 97 supports only DAO recordsets. To transfer an ADO recordset to Excel 97, you can create an array from the recordset and then populate a range with the contents of that array.
This article discusses both approaches. The sample code presented illustrates how you can transfer an ADO recordset to Excel 97, Excel 2000, Excel 2002, Excel 2003, or Excel 2007.
The code sample provided below shows how to copy an ADO recordset to a Microsoft Excel worksheet using automation from Microsoft Visual Basic. The code first checks the version of Excel. If Excel 2000 or 2002 is detected, the CopyFromRecordset method is used because it is efficient and requires less code. However, if Excel 97 or earlier is detected, the recordset is first copied to an array using the GetRows method of the ADO recordset object. The array is then transposed so that records are in the first dimension (in rows), and fields are in the second dimension (in columns). Then, the array is copied to an Excel worksheet through assigning the array to a range of cells. (The array is copied in one step rather than looping through each cell in the worksheet.)
The code sample uses the Northwind sample database that is included with Microsoft Office. If you selected the default folder when you installed Microsoft Office, the database is located in:
\Program Files\Microsoft Office\Office\Samples\Northwind.mdb
If the Northwind database is located in a different folder on your computer, you need to edit the path of the database in the code provided below.
If you do not have the Northwind database installed on your system, you can use the Add/Remove option for Microsoft Office setup to install the sample databases.
Note The Northwind database is not installed when you install 2007 Microsoft Office. To obtain Northwind 2007, visit the following Microsoft Web site:
Steps to Create Sample
For efficiency and performance, CopyFromRecordset is the preferred method. Because Excel 97 supports only DAO recordsets with CopyFromRecordset, if you attempt to pass an ADO recordset to CopyFromRecordset with Excel 97, you receive the following error:
In the code sample, you can avoid this error by checking Excel's version so that you do not use CopyFromRecordset for the 97 version.
Run-time error 430:
Class does not support Automation or does not support expected interface.
Note When using CopyFromRecordset, you should be aware that the ADO or DAO recordset you use cannot contain OLE object fields or array data such as hierarchical recordsets. If you include fields of either type in a recordset, the CopyFromRecordset method fails with the following error:
Run-time error -2147467259:
Method CopyFromRecordset of object Range failed.
If Excel 97 is detected, use the GetRows method of the ADO recordset to copy the recordset into an array. If you assign the array returned by GetRows to a range of cells in the worksheet, the data goes across the columns instead of down the rows. For example, if the recordset has two fields and 10 rows, the array appears as two rows and 10 columns. Therefore, you need to transpose the array using your TransposeDim() function before assigning the array to the range of cells. When assigning an array to a range of cells, there are some limitations to be aware of:
The following limitations apply when assigning an array to an Excel Range object:
If you decide to use Excel's Transpose method instead of the TransposeDim() function to transpose the array, you should be aware of the following limitations with the Transpose method:
Run-time Error 13: Type Mismatch
Run-time Error 5: Invalid procedure call or argument
Run-time Error 1004: Application defined or object defined error
For additional information about limitations on passing arrays to various versions of Excel, click the following article number to view the article in the Microsoft Knowledge Base:
177991For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/177991/ )XL: Limitations of Passing Arrays to Excel Using Automation
(http://support.microsoft.com/kb/146406/EN-US/ )XL: How to Retrieve a Table from Access into Excel Using DAO
(http://support.microsoft.com/kb/215965/ )XL2000: 12:00:00 AM Displayed for Dates Earlier Than 1900
(http://support.microsoft.com/kb/243394/ )How To Use MFC to Copy a DAO Recordset to Excel with Automation
(http://support.microsoft.com/kb/247412/ )INFO: Methods for Transferring Data to Excel from Visual Basic
Article ID: 246335 - Last Review: March 28, 2007 - Revision: 5.1