ACC: How to Use Automation to Fill a List Box

• 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 demonstrates how to use Automation to populate a list box from values contained in 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 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."

Keywords          : AutoGnrl kbprg kbusage PgmHowTo IntpOleA
Technology        : kbole
Version           : 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto