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.
Use the DDE function in the ControlSource property setting of a text box, option group, check box, or combo box on a form.
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 Microsoft Excel spreadsheet.
DDE(application, topic, item)
The DDE function syntax has the following arguments.
| Argument | 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. For example, to initiate a DDE conversation with Microsoft Excel, type “Excel” for the application argument. |
| topic | A string expression that is the name of a topic recognized by application. Topic 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. |
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 Microsoft 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 a Microsoft 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 cannot call the DDE function from Visual Basic.
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 cannot 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.
The maximum number of DDE conversations that can be open simultaneously is determined by Microsoft Windows and your computer’s memory and resources. If the conversation can’t be initiated because application isn’t running or doesn’t recognize topic, or if the maximum number of conversations has already been reached, DDE returns a Null.
Note The other application may be configured to ignore your request for a DDE conversation. If so, DDE returns a Null. Similarly, you can set Microsoft Access to ignore requests from other applications: click Options on the Tools menu, and on the Advanced tab of the Options dialog box, under DDE Operations, select Ignore DDE Requests.
Note If you need to manipulate another application’s objects from Microsoft Access, you may want to consider using OLE Automation.
The following table illustrates how the DDE function behaves when you use it with each of the controls.
| Control | 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 a Microsoft Excel worksheet that contains multiple cells, DDE returns the first entry. You could use the DDE 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 states or countries that you maintain in a Microsoft 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 DDE returns 2, the second button will be selected. If DDE 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 a Microsoft Excel worksheet that contains multiple cells, DDE returns the first entry. | |
| Check box | If the DDE function returns 0, the check box will be cleared. If DDE 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 a Microsoft Excel worksheet that contains multiple cells, the check box will be unavailable. |
DDEExecute Statement; DDEInitiate Function; DDEPoke Statement; DDERequest Function; DDESend Function; DDETerminate Statement; DDETerminateAll Statement; Enabled, Locked Properties.
This example establishes a DDE link with Microsoft Excel, places some values into cells in the first row of a new worksheet, and charts the values. First, the DDEInitiate function opens a channel to begin the DDE conversation. Then, the DDEExecute statement sends Microsoft Excel the command to open a new worksheet, and the DDERequest function asks Microsoft Excel for the name of the newly created worksheet. A new channel is opened, and the DDEPoke statement sends to Microsoft Excel the data to be charted. Finally, the DDETerminate statement terminates the DDE link with Microsoft Excel, and the DDETerminateAll statement terminates all active DDE links.
On Error Resume Next ' Set up error handler.Chan, SheetName, I, TopicList ' Declare variables.= DDEInitiate("Excel", "System") ' Establish spreadsheet
' link.Err Then ' If error occurs,
' spreadsheet
Err = 0 ' isn't running. Reset
' error and start
I = Shell("Excel", 1) ' spreadsheet.
If Err Then Exit Sub ' If another error, exit.
Chan = DDEInitiate("Excel", "System") ' Establish spreadsheet
' link.IfChan, "[New(1)]" ' Create new worksheet.= DDERequest(Chan, "Selection") ' Get topic list, worksheet
' name.= Left(TopicList, InStr(1, TopicList, "!") - 1)Chan ' Terminate DDE link.= DDEInitiate("Excel", SheetName) ' Establish link with new
' worksheet.I = 1 To 10 ' Put some values into
DDEPoke Chan, "R1C" & I, I ' first row.IChan, "[Select(""R1C1:R1C10"")][New(2,2)]" ' Make chart. ' Terminate all links.