You can use the DDE function to initiate a dynamic data exchange (DDE) conversation with another application, request an item of information from that application, and display that information in a control on a form or report.
For example, you can use the DDE function in the ControlSource property of a text box to display data from a specified cell in a Excel worksheet.
Syntax
DDE( application, topic, item )
The DDE function syntax has these arguments:
|
A rgument |
Description |
|---|---|
|
application |
A string expression identifying an application that can participate in a DDE conversation. Usually, application is the name of an .exe file (without the .exe extension) for a Microsoft Windows–based application, such as Excel. For example, to initiate a DDE conversation with Excel, type "Excel" for the application argument. |
|
topic |
A string expression that is the name of a topic recognized by application. The topic argument is often a document or data file. Check the other application's documentation for a list of possible topics. |
|
item |
A string expression that is the name of a data item recognized by application. Check the other application's documentation for a list of possible items. |
Remarks
The DDE function attempts to initiate a DDE conversation with the specified application and topic, and requests the data in item. If successful, the DDE function returns a string that contains the requested information.
If you are requesting data from Excel, item might be a row-and-column identifier, such as "R1C1" or the name of a range of cells. In the following example, the DDE function requests information from the cell at row 1, column 1 in an Excel worksheet. You can enter this expression for a text box control in the ControlSource property box on the control's property sheet:
=DDE("Excel", "Sheet1", "R1C1")
You can use the DDE function only in the ControlSource property of a text box, option group, check box, or combo box. You can't call the DDE function from aVisual Basic for Applications (VBA) module.
When you use the DDE function, the control becomes read-only in Form view and Print Preview. For example, if you use the DDE function in a text box, the text in the text box can't be edited. You must edit the text in the other application. Because the ControlSource property is read-only in Form view and Print Preview, changes to the control must be made in Design view.
Microsoft Windows and your computer's memory and resources determine the maximum number of DDE conversations that can be open simultaneously. If the conversation can't be initiated because the other application isn't running or doesn't recognize the specified topic, or if the maximum number of conversations has already been reached, the DDE function returns a Null.
Note: The other application may be configured to ignore your request for a DDE conversation. If so, the DDE function returns a Null. Similarly, you can set Access to ignore requests from other applications: click Access Options on the File menu, then click Advanced in the Application Settings dialog box. Under DDE operations, select Ignore DDE requests.
tip
If you need to manipulate another application's objects from Access, you may want to consider using Automation.
The following table illustrates how the DDE function behaves when you use it with each of the controls.
|
Con trol |
Remarks |
|---|---|
|
Text box |
The item argument can refer to text or numbers. If item refers to more than one piece of information, such as a named range in an Excel worksheet that contains multiple cells, the DDE function returns the first entry. You could use this function with a text box to display the data contained in a cell on the worksheet. |
|
Combo box |
The DDE function fills the combo box with the information referred to by item. You can't enter data in the text portion of the box. You might use the DDE function with a combo box to display a list of countries/regions that you maintain in an Excel worksheet. |
|
Option group |
The OptionValue property of each option button in an option group is set to a number. Usually, the first button value is 1, the second is 2, and so on. The number returned by the DDE function determines which option button will be selected. For example, if the DDE function returns 2, the second button will be selected. If this function returns a value that doesn't match any of the OptionValue property settings, none of the buttons will be selected. If item refers to more than one piece of information, such as a named range in an Excel worksheet that contains multiple cells, the DDE function returns the first entry. |
|
Check box |
If the DDE function returns 0, the check box will be cleared. If this function returns a nonzero number, such as 1 or –1, the box will be selected. If item refers to text or to more than one piece of information, such as a named range in an Excel worksheet that contains multiple cells, the check box will be unavailable. |
