ACC: How to Use Dynamic Data Exchange (DDE) to Fill a List Box
ID: Q94003
|
The information in this article applies to:
-
Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97
SUMMARY
Advanced: Requires expert coding, interoperability, and multiuser skills.
This article shows you how to create a sample user-defined Visual Basic for
Applications function that uses dynamic data exchange (DDE) to populate a
list box.
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.
NOTE: Visual Basic for Applications is called Access Basic in Microsoft
Access versions 1.x and 2.0. For more information about Access Basic,
please refer to the "Introduction to Programming" manual in Microsoft
Access version 1.x or the "Building Applications" manual in Microsoft
Access version 2.0
MORE INFORMATION
The following function uses DDE to retrieve a list of countries from
Microsoft Excel. The list will be used to populate a list box bound to the
Country field of the Suppliers table in the sample database Northwind.mdb
(or NWIND.MDB in Microsoft Access versions 1.x and 2.0).
- In a new worksheet in Microsoft Excel, enter the following data in the first column:
Cell Value
----------------
A1 Australia
A2 China
A3 Scotland
- Save the worksheet as COUNTRY.XLS.
- Open the sample database Northwind.mdb in Microsoft Access, and open a new module.
- Type the following lines in the Declarations section:
Option Explicit
Dim Countries(10) As String
Dim CountryData As String
- Type the following Sub procedure:
Sub DDEFillCountries ()
Dim EndString%, i%
Dim Chan
EndString% = 0
i% = 0
Chan = DDEInitiate("Excel", "COUNTRY.XLS")
CountryData$ = DDERequest(Chan, "R1C1:R10C1")
DDETerminate Chan
EndString% = InStr(1, CountryData, Chr(13))
Do Until Len(CountryData$) = 0
Countries(i%) = Left(CountryData, EndString% - 1)
CountryData$ = Right(CountryData, _
Len(CountryData) - EndString% - 1)
EndString% = InStr(1, CountryData, Chr(13))
i% = i% + 1
Loop
End Sub
- Type the following function:
Function DDEFillList (fld As Control, id, row, col, code)
Select Case code
Case 0
Call DDEFillCountries
DDEFillList = True
Case 1
DDEFillList = id
Case 3
DDEFillList = 10
Case 4
DDEFillList = 1
Case 5
DDEFillList = -1
Case 6
DDEFillList = Countries(row)
End Select
End Function
- Save the module as DDE Fill List Box.
- Create a new form based on the Suppliers table.
- Add a list box bound to the Country field and set the following
property:
RowSourceType: DDEFillList
- Verify that Microsoft Excel is running and the worksheet, COUNTRY.XLS,
is open. Switch to Form view. Note that the list box is populated
with the countries that you entered in Microsoft Excel.
NOTE: This article demonstrates a simple example and is not a complete
and robust function. If you intend to use this with a form, there are
several things that you may want to add to your code, including:
- Error checking for the DDE portion to handle cases where Microsoft Excel is not running or is busy.
- Refreshing the form if needed to show updated data.
REFERENCES
For more information about filling a list box, search the Help Index
for list boxes or ask the Microsoft Access 97 Office Assistant.
Additional query words:
Keywords : kbprg kbusage FmsCmbo
Version : WINDOWS:1.0,1.1,2.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto