ACC2000: How to Use Automation to Fill a List Box (210145)



The information in this article applies to:

  • Microsoft Excel 2000
  • Microsoft Access 2000

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.
  1. Start Microsoft Excel, and then create a new worksheet with the following data:

    A1: Australia
    A2: China
    A3: Scotland

  2. 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.
  3. Start Microsoft Access, open the sample database Northwind.mdb or the sample project NorthwindCS.adp, and then create a new module.
  4. Type the following lines in the Declarations section:
    Option Explicit
    Dim Countries(3) As String
    					
  5. 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
    					
  6. 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
    					
  7. Save the module as OLE Fill List Box.
  8. Create a new form based on the Suppliers table.
  9. Create a list box with the following properties:

    ControlSource: Country
    RowSourceType: OLEFillList

  10. On the View menu, click Form View.
Note that the list box contains the values entered in the spreadsheet.

REFERENCES

For more information about how to use the sample code in this article, click the article number below to view the article in the Microsoft Knowledge Base:

212536 OFF2000: How to Run Sample Code from Knowledge Base Articles

For more information about using functions to fill list boxes, click Microsoft Access Help on the Help menu, type in a form, create a list box or combo box that gets its rows from a function in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Modification Type:MajorLast Reviewed:5/9/2003
Keywords:kbhowto kbinfo kbProgramming kbusage KB210145