XL7: How to Return DAO Query Results Directly to a List Box

• Microsoft Excel for Windows 95, version 7.0
• Microsoft Office Professional for Windows 95, version 7.0
• Microsoft Access versions 7.0

SUMMARY

The following Microsoft Excel Visual Basic for Applications example returns the results of a Data Access Object (DAO) query on an external database directly to a list box.

MORE INFORMATION

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 Visual Basic for Applications macro uses DAO to perform a query on Northwind.mdb, a sample Microsoft Access database supplied with Microsoft Office Professional for Windows 95, version 7.0.

1. In Microsoft Excel, create a new dialog sheet. To do this, on the Insert menu, point to Macro, and then click Dialog.

2. To add a list box to the dialog sheet, on the Forms toolbar, click the List Box button, and then drag on the sheet until the control is the size and shape you want.

3. To insert a new Visual Basic module sheet, point to Macro on the Insert menu, and then click Module.

4. In the module, on the Tools menu, click References.

5. In the References dialog box, select the Microsoft DAO 3.0 Object Library check box.

6. On the new module sheet, enter the following macro:

         Sub DatatoListbox()
   
         Dim db As Database
         Dim rs As Recordset
         Dim varrecords As Variant
   
         'Open the Microsoft Access database.
         'Note: By default, Northwind.mdb is installed in the
         '\msoffice\access\samples directory. If you installed Microsoft
         'Access to a different location, you will have to modify the path
         'in the following line of code to point to the location of
         'Northwind.mdb.
   
         Set db = OpenDatabase("C:\msoffice\access\samples\northwind.mdb")
   
         'Retrieve the data from the database.
         Set rs = db.OpenRecordset("Select productname from Products
      where(products.unitprice>=10.00)")
   
         'Move the pointer to the last record of the retrieved data.
         rs.MoveLast
   
      'Count the number of records that are being returned and assign that
      'number to a variable.
         x = rs.RecordCount
   
         'Move the record pointer to the first record. This is required in
         'order to return the number of records specified by the RecordCount
         'Property. If this is not done, the record pointer will remain on
         'the last record and only the last record will be returned.
         rs.MoveFirst
   
         'Return the records to an array variable.
         varrecords = rs.GetRows(x)
   
         'Fill the list box.
         DialogSheets(1).ListBoxes(1).List = Array(varrecords)
   
         'Display the dialog sheet.
         DialogSheets(1).Show
         End Sub
   
   

REFERENCES

For additional information, click Microsoft Excel Help Topics on the Help Menu, click the Index Tab and then search on the following keywords:

   OpenDatabase
   OpenRecordset
   MoveLast
   MoveFirst
   GetRows
   Recordcount

"Developing Excel 95 Solutions with Visual Basic for Applications," by Eric Wells, Microsoft Press, 1995, Chapter 7, pages 397-532

"Microsoft Jet Database Engine Programmer's Guide," by Dan Haught and Jim Ferguson, Microsoft Press, 1995