[Transfer from Microsoft Knowledge Base] How to use ADO to transfer data from ADO data source to Excel article ID: 295646 Last update: March 25, 2004 Version: 1.4 This page Summary More Information Reference
Summary Since Microsoft Excel is such a powerful data analysis tool, Visual Basic and VBA applications developers often put data in an Excel worksheet. This article describes how to use the ActiveX Data Object (ADO) to complete this programming task, where some SQL commands use Microsoft Jet-specific syntax.
This article describes programming solutions without introducing Excel's menu-Drive Data Import feature (Open) option} or Microsoft query in the file (Open) menu (Get external data from the Data "menu Usage of options).
For additional information about supplementary programming solutions using Excel Automation rather than SQL commands, please click on the article number below, view Microsoft Knowledge Base Article: 246335 HOWTO: TRANSFER DATA from Ado Recordset To Excel With Automation (Howto: using Automation From the ADO Recording Collection to Excel) More Information In the subsequent example, CNSRC represents an open ADO connection to connect to the Northwind sample Jet database using Jet 4.0 OLE DB Provider. How to copy you can copy data in any data source of JET to any data target using the SELECT INTO statement, creating a new table (in Excel). Do not use the dollar symbol syntax when using the worksheet name as the target, such as [Sheet1 $]. Target work can be existed, or there is no existence; however, the target worksheet must have not yet existed.
There are three writing methods for copying the entire Customers table from the Microsoft Access Northwind database to the new work table of Excel workfill. Each syntax requires a SQL statement and creates a column header in the first line of the target worksheet. • The following example uses Select Into Syntax: DIM STRSQL AS StringSTRSQL = "Select * INTO [Excel 8.0; Database =" & App.Path & _ "/book1.xls].[sheet1] from customers" cNSRC.EXECUTE STRSQL? The following example Use Select Into ... in syntax: strsql = "Select * INTO [Sheet1] in '' [Excel 8.0; Database =" & app.path & _ "/book1.xls] from customers" where the target database in parentheses There is a pair of empty single quotes in front of the information, used to include type parameters ("Excel 8.0" section), when you use this syntax, type parameters are enclosed in parentheses. • The following example uses the instead of the in clause: strsql = "select * INTO [Sheet1] in '" & app.path "/book1.xls' 'Excel 8.0;' from customer", the type parameter is now separate Summary after the target file path. How to Additional You can use the INSERT INTO ... IN statement to attach the data in the data source that JET readable to any data object. Target works and target worksheets must exist. Since you now reference an existing worksheet, you must use standard dollar symbol syntax, such as [Sheet1 $] when you use a worksheet name as a target. In addition, the column header must already exist; that is, the statement can only be used to attach to an existing table.
