Creating write-backs from Excel to the data server

Cascade Connect: Version 4.0
Prev | Chapter 7. Connecting to Excel | Next

# 7.6. Creating write-backs from Excel to the data server

To create write-backs, which allow you to send data from Excel to the data server, you must create a macro using Visual Basic. A typical macro would have the following contents:

1. Open a DDE communications channel from Excel to Cascade Connect.

2. Pass the data to Cascade Connect for transmission to the data server.

3. Close the DDE communications channel.

## 7.6.1. Sending a single variable

To send the value of a single spreadsheet cell to the data server you could use the following macro:

        '
        ' Cascade Connect Writeback macro
        '
        Sub Cascade_Writeback()
              mychannel = DDEInitiate("cascade", "data")
              Application.Worksheets("Sheet1").Activate
              DDEPoke(mychannel, "output1", Cells(1,3))
              DDETerminate mychannel
        End Sub

This macro first opens a channel between Excel and Cascade Connect by naming the Cascade DDE Service and Cascade DDE Topic. These names must be previously specified in the Setup window. See Using Excel to send data to the data server for more details.

The next step in the macro is optional but it ensures that the Excel worksheet named `Sheet1` is active before the data is sent.

Then the DDEPoke command sends the data from the cell at row 1 column 3 (A3) as the value of the point named `output1` through the open channel (which we have called `mychannel`) to Cascade Connect.

The last step is to terminate the link between Excel and Cascade Connect using the DDETerminate statement.

## 7.6.2. Sending multiple variables

When sending multiple values from Excel to Cascade Connect there are a few issues which you should be aware of to ensure that data is passed as efficiently as possible.

The DDEInitiate and DDETerminate statements that are used to open and close DDE links between applications are very CPU expensive. When sending variables at frequent intervals it would be more efficient to open a DDE channel at the beginning of the session and close it when you are finished. Here's an example:

        '
        ' Cascade Multiple Writeback macro
        '
        Sub Cascade_Writeback_Many()
              mychannel = DDEInitiate("cascade", "data")
              Application.Worksheets("variables").Activate
              DDEPoke(mychannel, "pointname1", Cells(1,2))
              DDEPoke(mychannel, "pointname2", Cells(2,2))
              DDEPoke(mychannel, "pointname3", Cells(3,2))
              DDEPoke(mychannel, "pointname4", Cells(4,2))
              DDEPoke(mychannel, "pointname5", Cells(5,2))
              DDEPoke(mychannel, "pointname6", Cells(6,2))
              DDETerminate mychannel
        End Sub

In this example the worksheet `variables.xls` contains six variables (`pointname1` through `pointname6`) that we wish to send to the data server. The DDEInitiate command opens the channel, then all six variables are sent to Cascade Connect before the link is closed.

There may be times when you need to send data on a continual basis from an application like Excel to Cascade Connect. This can create some problems. When you open a DDE channel using the DDEInitiate statement, and follow it with several DDEPoke statements, there is a chance that the DDE channel may fail after some time. One of the applications involved in the DDE transfer could be stopped by a user, or the DDE Topic could now be closed (someone may close the spreadsheet containing the data server links). For this reason, if you need to keep a DDE channel for an extended period of time, we suggest that you explore the complexities of writing Visual Basic Macros and attempt to deal with DDE errors within the macro.

Prev | Home | Next
Tips for Writing Reports in Excel | Up | The system variable

Copyright 1995-2002 by Cogent Real-Time Systems, Inc.