Ddesend function

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 Office Excel 2007 spreadsheet.

Syntax

DDESend ( application, topic, item, data )

The DDESend function syntax has these 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 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.
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 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 an 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 an 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 Office Access 2007 form to a cell on an 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 LastName, 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 a Visual Basic for Applications (VBA) module.

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 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 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 an Excel worksheet: =DDESend("Excel", "Sheet1", "R1C1", [LastName])
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 "2". If the data argument isn't numeric, the DDESend function doesn't send the information and item isn't changed.
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 "2". If the data argument isn't numeric, the DDESend function doesn't send the information and item isn't changed.