DDE Function

Description

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.

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.

Syntax

DDE(application, topic, item)

The DDE 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.

Remarks 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 can't 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 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.

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, the DDE function returns a Null.

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 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 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, 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.
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 regions or countries that you maintain in a Microsoft Excel worksheet.

(continued)

Control	Remarks

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 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 a Microsoft Excel worksheet that contains multiple cells, the DDE function returns the first entry.
Check box	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 a Microsoft Excel worksheet that contains multiple cells, the check box will be unavailable.

See Also DDEExecute statement, DDEInitiate function, DDEPoke statement, DDERequest function, DDESend function, DDETerminate statement, DDETerminateAll statement, Enabled, Locked properties.

Example

The following 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.

Sub ExcelDDE()
    Dim intI As Integer, intChan1 As Integer
    Dim strTopics As String, strResp As String, strSheetName As String

    On Error Resume Next                    ' Set up error handling.

    intChan1 = DDEInitiate("Excel", "System")    ' Establish link.
    If Err Then                                ' If error occurs, Excel may
        Err = 0                                ' not be running. Reset error
        Shell "C:\Excel\Excel.exe", 1        ' and start spreadsheet.
        If Err Then Exit Sub                ' If another error, exit.
        ' Establish Spreadsheet link.
        intChan1 = DDEInitiate("Excel", "System")
    End If

    ' Create new worksheet.
    DDEExecute intChan1, "[New(1)]"
    ' Get topic list, worksheet name.
    strTopics = DDERequest(intChan1, "Selection")
    strSheetName = Left(strTopics, InStr(1, strTopics, "!") - 1)
    ' Terminate DDE link.
    DDETerminate intChan1
    ' Establish link with new worksheet.
    intChan1 = DDEInitiate("Excel", strSheetName)
    For intI = 1 To 10                        ' Put some values into
        DDEPoke intChan1, "R1C" & intI, intI    ' first row.
    Next intI
    ' Make chart.
    DDEExecute intChan1, "[Select(""R1C1:R1C10"")][New(2,2)]"
    ' Terminate all links.
    DDETerminateAll
End Sub