5.2. Getting Data out of Excel

Cascade DataHub™ : Version 5.4
	Chapter 5. Connecting Microsoft Excel

There are two ways to get data out of Excel and into the Cascade DataHub: by setting up a DDEAdvise loop to have the DataHub receive data automatically, or by using a DDEPoke command from a macro to write data to the DataHub manually. Here is a summary of the differences:

`DDEAdvise`

The data is sent immediately to the DataHub, every time the specified cell or range changes. This does not allow any kind of sanity check or safeguard on the data being sent, but in some cases it may be desirable to have Excel emit data automatically.
Each time data is sent for one point (data item), it is sent for all points. This can tie up your network if you have a large number of points. If you need to send data for a large number of cells, you can reduce this effect and reduce CPU load by sending a range that contains the cells.

`DDEPoke`

You manually send data from Excel to the Cascade DataHub, using a macro.
You can add safety checks to your macro to reduce the risk of broadcasting erroneous data.

5.2.1. Method 1 - Cascade DataHub as a Client, with `DDEAdvise`

The DataHub can act as a DDE client and receive data automatically from Excel acting as a server.

Open an Excel spreadsheet.
Name a cell or range. This is done by clicking in a cell, for example B2, and changing the default name to the name of the data point you want to use. To name a range, select the range and enter the name.
The name box for the cell or range is just above the first column of the worksheet.
Save the spreadsheet. This must be donebefore moving on to the next step.
Start the Cascade DataHub if it isn't already started, and open the Properties Window (by right-clicking on the DataHub icon in the Windows system tray and selecting Properties).
Click the DDE button.
Make sure the Act as DDE client box is checked.
Click the Add button. This opens the DDE Item Definition window where you can add Excel as a new DDE service.

Type in the following information:

Connection Name: choose a name to identify this connection. It must be unique among all DDE connections.
Service: type in Excel.

Topic: type the name of your worksheet file. The default file name is Book1, with no extension. If you have named the file, be sure to include the .xls extension, like this: my_filename.xls.

If you want to link to a cell or range which is not on the first sheet in the workbook, you need to put the filename in square brackets, followed by the sheet name. For example:

Sheet in workbook	Service	Topic to enter
The first sheet	`Excel`	`my_filename.xls`
An unnamed sheet (e.g. Sheet2)	`Excel`	`[my_filename.xls]Sheet2`
A named sheet (e.g. StockData)	`Excel`	`[my_filename.xls]StockData`

Item Names: type the same name you entered as the cell or range name in Excel (in step 2 above).

Click the Add button. The fields DDE Item, Point Name and Domain should automatically fill in with some values.
Check the names in the Point Name and Domain columns. If either of them is not what you need, double-click it to select it, and change it.
Click OK to close the DDE Item Definition window. The fields DDE Connection Name and Status in the Properties Window should now be filled in as well.
Click OK to close the Properties Window.
Open the Data Browser by right clicking the DataHub icon in the system tray and selecting View Data
With the default domain chosen, scroll down to see the name of the point.
In Excel, type a number into the cell or range you named in step 2, and press Enter. You should see the data update in the Data Browser.

Although this an easy way to send data from Excel, it is not the most efficient when you have many data values to send. Each time any cell in the worksheet updates, all the values for all the cells connected to the DataHub get resent. Rather than having the DataHub act as a DDE client for many connected cells, we suggest using a range or the DDEPoke to send data instead (see below).

When you save and close a spreadsheet connected to the Cascade DataHub, and then attempt to reopen it, you may get one or more messages, depending on your security settings in Excel, or other circumstances. Here's a summary of each message, and what to do:

This document contains macros. Enable them?: Click Enable Macros.
This workbook contains links. Update them?: Click Update. If the DataHub is already running, all the links should then update automatically. If the DataHub is not running, you will get a #REF! entry in each cell that has an advise loop established with the DataHub, and the next message (see below) will probably appear.
Remote data not accessible. Start DataHub?: Click No. At this point the best thing to do is close the worksheet, start the DataHub manually, and then reopen the worksheet. When you update the spreadsheet (see above) this time you won't get any #REF! entries. If, instead of No you click Yes at this point, the DataHub will not start, but instead generate an error message, and Excel may even crash later on.

5.2.2. Method 2 - Excel Macros using `DDEPoke`

The most flexible and efficient way to send data from Excel to the Cascade DataHub is by using DDEPoke from a macro. With DDEPoke you have complete control over when values get sent, and you can send the values for several data points at one time, which is explained in Additional Pointers. To activate the macro, it can be convenient to have a control button, which is explained in Add a Control Button.

5.2.2.1. Create a macro

Open a spreadsheet.
From the Tools menu, select Macro, and then Macros....
In the Macro Name: field of the Macro dialog box, type the name SendOutput, and press the Create button.

In the Visual Basic text entry window that comes up, edit the macro to read as follows:

'
' SendOutput Macro
'
Sub SendOutput()
    mychannel = DDEInitiate("datahub", "default")
    Application.Worksheets("Sheet1").Activate
    Call DDEPoke(mychannel, "my_pointname", Cells(4, 3))
    DDETerminate mychannel
End Sub

Use the name of your data point from the Cascade DataHub for my_pointname.

We use cell C4 in this example. If you need to use another cell, you will have to replace (4, 3) with the row and column numbers of the cell you wish to use. You can also name a range to send multiple values as an array.

Save and close the Visual Basic text entry window.

5.2.2.2. Add a Control Button

This explanation is illustrated in Section 5.1.2.2, “Add a Control Button”. We repeat the text briefly here.

Activate the Forms toolbar by clicking on the View menu and selecting Toolbars, and then Forms.
Click on the button icon, and then click in cell D4. (You can choose another cell if you'd like.) An Assign Macro window should appear.
Select SendOutput and click OK.
Change the label on the button to "Send".
Save the spreadsheet.

5.2.2.3. Send the data

Now you're ready to send the data. Open the Cascade DataHub Data Browser if it is not already open, go to the default domain, and find the name of the point.
In Excel, type a number in cell C4 (or the cell or range you assigned the macro to) and press Enter.
Click the Send button.
You should see the data update.

5.2.2.4. Additional Pointers

To reduce CPU for large amounts of data, send arrays of data using ranges instead of sending the data for each cell as a separate point.
The DDEInitiate and DDETerminate commands that are used to open and close DDE links between applications are also very CPU expensive. When sending variables at frequent intervals it is more efficient to open a DDE channel at the beginning of the session and close it when you are finished. Here are two suggestions:
1. Send multiple points within a single set of DDEInitiate and DDETerminate commands. For example:
```
'
' Cascade Multiple Writeback macro
'
Sub Cascade_Writeback_Many()
      mychannel = DDEInitiate("datahub", "default")
      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 named variables contains six variables (pointname1 through pointname6) that we wish to send to the Cascade DataHub. The DDEInitiate command opens the channel, then all six variables are sent to the DataHub before the link is closed.
2. Create a separate 'open' and 'close' macro for the worksheet, and place the DDEInitiate and DDETerminate commands in those macros. This will keep communication to the DataHub open for the whole time the worksheet is open. The only drawback is that your data transmission could get interrupted (see below).
If you need to send data continually from Excel to the Cascade DataHub you may run into problems using DDEInitiate and DDEPoke. 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. For this reason, if you need to keep a DDE channel open for an extended period of time, we suggest that you attempt to deal with DDE errors within the macro.