ACC: How to Use Automation to Fill a List Box

MORE INFORMATION

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.