Moving Data from a Database into Microsoft Excel 97 Using Fields

Charlie Kindschi
Microsoft Corporation

February 20, 1998

Click to copy the daofields sample discussed in this article.

The objects in the Data Access Objects (DAO) object hierarchy are like stepping stones to get at data programmatically. For example, to return the value of particular fields in your database tables, use the Fields collection of the Recordset object.

This article contains a code example that uses the Fields collection to bring data from a database into a Microsoft® Excel spreadsheet. It includes some pointers for adapting the code example for use with your own data source and concludes with tips for improving the speed of the code. Along the way, you will learn how to create a custom toolbar button through the Excel user interface and assign a macro to run when you click the button.

This article uses data from the Northwind sample database (Northwind.mdb), which contains the sales data for a fictitious import/export company called Northwind Traders. The Northwind database is included with Office 97 as well as other development products, such as Visual Basic®. The default location of the Northwind database is C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb.

You can bring selected data from a Microsoft Access or Microsoft Jet database into an Excel spreadsheet to analyze in Excel. For example, suppose you need to analyze data about employees at the Northwind Traders company. The following code example gets data from the Employees table of the Northwind database and places it in an Excel worksheet.

Note   The completed project is provided as an Excel template that you can download. If you prefer to go immediately to the completed project, see the section "Using the Template" for instructions on downloading and using the Excel template.

Public Sub GetData()
    Dim dbs As DAO.Database
    Dim rstEmployees As Recordset
    Dim fldEmployees As Field
    Dim intCount As Integer
    Dim empLname As String
    Dim empFname As String
    Dim empTitle As String

    intCount = 1
    
    Set dbs = DBEngine(0).OpenDatabase("C:\Program Files" _
        & "\Microsoft Office\Office\Samples\Northwind.mdb")

    Set rstEmployees = dbs.OpenRecordset("Employees", dbOpenTable)

    ' Set header values for sheet 1.
    With Worksheets("Sheet1").Rows(9)
        .Font.Bold = True
        .Cells(1, 5).Value = "Last Name"
        .Cells(1, 6).Value = "First Name"
        .Cells(1, 7).Value = "Job Title"
    End With

    ' Loop through all records, sending selected fields to AddToSheet
    ' function one row at a time.
    Do Until rstEmployees.EOF
        Set fldEmployees = rstEmployees.Fields(1)  ' "LastName" field.
        empLname = fldEmployees.Value
        Set fldEmployees = rstEmployees.Fields(2)  ' "FirstName" field.
        empFname = fldEmployees.Value
        Set fldEmployees = rstEmployees.Fields(3)  ' "Title" field.
        empTitle = fldEmployees.Value
        intCount = intCount + 1
        Call AddToSheet(intCount, empLname, empFname, empTitle)
       
        rstEmployees.MoveNext
    Loop
    
    With Worksheets("Sheet1").Columns("E:G")
        .AutoFit

    End With
End Sub 


Public Function AddToSheet(intCount As Integer, empLname As String, _
    empFname As String, empTitle As String)

' This function adds recorset data to sheet one row at a time.
' Values are passed from GetData procedure.
' The intCount variable is incremented one each pass so a new
' row is used for each record.
    
    With Worksheets("Sheet1").Rows(intCount)
        .Cells(10, 5).Value = empLname
        .Cells(10, 6).Value = empFname
        .Cells(10, 7).Value = empTitle
    End With
    
End Function

Note   You can refer to a Field object by using its name, as shown in the following line of code:

Set fldEmployees = rstEmployees.Fields("LastName")

You can also refer to a Field object by using its ordinal number in a collection. Members of most collections are numbered beginning with zero. The following line of code refers to the second field in the Fields collection of the Recordset object:

Set fldEmployees = rstEmployees.Fields(1)

The code example in this section refers to fields by their ordinal number.

Testing This Code Example

To test this code example, follow these steps:

  1. Open Excel and switch to the Visual Basic Editor by pressing ALT+F11 or by clicking the Tools menu, pointing to Macro, and clicking Visual Basic Editor.

  2. Insert a new code module by clicking Module on the Insert menu and paste the GetData procedure and the AddToSheet function into it.

  3. Make sure the path used in the OpenDatabase method of the DBEngine object reflects the location of the Northwind database on your computer. To do so, check the line of code that reads: 
    Set dbs = DBEngine(0).OpenDatabase("C:\Program Files" _
    & "\Microsoft Office\Office\Samples\Northwind.mdb")

    If the location differs, move your copy of Nothwind.mdb or change the path in the OpenDatabase method.

  4. Set a reference to the Microsoft DAO 3.5 Object Library by selecting it in the References dialog box, accessed from the Tools menu in the Visual Basic Editor.

  5. Close the code module and accept the default name Module1 when you are prompted to save.

  6. Create a custom macro command button on an existing toolbar, as described in the following section "Creating a Custom Macro Button."

  7. Click the new command button to run the code examples.

Creating a Custom Macro Button

You can create your own toolbar button in Excel and use it to run your macro.

