Create Spreadsheet Using a DDE Link

A Dynamic Data Exchange (DDE) server is a windows program that can provide data to other programs. GenStat can request data from a DDE server on a given Topic and Item. If the data is changed in the DDE server, GenStat can specify that the DDE server notifies it of the changes. This ability is known as a hot link. For example, Microsoft Excel is a DDE server and the available topics in Excel are sheets in open workbooks within Excel, and the DDE items are cell addresses/ranges. GenStat can create a spreadsheet from a given sheet within an open Excel file, and if you make a change to a cell in Excel, the corresponding cell within GenStat is automatically updated. Many DDE servers can also receive data, so any changes made in GenStat can be sent to the DDE server, so that it is kept synchronized with GenStat.

The data format from a DDE link is a rectangular table, with optional column names in the first row, and no empty rows or columns in the data.

GenStat can save the DDE link information in GenStat DDE Link format (file extension ".GDE"). Opening this File using the File Open menu will recreate a spreadsheet from the link information, containing the latest version of the data. When a spreadsheet is saved in GenStat spreadsheet format (.GSH), information about any current DDE link is saved as well. On reopening the GSH file, GenStat will prompt to offer you the opportunity to reconnect to the previous DDE server.

This menu can be also accessed through the Spread | Add | DDE Link menu item. If the spreadsheet previously contained a DDE link, then this information is filled into the dialog.

Server

The name of the DDE server to request data from (e.g. Excel or QPW for Quattro for Windows). The drop down button lists the currently running DDE servers, or DDE Servers that have been specified using the Servers button. The Windows system has two DDE servers, Shell and Folders that are always running, but these do not provide useful data to GenStat and can be generally be ignored.

If the DDE server has had an executable file defined using the Servers button, then choosing this item from the list will cause GenStat to start up the DDE server. If this is Excel, then it will still need to have the required file opened. However, fully defining the file name in the Topic entry will give GenStat enough information to allow it to automatically open the file if it's not currently loaded.

Topic

The name of the DDE topic to request data from (e.g. in Excel this is in the form [workbook name]sheet name - e.g. [Book1.xls]Sheet1 or in Quattro for Windows this is a filename - e.g. C:\Data\Notebook1.wb3). The drop down button lists the current topics (if any) for the specified DDE server.

The default format for an Excel topic excludes the files directory. Thus only one DDE link to a file of the same name is available, i.e. links could not be made to both files C:\Book1.XLS and C:\DATA\Book1.XLS. If the file name is provided in full, GenStat will open the file in Excel if it is not already open.

Item

The name of the DDE Item to request data from (e.g. in Excel this is a cell range in the format Rm₁Cn₁:Rm₂Cn₂ (e.g. R3C1:R8C4) or in Quattro for Windows it is in the format Sheet:c₁m₁..c₂m₂ e.g. A:B1..E25). The drop down button lists the current topics (if any) for the specified DDE server and topic.

For convenience, if the server is Excel, the Item format c₁m₁:c₂m₂ (e.g. B2:BZ215) is also supported.

Servers

Open the DDE Server File Locations dialog to specify the location of the executable files belonging to named DDE servers.

Hot link (receive changes from DDE Server)

If selected, the DDE server will notify GenStat of any changes made to the data and the corresponding cells in the GenStat spreadsheet will be updated automatically.

Send changes back to DDE server

If selected, the DDE server will be notified of any changes made within the GenStat spreadsheet, allowing it to update its data and display.

Send/receive Column name to/from DDE server

If selected, GenStat uses the first line of data from the DDE server as the column names in the spreadsheet. The Column names are then included in the data sent and/or received to/from the DDE server. The column names are only sent back if the Send Changes back to DDE server option is selected. If both hotlink and Send back are chosen, the column names are sent in both directions.

Restrict sheet to initial size

Disable the addition or deletion of rows or columns so that the spreadsheet always matches the original size of the DDE link. This option is only available when data is only being sent to the DDE server and there are no hot links.

Automatically expand item range to sheet size

When writing to Excel, Quattro or Lotus 123 for Windows, GenStat will automatically extend the Item range to take into account the spreadsheet changing in size. This option is only available when adding a link to an existing spreadsheet and there are no hot links to the DDE server.

OK

Create the GenStat spreadsheet from the given DDE server, topic and item.

Cancel

Close the dialog without creating a spreadsheet.

DDE Topics and Item formats by Server

Server	Topic	Item
123W	Filename	Sheet:c₁n₁..c₂n₂
MSACCESS	Database name; QUERY query name	DATA
MSACCESS	Database name; TABLE table name	DATA
QPW	Filename	Sheet:c₁n₁..c₂n₂
EXCEL	[workbook name]sheet name	Rm₁Cn₁:Rm₂Cn₂
MINITAB	[Project.MPJ]Worksheet.MTW	Cm₁:Cn₂ or Rm₁Cn₁:Rm₂Cn₂
SPSSWIN	!DATA	[First Column Last Column n₁ n₂]

Note that c represent column letters, whereas m and n are used for column and row numbers, respectively.

Foreign Language Settings

Using an English language setup for Windows, Excel can address spreadsheet cells with R1C1 notation, where R and C are the customary single-letter abbreviations for row and column. However, other language settings may result in other letters being used to specify row and column in the cell addresses. In this case, you should refer to the DDE Servers dialog (accessed via the Server button) which permits alternative settings to be specified in GenStat as well.