DDESend Function

You can use the DDESend function to initiate a dynamic data exchange (Dynamic Data Exchange (DDE): An established protocol for exchanging data between Microsoft Windows-based programs.) (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 (string expression: An expression that evaluates to a sequence of contiguous characters. Elements of the expression can be: functions that return a string or a string Variant (VarType 8); a string literal, constant, variable, or Variant.) 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 (data item: An application-specific piece of data that can be transferred over a (Dynamic Data Exchange) DDE channel.) recognized by application. Check the other application's documentation for a list of possible items.
data A string or expression (expression: A combination of operators, field names, functions, literals, and constants that evaluates to a single value. Expressions can specify criteria (such as Order Amount>10000) or perform calculations on field values (such as Price*Quantity).) 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 (bound control: A control used on a form, report, or data access page to display or modify data from a table, query, or SQL statement. The control's ControlSource property stores the field name to which the control is bound.) 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 (Form view: A view that displays a form to show or accept data. Form view is the primary means of adding and modifying data in tables. You can also change the design of a form in this view.) and Print Preview (print preview: A view of a document as it will appear when you print it.). Because the ControlSource property is also read-only in Form view and Print Preview, changes to the control must be made in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).

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 (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain a Null value.).

 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: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.).

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.
 
 
Applies to:
Access 2007