To create a custom macro button that runs the GetData procedure, follow these steps:

  1. In Excel, right-click any existing toolbar, then click Customize on the shortcut menu.

  2. Click the Commands tab, then click Macros in the Categories box. Drag Custom Button from the Commands box to any existing toolbar.

  3. Right-click the new command button and click Assign Macro on the shortcut menu. In the Macro Name box, click GetData, then click OK.

  4. If you want to change the button image or its name, do so by right-clicking the button and using the appropriate commands on the shortcut menu.

  5. In the Customize dialog box, click Close.

Using This Code with Your Own Data

To try this example using your own data source, follow these steps:

  1. Change the path in the OpenDatabase method of the DBEngine object to reflect the path to your own Access or Microsoft Jet database. Your code should look like this: 
    Set dbs = DBEngine(0).OpenDatabase("C:\Your Directory" _
    & "\YourDatabase.mdb")

    In this code, Your Directory is the path to the database file you want to open and YourDatabase.mdb is the name of an Access or a Microsoft Jet database that contains at least one table with at least four fields in each record.

  2. If the table containing the data you want to access is not named Employees in your database, change the first argument of the OpenRecordset method of the Database object to reflect the name of your table. The table must contain a least four fields per record. 
    Set rstEmployees = dbs.OpenRecordset("YourTable", dbOpenTable)

    In this code, YourTable is a valid table name in your database.

    The code is now operational; it will bring data from your data source into the Excel spreadsheet. However, if you want to bring in data from particular fields and label the headers appropriately, you can complete the following additional steps.

  3. In the original code, the fields of data were brought in using ordinal numbers. They are the second, third, and fourth fields of the Employees table in the Northwind database. If the fields whose data you want to access are not the second, third, and fourth fields in your table, you need to change the code. To bring in your chosen fields, get their names by looking at the table in Design view, then change the GetData procedure code as follows: 
    Do Until rstEmployees.EOF
    Set fldEmployees = rstEmployees.Fields("YourFirstField") 
    empLname = fldEmployees.Value
    Set fldEmployees = rstEmployees.Fields("YourSecondField") 
    empFname = fldEmployees.Value
    Set fldEmployees = rstEmployees.Fields("YourThirdField")  
    empTitle = fldEmployees.Value
    intCount = intCount + 1
    Call AddToSheet(intCount, empLname, empFname, empTitle)
       
    rstEmployees.MoveNext
Loop

    This code refers to fields by name. Alternatively, you could refer to your fields by ordinal number.

  4. As written, the code creates a header that reads Last Name, First Name, Title. If your table data requires different headers, change the code as follows. 
    ' Set header values for sheet 1.
    With Worksheets("Sheet1").Rows(9)
        .Font.Bold = True
        .Cells(1, 5).Value = "Your First Field Header"
        .Cells(1, 6).Value = "Your Second Field Header "
        .Cells(1, 7).Value = "Your Third Field Header "
    End With
  5. Switch to the Excel worksheet and click the custom button you created earlier.

Improving the Code

The code example in this article was written for clarity, not for speed. The references to fields were made explicitly without taking advantage of the implicit reference capability of Visual Basic for Applications (VBA) collections. The Fields collection is the default collection of the Recordset object, so you need not explicitly specify its name in code. Similarly, because the Value property is the default property of the Field object, you need not refer to it explicitly. The following two lines of code are equivalent: 

strLastName = rstEmployees.Fields("LastName").Value
strLastName = rstEmployees!LastName

Each time a ! or a . (dot) operator is used in code, things slow down slightly as VBA iterates through the referenced collection. So if you use implicit references, you can reduce the number of ! and . (dot) operators and improve the speed of your code.

The FastData procedure performs the same action as the AddToSheet function shown earlier. However, because the FastData procedure takes advantage of implicit references, it is more efficient.

Public Sub FastData()
    Dim dbs As DAO.Database
    Dim rstEmployees As Recordset
    Dim strSQL As String
    Dim intCount As Integer
    
    Set dbs = DBEngine(0).OpenDatabase("C:\Program Files" _
        & "\Microsoft Office\Office\Samples\Northwind.mdb")
    Set rstEmployees = dbs.OpenRecordset("SELECT Employees.LastName, " _
        & "Employees.FirstName, Employees.Title FROM Employees;")
    
    With rstEmployees
        Do While Not .EOF
            ActiveSheet.Cells(intCount + 21, 5) = !LastName
            ActiveSheet.Cells(intCount + 21, 6) = !FirstName
            ActiveSheet.Cells(intCount + 21, 7) = !Title
            intCount = intCount + 1
            .MoveNext
        Loop
    End With
End Sub

Using the Template

The completed project is provided as an Excel template that you can download. To use it, follow these steps:

  1. Download, extract, and copy Fields.xlt to a templates folder. By default, Excel templates are located in C:\Program Files\Microsoft Office\Templates.

  2. Open Excel.

  3. On the File menu, click New, then double-click Fields.xlt on the General tab of the New dialog box.

  4. In the sample code, make sure the path used in the OpenDatabase method of the DBEngine object reflects the location of the Northwind database on your computer. If it differs, either move your copy of Nothwind.mdb or change the path in the OpenDatabase method.

  5. To bring data from the Employees table in the Northwind database into the Excel worksheet, click the custom toolbar button with the standing man on it.

  6. To bring in the same data with more efficient code, click the custom toolbar button with the running man on it.