Home

Populating an Excel Worksheet Using ADO

This sample project demonstrates how to use ADO 2.1 to populate an Excel 2000 worksheet.

Download a copy of the project.

Making the Excel Worksheet Project

  1. Create a DSN referring to the Northwind database. For information on creating DSNs, see these two MSDN Help topics:
  2. Open a new project in Visual Basic 6.0, and add references to the Microsoft ActiveX Data Objects 2.1 Library, and the Microsoft Excel 9.0 Object Library.
  3. Add a command button to the form.
  4. Copy the following code into the form.

    Option Explicit

    Private Sub Command1_Click()
    'This procedure opens an Excel worksheet and a Northwind table,
    'and fills the worksheet with data from the table.

        Dim xlApp As Excel.Application
        Dim xlSheet As Excel.Worksheet
        Dim cnNWind As ADODB.Connection
        Dim rsNWind As ADODB.Recordset
        Dim lngCounter As Long

        'Open an Excel workbook, and create an object
        'variable referring to a worksheet.
        Set xlApp = New Excel.Application
        xlApp.Workbooks.Add
        Set xlSheet = xlApp.ActiveSheet

        'Use a DSN and a Connection object to open
        'the Products worksheet.
        Set rsNWind = New Recordset
        Set cnNWind = New Connection
        cnNWind.Open "northwind"
        rsNWind.Open "products", cnNWind, adOpenStatic, adLockOptimistic, adCmdTable
        rsNWind.MoveNext

        'Copy the field names into the 1st row of
        'the Excel worksheet, beginning at cell A1.
        For lngCounter = 0 To rsNWind.Fields.Count - 1
           xlSheet.Range("a1").Offset(0, lngCounter).Value = rsNWind.Fields(lngCounter).Name
        Next lngCounter

        'Copy the recordset to the Excel worksheet,
        'beginning at cell A2.
        xlSheet.Range("a2").CopyFromRecordset rsNWind

        'Display Excel.
        xlApp.Visible = True

        'Cleanup
        rsNWind.Close
        Set rsNWind = Nothing
        cnNWind.Close
        Set cnNWind = Nothing
        Set xlSheet = Nothing
        Set xlApp = Nothing

        End Sub
  5. Save the project, then run it.
Last changed 10/4/00.