Microsoft KB Archive/113899

= How To Convert a Database Table into an Excel Spreadsheet =

Article ID: 113899

Article Last Modified on 2/23/2007

-

APPLIES TO


 * Microsoft Visual Basic 3.0 Professional Edition
 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 2000 Service Pack 1
 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q113899



SUMMARY
This article contains a code example that shows you how to convert a database table into an Excel spreadsheet by using data access objects and OLE automation.



MORE INFORMATION
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 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. The program creates a snapshot object 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 version 3.0. After creating the snapshot, the program loops through the fields collection of the snapshot to place the field names in the first row of the spreadsheet. Then it loops through each of the records to add them to the spreadsheet.

Steps to Create Example Program
 Create a new project in Visual Basic. Form1 is created by default. Add a command button (Command1) and label (Label1) to Form1.  Place the following code in the Form_Load event of Form1: Sub Form_Load Label1.AutoSize = True Label1.Caption = "Ready" Label1.Refresh End Sub   Add the following code to the Click event of Command1: Sub Command1_Click Dim i As Integer          ' Loop counters Dim j As Integer Dim rCount As Long        ' Record count Dim xl As object          ' OLE automation object Dim db As database        ' Database object Dim Sn As Snapshot        ' Snapshot to hold records

Screen.MousePointer = 11  ' Change mousepointer Label1.Caption = "Creating Excel Object" Label1.Refresh Set xl = CreateObject("Excel.Sheet.5") ' Open the database: Label1.Caption = "Opening the database" Label1.Refresh Set db = OpenDatabase("C:\VB\BIBLIO.MDB") ' Set up Field names as Column names: Label1.Caption = "Creating SnapShot" Label1.Refresh Set Sn = db.CreateSnapshot("Titles") If Sn.RecordCount > 0 Then ' Place the fields across the top of the spreadsheet: Label1.Caption = "Adding field names to Spreadsheet" Label1.Refresh For i = 0 To Sn.Fields.Count - 1 xl.cells(1, i + 1).value = Sn(i).Name Next ' Update record count, and return to the first record: Sn.MoveLast Sn.MoveFirst rCount = Sn.RecordCount ' Loop through each record: i = 0 Do While Not Sn.EOF Label1.Caption = "Record:" & Str(i + 1) & " of" & _ Str(rCount) Label1.Refresh For j = 0 To Sn.Fields.Count - 1 ' Add each field to the spreadsheet: If Sn(j).Type < 11 Then xl.cells(i + 2, j + 1).value = Sn(j) Else ' Separate out Memo and LongBinary fields. ' They aren't guaranteed to be text. xl.cells(i + 2, j + 1).value = "Memo or Binary Data" End If               Next j                Sn.MoveNext i = i + 1 Loop ' Save the spreadsheet: Label1.Caption = "Saving Spreadsheet" Label1.Refresh xl.SaveAs "C:\TMP\TITLES.XLS" ' Quit the excel object - removes Excel from memory! Label1.Caption = "Quitting Excel" Label1.Refresh xl.Application.Quit Else ' No records. End If        ' Clean up: Label1.Caption = "Cleaning up" Label1.Refresh Set xl = Nothing        ' Remove object variable. Set Sn = Nothing        ' Remove snapshot object. Set db = Nothing        ' Remove database object. Screen.MousePointer = 0 ' Restore mouse pointer. Label1.Caption = "Ready" Label1.Refresh End Sub  Press the F5 key to run the program. When you click the command button, the data in the Titles table will be imported into an Excel spreadsheet. The label will update you on its progress.

Keywords: kbhowto kbprogramming KB113899

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.