You can use the DDE 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, 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 Microsoft Office Excel 2007 worksheet.
DDE(application, topic, item)
The DDE function syntax has these arguments:
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
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 (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.). 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 (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 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 (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 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.
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 (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.).
||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.
||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.
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.
||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.