Microsoft KB Archive/295646

= How To Transfer Data from ADO Data Source to Excel with ADO =

Article ID: 295646

Article Last Modified on 8/23/2006

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Data Access Components 2.0
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5
 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q295646



SUMMARY
Because Microsoft Excel is such a powerful data analysis tool, Visual Basic and VBA application developers often want to bring data into an Excel worksheet for analysis purposes. This article describes the ActiveX Data Objects (ADO) approach to this programming task, using Microsoft Jet-specific syntax for certain SQL commands.

This article describes programming solutions and does not describe Excel's menu-driven data import capabilities (through the Open option on the File menu) or the use of Microsoft Query (from the Get External Data option on the Data menu).

For additional information about complementary programming solutions that make use of Excel Automation instead of SQL commands, click the article number below to view the article in the Microsoft Knowledge Base:

246335 How To Transfer Data from ADO Recordset to Excel with Automation



MORE INFORMATION
In the examples that follow, cnSrc represents an open ADO connection to the Northwind sample Jet database using the Jet 4.0 OLE DB Provider.

How to Copy
You can use the SELECT INTO statement to copy data from any data source that Jet can read into any data destination, creating a new table (or, in the case of Excel, a new worksheet) on the fly. Do not use the dollar sign syntax, for example [Sheet1$], when you refer to a sheet name as your destination. The destination workbook can exist or not exist; however, the destination sheet must not yet exist.

There are three ways to write the copy command that copies the entire Customers table from the Microsoft Access Northwind database into a new sheet in an Excel workbook. Each syntax requires a single SQL statement and creates column headings in the first row of the destination worksheet.   The following example uses the SELECT INTO syntax: Dim strSQL As String strSQL = &quot;SELECT * INTO [Excel 8.0;Database=&quot; & App.Path & _ &quot;\book1.xls].[Sheet1] FROM Customers&quot; cnSrc.Execute strSQL   The following example uses the SELECT INTO ... IN syntax: strSQL = &quot;SELECT * INTO [Sheet1] IN '' [Excel 8.0;Database=&quot; & App.Path & _ &quot;\book1.xls] FROM Customers&quot; where the bracketed destination database information is preceded by an empty pair of single quotes for the type argument (the &quot;Excel 8.0&quot; portion), which is included within the brackets when you use this syntax.   The following example uses the alternate syntax for the IN clause: strSQL = &quot;SELECT * INTO [Sheet1] IN '&quot; & App.Path & _ &quot;\book1.xls' 'Excel 8.0;' FROM Customers&quot; where the type argument is now listed separately after the destination file path. 

How to Append
You can use the INSERT INTO ... IN statement to append data from any data source that Jet can read into any data destination. Both the destination workbook and worksheet must exist. Now that you are referring to an existing worksheet, you must use the standard dollar sign syntax, for example, [Sheet1$], when you refer to a sheet name as your destination. In addition, the column headings must already be present; in other words, this statement can only be used to append to an existing table.

There are two ways to write the append command that copies the entire Customers table from the Northwind database into an existing Excel worksheet, which already has the appropriate column headings.   The following example uses the INSERT INTO ... IN syntax: strSQL = &quot;INSERT INTO [Sheet1$] IN '' [Excel 8.0;Database=&quot; & App.Path & _ &quot;\book1.xls] SELECT * FROM Customers&quot; where the bracketed destination database information is again preceded by an empty pair of quotes for the type argument, which is now included within the brackets.   The following example uses the alternate syntax for the IN clause: strSQL = &quot;INSERT INTO [Sheet1$] IN '&quot; & App.Path & _ &quot;\book1.xls' 'Excel 8.0;' SELECT * FROM Customers&quot; </ul>

<div class="references_section">