19.4. Working with Ranges
Prev Chapter 19. Excel Connections Next

19.4. Working with Ranges

The Cogent DataHub program can send and receive the data contained in an entire range of an Excel spreadsheet. This data is treated as an array, a two-dimensional range of cells as rows and columns. The array can be as big as necessary (within point size limits), or as small as a single cell—at least one row and one column.

Data Format

Excel transmits array data as a tab-and-newline delimited text string of values. Each value in a row is separated by a tab, and each row is separated by a newline character. The string does not contain any information concerning the source range of the array within the spreadsheet.

19.4.1. Getting a Range out of Excel

There are two methods of transmitting a range, or array data, from Excel to the DataHub instance. These exactly match the mechanisms used for individual point data: DDEPoke and DDEAdvise.

Using DDEPoke with a Macro

A DDEPoke command can be issued by Excel to send data to the DataHub instance based on a trigger within Excel. For this to work, the DataHub instance needs to be configured to act as a DDE server and have registered at least one service name. An Excel macro can then issue a DDEPoke to that service, along with a DataHub data domain name (the DDE topic), a point name (the DDE item) and a value. If the value is of type Range then Excel will automatically format the value as a tab-and-newline separated string.

Example: See the definition of the PutData function in the Excel macro coding examples below.

Using a DDE Advise Loop

When sending data from Excel to the DataHub instance using a DDE advise loop, Excel acts as the DDE server and DataHub instance acts as the client. To create the advise loop:

  1. Open the Cogent DataHub Properties Window (by right-clicking on the DataHub icon in the Windows system tray and selecting Properties).

  2. Click the DDE button.

  3. Make sure the Act as DDE client box is checked.

  4. Click the Add button. This opens the DDE Item Definition window.

  5. 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 (case is not important).

    • Topic  Type the name of your worksheet file, including the .xls extension, like this: my_filename.xls.

    • Item Names  These create a mapping between Excel cells and ranges, and DataHub point names. You may specify a single cell in r1c1 format, a range of cells in r1c1:r2c2 format, a cell name, or a range name as the DDE Item name. For example:

      r2c5 - accesses the cell E2 (second row, fifth column)
      r3c3:r5c9 - accesses the range C3:I5
      MyRange - accesses the cell or range that is named MyRange

  6. Click the Add button. The fields DDE Item, Point Name and Data Domain should automatically fill in with some values.

    [Note]

    Check the names in the Point Name and Data Domain columns. If either of them is not what you need, double-click it to select it, and change it.

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

  8. Click OK to close the Properties Window.

  9. Enter some values in the range of the spreadsheet you have defined. You should see the array in the Data Browser change accordingly.

Prev Up Next
19.3. Networking Excel Home 19.4.2. Getting a Range into Excel