ACC: How to Use Dynamic Data Exchange (DDE) to Fill a List Box

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article contains an example of a Visual Basic for Applications list 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 function below 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).

1. In a new worksheet in Microsoft Excel, enter the following in the first column:

          Cell   Value
          ----------------
          A1     Australia
          A2     China
          A3     Scotland
   
   

2. Save the worksheet as COUNTRY.XLS.

3. Open Northwind.mdb in Microsoft Access and open a new module.

4. Type the following lines in the Declarations section:

   Option Explicit Dim Countries(10) As String Dim CountryData As String

5. Type the following subroutine:

         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
   
   

6. Create 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
   
   

7. Save the module as DDE Fill List Box.

8. Create a new form based on the Suppliers table.

9. Add a list box bound to the Country field and set the following property:

   RowSourceType: DDEFillList

10. 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.

• Error checking for the DDE portion to handle cases where Microsoft Excel is not running or is busy.
• The form may need to be refreshed 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.

Keywords          : IntpDde kbprg kbusage FmsCmbo
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto