XL7: How to Return DAO Query Results Directly to a List Box
ID: Q149254
|
The information in this article applies to:
-
Microsoft Excel for Windows 95, version 7.0
-
Microsoft Office for Windows 95, Professional edition
-
Microsoft Access 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 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.
- In Microsoft Excel, create a new dialog sheet. To do this, on the Insert
menu, point to Macro, and then click Dialog.
- 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.
- To insert a new Visual Basic module sheet, point to Macro on the Insert
menu, and then click Module.
- In the module, on the Tools menu, click References.
- In the References dialog box, select the Microsoft DAO 3.0 Object
Library check box.
- 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:
Keywords :
Version : WINDOWS:7.0
Platform : WINDOWS
Issue type :
|