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.
- Start Microsoft Excel, and then create a new worksheet with the following data:
A1: Australia
A2: China
A3: Scotland
- 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. - Start Microsoft Access, open the sample database Northwind.mdb or the sample project NorthwindCS.adp, and then create a new module.
- Type the following lines in the Declarations section:
Option Explicit
Dim Countries(3) As String
- 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
- 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
- Save the module as OLE Fill List Box.
- Create a new form based on the Suppliers table.
- Create a list box with the following properties:
ControlSource: Country
RowSourceType: OLEFillList
- On the View menu, click Form View.
Note that the list box contains the values entered in the spreadsheet.