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

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.