XL: How to Retrieve Records into Specific Columns Using DAO

• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows

SUMMARY

In earlier versions of Microsoft Excel, you can use SQLBIND to specify where the results of a query are placed. However, if you use Data Access Objects (DAO), and you use the CopyFromRecordset method, data is always returned to a contiguous section of the worksheet. SQLBIND is not available with DAO; therefore, you must use alternate method of placing data in specified columns when you use DAO. This article demonstrates how to do this.

MORE INFORMATION

In order to specify into which columns the results of a data retrieval are inserted, it is necessary to loop through the recordset, specifying into which cells to place the data in each iteration of the loop.

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

The following subroutine will retrieve the Customers table in the Northwind database. The subroutine will then place the CustomerID field in Column A, the CompanyName field in Column C, and the ContactName field in Column E of Sheet1. The data is placed in the worksheet starting on the second row so that field headers can be placed in the first row.

NOTE: This subroutine is designed to run using the Northwind database that is included with both Microsoft Office 97 for Windows, Professional Edition and Microsoft Office version 7.0, Professional Edition.

In Microsoft Office 97 for Windows, Professional Edition, the default location for the Northwind database is:

   C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb

   C:\MSOffice\Access\Samples\Northwind.mdb

   Sub Test()
       Dim DB As Database, RS As Recordset
       Dim StartCell As Range

       ' Open the Northwind database.
       Set DB = OpenDatabase("c:\msoffice\access\samples\northwind.mdb")

       ' Open the Customers table as a recordset.
       Set RS = DB.OpenRecordset("Customers")

       ' Set StartCell as the first cell of the return range.
       Set StartCell = Worksheets(1).Range("A2")

       ' Move to the first row of the recordset.
       RS.MoveFirst

       For x = 0 To RS.RecordCount - 1
           With StartCell
               ' Place CustomerName field in column A.
               .Offset(x, 0).Value = RS.Fields("CustomerID").Value

               ' Place CompanyName field in column C.
               .Offset(x, 2).Value = RS.Fields("CompanyName").Value

               ' Place ContactName field in column E.
               .Offset(x, 4).Value = RS.Fields("ContactName").Value
           End With

           ' Move to the next record.
           RS.MoveNext
       Next

       ' Close the Recordset object.
       RS.Close

       ' Close the Database object.
       DB.Close
   End Sub

REFERENCES

For more information about data access objects, click the Index tab in Microsoft Excel Help and type:

   DAO