DOCUMENT:Q295646 23-AUG-2001 [vbwin] TITLE :HOWTO: Transfer Data from ADO Data Source to Excel with ADO PRODUCT :Microsoft Visual Basic for Windows PROD/VER::2.0,2.1,2.5,2.6,2000,5.0,6.0 OPER/SYS: KEYWORDS:kbDatabase kbJET kbMDAC kbGrpDSVBDB kbGrpDSMDAC kbDSupport kbMDACNoSweep kbATM ====================================================================== ------------------------------------------------------------------------------- The information in this article applies to: - Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0 - Microsoft Visual Basic Professional Edition for Windows, versions 5.0, 6.0 - Microsoft Data Access Components versions 2.0, 2.1, 2.5 - Microsoft Excel 97 for Windows - Microsoft Excel 2000 ------------------------------------------------------------------------------- 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: Q246335 HOWTO: 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 = "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 SELECT 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" Starting with Microsoft Data Access Components (MDAC) version 2.6, MDAC no longer contains the following Jet components: - Microsoft Jet - Microsoft Jet OLE DB Provider - ODBC Desktop Database Drivers For additional information, click the article number below to view the article in the Microsoft Knowledge Base: Q239114 ACC2000: Updated Version of Microsoft Jet 4.0 Available in Download Center The "MDAC 2.5 Stack and Windows File Protection" white paper contains a full list of the components that are shipped with MDAC 2.5, along with a discussion of Windows File Protection. Refer to this white paper for more information about the Jet dynamic-link libraries (DLLs) that are included in MDAC 2.5, which are no longer a part of MDAC 2.6. For more information about MDAC 2.5 and Windows File Protection, see the following Microsoft Web site: http://www.microsoft.com/data/mdacwfp.htm REFERENCES ========== 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. Additional query words: ====================================================================== Keywords : kbDatabase kbJET kbMDAC kbGrpDSVBDB kbGrpDSMDAC kbDSupport kbMDACNoSweep kbATM Technology : kbVBSearch kbExcelSearch kbAudDeveloper kbZNotKeyword6 kbExcel2000 kbZNotKeyword2 kbVB500Search kbVB600Search kbVB500 kbVB600 kbExcel2000Search kbExcel97Search kbMDACSearch kbZNotKeyword3 kbMDAC200 kbMDAC210 kbMDAC250 Version : :2.0,2.1,2.5,2.6,2000,5.0,6.0 Issue type : kbhowto ============================================================================= THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Copyright Microsoft Corporation 2001.