Microsoft KB Archive/142996

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

Article ID: 142996

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition
 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition

-



This article was previously published under Q142996





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

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. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.



MORE INFORMATION
The following function uses Automation to retrieve a list of countries from a Microsoft Excel worksheet. The list is then used to populate a list box bound to the Country field of the Suppliers table in the sample database Northwind.mdb.   Start Microsoft Excel and create a new worksheet with the following data:       Cell   Value A1    Australia A2    China A3    Scotland  Save the worksheet as C:\My Documents\Country.xls.

NOTE: If you change the name or location of this file, be sure to change the sample code to reflect this change. Open the sample database Northwind.mdb and create a new module. Type the following lines in the Declarations section:

Option Explicit

Dim Countries(3) As String

  Type the following subroutine: 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: 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" (without the quotation marks).</li> Create a new form based on the Suppliers table.</li>  Create a list box with the following properties: <pre class="fixed_text">      Object: List Box --      ControlSource: Country RowSourceType: OLEFillList </li> Open the form in Form view. Note that the list box contains the values entered in the spreadsheet.</li></ol>

<div class="references_section">