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

ID: Q149254

The information in this article applies to:

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 professionals 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
"Microsoft Excel Visual Basic Programmer's Guide," pages 235-253

"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

Additional query words: 7.00 

Keywords          :  
Version           : 7.00
Platform          : WINDOWS

Last Reviewed: May 18, 1999