You can use the DDESend function to initiate a dynamic data exchange (DDE) conversation with another application and send an item of information to that application from a control on a form or report.
For example, you can use the DDESend function in the ControlSource property of a text box to send the data displayed in that text box to a specified cell in a Microsoft Excel spreadsheet.
Syntax
DDESend(application, topic, item, data)
The DDESend function 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, such as Microsoft Excel. 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. 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. |
| data | A string or expression containing the data to send to application. |
Remarks
The DDESend function initiates a DDE conversation with application and topic, and identifies item as the data item that will receive data. For example, if application is Microsoft Excel, topic might be "Sheet1", and item might be a row-and-column identifier, such as "R1C1", or the name of a range of cells.
The data argument specifies the information you want to send. It can be a literal string, such as "Report prepared by John". Or it can be an expression that includes the result of a function that creates a string, such as "Prepared on " & Date(). If item refers to more than one piece of information, such as a named range in a Microsoft Excel worksheet that contains multiple cells, the DDESend function sends data to the first entry.
In the following example, the DDESend function sends the string "Some text" to 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:
=DDESend("Excel", "Sheet1", "R1C1", "Some text")Suppose you want to send data from a bound control on a Microsoft Access form to a cell on a Microsoft Excel spreadsheet. The ControlSource property of the bound control already contains a field name or expression. You can create another text box or combo box, and set its ControlSource property to an expression including the DDESend function, where data is the name of the bound control. For example, if you have a bound text box called Last Name, you can create another text box and set its ControlSource property to the following:
=DDESend("Excel", "Sheet1", "R1C1", [LastName])This intermediary control must be either a text box or a combo box. You can't use the name of a bound control as the data argument for a check box or option group.
You can use the DDESend function only in the ControlSource property setting of a text box, option group, check box, or combo box on a form. You cannot call the DDESend function from Visual Basic.
When you use the DDESend function, the control becomes read-only in Form view and Print Preview. Because the ControlSource property is also 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 application isn't running or doesn't recognize topic or if the maximum number of conversations has already been reached, the DDESend function returns a Null.
Note The other application may be configured to ignore your request for a DDE conversation. If so, the DDESend function 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.
Tip If you need to manipulate another application's objects from Microsoft Access, you may want to consider using Automation.
The following table illustrates how the DDESend function behaves when you use it with each of the controls.
| Control | Remarks |
|---|---|
| Text box or Combo box | Because the text box or combo box appears blank in Form view and Print Preview, you may want to set its Visible property to False.
The data argument can refer to another control. The following example shows how you can send the contents of a control called LastName to a Microsoft Excel worksheet: |
| Option group | None of the option buttons in the option group is selected in Form view and Print Preview. You may want to make the option group (and its buttons) invisible by setting its Visible property to False.
The data argument must contain numeric data, such as |
| Check box | The check box is shaded in Form view and Print Preview. You may want to make it invisible by setting its Visible property to False.
The data argument must contain numeric data, such as |