Microsoft KB Archive/172058
HOWTO: Convert a Database Table into an Excel Spreadsheet
The information in this article applies to:
- Microsoft Visual Basic Professional and Enterprise Editions for Windows, versions 4.0, 5.0, 6.0
- Microsoft Excel 2000
- Microsoft Office 2000 Developer
- Microsoft Excel for Windows
This article contains a code example that demonstrates how to convert a database table into an Excel spreadsheet by using data access objects and OLE automation.
The program below demonstrates how easy it is to create a flexible and powerful program by integrating OLE automation with the data access objects in Visual Basic for Windows. Specifically, the program provides a method for converting a table that exists in a Microsoft Access database into a Microsoft Excel version 5.0 and above spreadsheet.
To do this, you'll need an Excel Spreadsheet object to receive the data from the table. This example uses OLE automation, so you'll need Excel version 5.0 or above. The program creates a Recordset object of type snapshot from the table you want to convert. The example uses the Titles table from the BIBLIO.MDB database: the sample database that comes with Visual Basic versions 4.0 and 5.0. After creating the snapshot, the program uses a user-defined CopyFromRecordset method to fill a variant array from the Recordset and uses this array to relay rows to Excel.
Steps to Create Example Program
- Create a new project in Visual Basic. Form1 is created by default.
- Add a CommandButton (Command1) and label (Label1) to Form1.
- Reference both the "Microsoft Excel Object Library" and the "Microsoft DAO Object Library."
- Place the following code in the General Declarations section of the form:
- Press the F5 key to run the program. When you click the CommandButton, the data in the Titles table will be imported into an Excel spreadsheet. The label will update you on its progress.
Additional query words: Excel CopyFromRecordSet
Keywords : kbinterop kbAutomation kbVBp kbVBp400 kbVBp500 kbVBp600 kbGrpDSO kbOffice2000 kbexcel2000
Issue type : kbhowto
Last Reviewed: October 18, 2000