ACC: How to Use Automation to Fill a List Box

ID: Q142996

The information in this article applies to:

• Microsoft Access versions 7.0, 97
• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, version 7.0

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.

  1. Start Microsoft Excel and create a new worksheet with the following
     data:

       Cell   Value
       ----------------
       A1     Australia
       A2     China
       A3     Scotland

  2. 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.

  3. Open the sample database Northwind.mdb and 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:

       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:

       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" (without the quotation marks).

  8. Create a new form based on the Suppliers table.

  9. Create a list box with the following properties:

       Object: List Box
       --------------------------
       ControlSource: Country
       RowSourceType: OLEFillList

 10. Open the form in Form view. Note that the list box contains the
     values entered in the spreadsheet.

REFERENCES

For more information about using functions to fill list boxes, search the Help Index for "List Boxes, Create a list box or combo box that gets its rows from a Visual Basic function," or ask the Microsoft Access 97 Office Assistant.

For more information about Automation, search the help Index for "OLE Automation."

Additional query words:

Keywords          : kbprg kbusage IntpOlea 
Version           : 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: January 12, 1999