How To Convert a Database Table into an Excel Spreadsheet

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

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
   						

4. 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
   						

5. 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.