Microsoft KB Archive/210145

= ACC2000: How to Use Automation to Fill a List Box =

Article ID: 210145

Article Last Modified on 5/9/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition
 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210145



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



SUMMARY
This article shows you how to use Automation to populate a list box with values from a Microsoft Excel worksheet.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access.



MORE INFORMATION
CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

CAUTION: If you follow the steps in this example, you modify the sample Access project NorthwindCS.adp. You may want to back up the NorthwindCS.adp file and follow these steps on a copy of the project.

To use Automation to retrieve a list of countries from a Microsoft Excel worksheet, follow these steps. You can then use the list to populate a list box bound to the Country field of the Suppliers table in the sample database Northwind.mdb.  Start Microsoft Excel, and then create a new worksheet with the following data:

A1: Australia

A2: China

A3: Scotland

 Save the worksheet as C:\My Documents\Country.xls.

NOTE: If you use a different name or location for this file, be sure to change the sample code in the module to reflect this change. Start Microsoft Access, open the sample database Northwind.mdb or the sample project NorthwindCS.adp, and then create a new module.  Type the following lines in the Declarations section: Option Explicit Dim Countries(3) As String   Type the following subroutine in the module: Sub OLEFillCountries Dim i%  Dim XL As Object Dim WrkBook As Object Set XL = CreateObject("Excel.Application") Set WrkBook = XL.Workbooks.Open("C:\My Documents\Country.xls") For i% = 0 To 2 Countries(i%) = WrkBook.Sheets(1).Cells(i% + 1, 1).Value Next i%  XL.Quit Set WrkBook = Nothing Set XL = Nothing End Sub   Type the following function in the module: Function OLEFillList(fld As Control, id, row, col, code) Select Case code Case 0                    ' Initialize. Call OLEFillCountries OLEFillList = True Case 1                    ' Open. OLEFillList = id     Case 3                     ' Get number of rows. OLEFillList = 3 Case 4                    ' Get number of columns. OLEFillList = 1 Case 5                    ' Force default width. OLEFillList = -1 Case 6 OLEFillList = Countries(row) End Select End Function </li> Save the module as OLE Fill List Box.</li> Create a new form based on the Suppliers table.</li> Create a list box with the following properties:

ControlSource: Country

RowSourceType: OLEFillList

</li> On the View menu, click Form View.</li></ol>

Note that the list box contains the values entered in the spreadsheet.

<div class="references_section">