VTS as DDE Server: Using DDE to Display Tag Values in an Excel Spreadsheet

When VTS is the DDE server, the values being read by VTS from the physical equipment are also reported to a cell in an Excel spreadsheet. The process of configuring VTS as a DDE server to display the values, name, I/O device, or other tag data in the cell(s) of an excel spreadsheet is fairly simple, and does not require the creation of a DDE tag. DDE tags are only used in situations where you wish to establish VTS as a DDE client either locally or on the network.

Configuring VTS to display data in the cells of a Microsoft Excel spreadsheet is a fairly straightforward process, once you are aware of the appropriate formula to use within the spreadsheet cells.

The formula has three parts: the name of the program; the file name or topic; and the cell range, value, field, or data that's referred to. The following example shows the parts of a remote reference formula and the required delimiters (separator characters).

The formula to be entered in an Excel spreadsheet cell is:

=VTS|Display!TagName\Variable

The above formula is not literal;  the sections of the above formula are defined in the table below.

 

Syntax	Description
=	The equals sign (=) indicates to Excel that a formula is to follow in the selected cell.
VTS	VTS is literal, indicating that VTS is the name of the program from which the data you wish to report in this cell originates.
|	The pipe character (|) is the program topic delimiter, separating the program name from the document or topic name.
Display	Following the pipe character is the document or topic name. In the case of VTS, this should be the Display Manager's title. By default, the title for the Display Manager is set to "Display". If you've specified a custom Display Manager title using the application's Config.ini configuration file, enter it in this section of the formula (see Display Manager Title below this table). Otherwise literally enter Display in this section of the formula. Note: If the title you've specified for the Display Manager has spaces in it, you must use single quotes to surround the title (see Use of Single Quotes below this table).
!	The exclamation point character (!) is the topic item delimiter, separating the document or topic name from the cell range, value, field or data to which you are referring.
TagName	Following the exclamation point character should be the name of the tag whose data you wish to be reported in the spreadsheet cell. The tag name should appear exactly as it is displayed in the name property of the tag properties folder. Note: If the tag name you've specified has spaces in it, you must surround the TagName\Property portion of the function with single quotes (see Use of Single Quotes below this table).
\	The back slash character (\) is the scope operator, separating the tag name from the property or data to which you are referring.
Variable	The name of one of the tag's variables whose value you wish to be reported in the cell; for example, Name, Area, Description, or I/O device. You may also use Value if you wish to report the actual value of the tag. Note: Variables are containers that hold tag values within the VTS scripting language. Although the VTS scripting language is used primarily by VTS Programmers, this is one instance where advanced VTS developers will require the names of variables to develop formulas and employ Dynamic Data Exchange. For your information, variable names for each tag type are provided earlier in this chapter (see " Tag Properties Database (Points.mdb).

Formula Examples

The following formula examples have been provided to assist you in setting up Excel spreadsheet cells to read values from VTS tags.

For example, if you wanted the value of a tag named AnalogInputTag to appear in a spreadsheet cell, you would enter:

=VTS|Display!AnalogInputTag\Value

If you wanted to display the name of the same tag's associated I/O device in a spreadsheet cell, you would enter:

=VTS|Display!'AnalogInputTag\IODevice'

Below, an example has been designed to help you configure an Excel spreadsheet to read data from a simple VTS application. For the purposes of this example, it is assumed that you are working with a VTS application consisting of an analog input tag and a digital input tag whose properties have been set according to the table below using the tag properties folder for each tag:

Tag Values Configured in VTS Application "DDE Test"

Tag Type	Name (ID Tab)	Description (ID Tab)	Manual Data (I/O Tab)
Analog Input	AITest	Testing an Analog Input	32
Digital Input	DITest	Testing a Digital Input	1

 

The following steps provide instructions on configuring a Microsoft Excel spreadsheet to read the above tag values from your "DDE Test" application. Two cells in the spreadsheet will be configured to report the value of the two tags, while two cells will be configured to report the description of the two tags.

1.   Create the tags identified in the table above in a test application. Be sure to name the tags exactly as shown above to ensure that this example works.

2.   Run the DDE Test application (or the alternate test application you've configured).

3.   Open Microsoft Excel. A new spreadsheet opens.

4.   Enter =VTS|Display!AITest\Value in the first cell (AI).

5.   Press the Enter key. The value 32 should appear in the first cell.

6.   Enter =VTS|Display!DITest\Value in the second cell (A2).

7.   Press the Enter key. The value 1 should appear in the second cell.

8.   Enter =VTS|Display!AITest\Description in the third cell (B1).

9.   Press the Enter key. The value "Testing an Analog Input" should appear in the third cell.

10. Enter =VTS|Display!DITest\Description in the fourth cell (B2).

11. Press the Enter key. The value "Testing a Digital Input" should appear in the fourth cell.

Use of Single Quotes in Formulas

If the name of the application, document, topic, or data item contains spaces or reserved characters such as colons (:) or minus signs ( – ) that have special uses in a formula, the text must be enclosed in single quotation marks.

=VTS|'My Application'!AITag\Description

In the case where spaces appear in the tag name, the entire tag name and variable must be enclosed in single quotes.

=VTS|Display!'Analog Input Tag\Area'

=VTS|'My Application'!'Analog Input Tag\Value'

Display Manager Title

When entering a formula to display VTS data in a spreadsheet cell, it is necessary to name the document or topic name following the pipe character. In the case of VTS, the document or topic name is the title given the Display Manager. By default, the Display Manager's title is set to "Display" by VTS. If you have not expressly configured a custom title for the Display Manager, you should literally use "Display" after the pipe character in your formulas as shown:

=VTS|Display!'Analog Input Tag\Area'

However, if you have used Config.ini to set a Windows title bar to appear over the Display Manager's title bar, and you have specified a custom title to be displayed in this Windows title bar, the custom title must be used after the pipe character in your formulas.

As you have discovered, the Config.ini configuration file allows you to change application-specific configuration settings for your VTS application. Two of the commonly used Config.ini [SYSTEM] section settings are the "DispMgrFullScreen" setting and the "DisplayManagerTitle" setting. An example of these settings is shown below:

[SYSTEM]

DispMgrFullScreen = 0

DisplayManagerTitle = My Application

 

As demonstrated above, the "DispMgrFullScreen" setting can be set to "0" to display a Windows title bar for your application, while the "DisplayManagerTitle" setting can be configured to display a custom title to the left of this Windows title bar.

If you've specified a name for the Display Manager's title bar, regardless of whether you've configured the Windows title bar to be displayed, you must use this custom title in the formula specified in the Excel spreadsheet cell in order for your data to be reported in the cell. Further, if the title you've specified has spaces or reserved characters in it, you must surround the title with single quotes, as instructed in "Using DDE to Display Tag Values in an Excel Spreadsheet".