ID: Q113899
The information in this article applies to:
- Professional Edition of Microsoft Visual Basic for Windows,
version 3.0
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.
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.
1. Create a new project in Visual Basic. Form1 is created by default.
2. Add a command button (Command1) and label (Label1) to Form1.
3. Place the following code in the Form_Load event of Form1:
Sub Form_Load ()
Label1.AutoSize = True
Label1.Caption = "Ready"
Label1.Refresh
End Sub
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
button, the data in the Titles table will be imported into an Excel
spreadsheet. The label will update you on its progress.
Keywords : kbprg IAPOLE APrgDataAcc vbwin
Version : WINDOWS:3.00
Platform : WINDOWS
Issue type : kbhowtoLast Reviewed: September 30, 1997