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 DDE server changes, 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: 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 the two remain synchronized.
The options for controlling how data are imported in are much more limited using a DDE link, as opposed to opening the file directly or copying using the clipboard. However, if a DDE link is saved to a Genstat DDE link file (file extension.gde), then the latest version of the data will always be read, avoiding problems with maintaining two versions of the data.
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 display a prompt offering you the opportunity to reconnect to the previous DDE server.
- To set up a DDE link select Spread | New | DDE Link.
- Set options as required then click OK.
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.
| Server |
The name of the DDE server to request data from (e.g. Excel or QPW for Quattro for Windows). The dropdown list shows the currently running DDE servers, or DDE Servers that have been specified using the Servers button (see below). 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 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 (in Excel this is in the form [workbook name]sheet name, such as [Book1.xls]Sheet1 or in Quattro for Windows this is a filename – e.g. C:\Data\Notebook1.wb3). The dropdown list shows the current topics (if any) for the specified DDE server. The default format for an Excel topic excludes the files directory. Only one DDE link to a file of the same name is available, i.e. links cannot 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 (in Excel this is a cell range in the format Rm1Cn1:Rm2Cn2 (e.g. R3C1:R8C4) or in Quattro for Windows it is in the format Sheet:c1m1..c2m2 e.g. A:B1..E25). The dropdown list shows the current topics (if any) for the specified DDE server and topic. For convenience, if the server is Excel, the item format c1m1..c2m2 (e.g. B2:BZ215) is also supported. |
| 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 columns names 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 Hot link and Send changes back are chosen, the column names are sent in both directions |
| Restrict sheet to initial size | Disables the addition or deletion of rows or columns so that the spreadsheet always matches the original size of the DDE link. This option is displayed 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. |
| Add to book | This dropdown list contains the names of any workbooks you have open in Genstat: if you have no workbooks open it will only contain New Book. |
| Servers | This button opens the DDE Server File Locations dialog that lets you specify the location of the executable files belonging to named DDE servers. |
Note that c represents column letters, whereas m and n are used for column and row numbers, respectively.
| Server | Topic | Item |
| 123W | Filename | Sheet:c1n1 ..c2n2 |
| MSACCESS | Database name; QUERY query name | DATA |
| MSACCESS | Database name; TABLE table name | DATA |
| QPW | Filename | Sheet:c1n1 ..c2n2 |
| EXCEL | [workbook name]sheet name | Rm1Cn1:Rm2Cn2 |
| MINITAB | [Project.MPJ]Worksheet.MTW | Cn1:Cn2 or Rm1Cn1:Rm2Cn2 |
| SPSSWIN | !DATA | [First Column Last Column n1 n2] |
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 rows and columns 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.
When you click the Servers button this opens the DDE Server File Locations dialog.
| Server name | The name of the DDE server. This is usually a single upper case word e.g. EXCEL, QPW, MSACCESS, 123W, etc. |
| Server EXE file location | The path to the EXE file that runs the DDE server. This can be set by selecting the filename with the Browse button. |
| Browse | Lets you navigate to the EXE file. |
| Add | Add the specified DDE server and EXE file into the list of DDE servers. If the DDE server already exists, this will update the EXE file information. |
| Change | Change the specified DDE server and EXE file in the list of DDE servers. |
| Remove | Remove the specified DDE server and EXE file from the list of DDE servers. |
| Start | Start the specified EXE file corresponding to that currently selected in the Server name item. |
| OK | Save the DDE server information and close the dialog. |
| Row number prefix | These fields can be changed to simplify use of Genstat with Excel when a language other than English is used on a PC. Excel can addresses spreadsheet cells using the row and column number in what is termed R1C1 notation, where R and C represent “row” and “column” respectively. However, other language settings may result in other letters being used for this purpose. The two entries in this dialog allow alternative row and column letters to be supplied.To find out what letters are currently used in Excel, as set up on your PC, enter the following formula into a cell of an Excel sheet. =ADDRESS(1,1,1,FALSE) The resulting text will be in the form <Row letter>1<Column letter>1. You should change the Row and Column letters in Genstat to match those displayed in Excel. |
| Column number prefix | See above. |
| Excel formula to obtain Excel filename | This is pre-filled in for you. |
- From the menu select Spread | Sheet | Edit DDE Link.
This displayed dialog shows the current DDE server, topic and item for an active DDE link to a spreadsheet and allows updates to be controlled. The DDE link can be suspended temporarily, restarted if suspended or can be stopped, in either direction.
| Suspend DDE updates from DDE server | Temporarily stop Genstat receiving DDE updates from the DDE server. This can be re-enabled later using the Restart link from DDE server item. |
| Suspend updates to DDE server | Temporarily stop Genstat receiving DDE updates from the DDE server. This can be re-enabled later using the Restart link to DDE server item. |
| Disconnect link to DDE server | Stop the DDE server updating Genstat permanently. |
| Disconnect link from DDE server | Stop Genstat updating the DDE server permanently. |
| Clear | Close the dialog without making any changes to the DDE link. |