Article ID: 295646 - View products that this article applies to.
This article was previously published under Q295646
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:
(https://support.microsoft.com/kb/246335/EN-US/ )How To Transfer Data from ADO Recordset to Excel with Automation
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 CopyYou 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.
How to AppendYou 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.
For additional information about this Jet-specific syntax, consult the Jet SQL help file (JETSQL40.chm), especially the topics on the SELECT INTO statement, the INSERT INTO statement, and the IN clause.
Article ID: 295646 - Last Review: August 23, 2006 - Revision: 2.4