Microsoft KB Archive/210145

Knowledge Base

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.

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.

Keywords: kbhowto kbinfo kbprogramming kbusage KB210145

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.