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

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:

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 = "SELECT * INTO [Excel 8.0;Database=" & App.Path & _ 
      "\book1.xls].[Sheet1] FROM Customers"
  cnSrc.Execute strSQL
  					

• The following example uses the SELECT INTO ... IN syntax:

  strSQL = "SELECT * INTO [Sheet1] IN '' [Excel 8.0;Database=" & App.Path & _
      "\book1.xls] FROM Customers"
  						

  where the bracketed destination database information is preceded by an empty pair of single quotes for the type argument (the "Excel 8.0" portion), which is included within the brackets when you use this syntax.
• The following example uses the alternate syntax for the IN clause:

  strSQL = "SELECT * INTO [Sheet1] IN '" & App.Path & _
      "\book1.xls' 'Excel 8.0;' FROM Customers"
  						

  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 = "INSERT INTO [Sheet1$] IN '' [Excel 8.0;Database=" & App.Path & _
      "\book1.xls] SELECT * FROM Customers"
  						

  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 = "INSERT INTO [Sheet1$] IN '" & App.Path & _
      "\book1.xls' 'Excel 8.0;' SELECT * FROM Customers"
  					

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.