Dynamic Data Exchange (DDE) 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