treestopper.jpg (302 bytes)

Site Configuration    Action Definition   DDE Control

When the TRi-ExcelLink program is loaded an internal DDEServer will be running regardless of whether the "Run" or the "Stop" button has been pressed. This allows other DDE clients such as a macro in the Excel spreadsheet or Wordbasic macro to remotely control the executions of the predefined "Actions" defined in the TRi-ExcelLinkprogram. The remote DDE client can even trigger the "Run", "Pause" and "Stop" button to control the operation of the TRi-ExelLink software. An Excel file "testmacro.xls" which contains some macros for sending DDERequest to the TRi-ExcelLink software can be found at the program's installation folder.

Only the DDERequest command is supported by the TRi-ExcelLink DDE server with the following parameters:

DDE  System Name:
"XLLINKSVR"  
DDE  Topic Name= "Action" Purpose -  For requesting the execution of an "Action" defined in the program. 

Item -   "SnAm" where n is the Site No. (1-8) and m is the Action No. (1-100).

Returned String:   OK - action has been scheduled successfully. Otherwise error string will be returned.

Note: Both active and inactive "Actions" can be executed by this DDERequest. However a Site must be active and running before its defined action can be executed due to the need to log-in to the TLServer of the defined Site.

DDE  Topic Name= "Command" Purpose -  For controlling the "Run", "Pause" and "Stop" button of the TRi-ExcelLink program.

Item   - Only three Item names are currently supported: "Run", "Pause" and "Stop".

Returned String:   OK - command has been performed.

 

Examples  1:  - Trigger the action defined at: Site #1, Action #1

Sub Macro1( ) 

channelNumber = Application.DDEInitiate("xlLinkSvr", "Action")
DataArray = Application.DDERequest(channelNumber, "S1A1")
Application.DDETerminate channelNumber
Sheets("Sheet1").Range(Cells(1, 1), Cells(1, 1)).FormulaArray = DataArray ' display the returned string at cell A1.

Example  2:  - Trigger the action defined at: Site #5, Action #67

Sub Macro1( ) 

channelNumber = Application.DDEInitiate("xlLinkSvr", "Action")
DataArray = Application.DDERequest(channelNumber, "S5A67")
Application.DDETerminate channelNumber
Sheets("Sheet1").Range(Cells(1, 1), Cells(1, 1)).FormulaArray = DataArray ' display the returned string at cell A1.

Example 3:  - Trigger the "Run" button of the TRi-ExcelLink software.

Sub RunXLLink( )

channelNumber = Application.DDEInitiate("XLLinkSvr", "Command")
DataArray = Application.DDERequest(channelNumber, "Run")
Application.DDETerminate channelNumber
Sheets("Sheet1").Range(Cells(1, 1), Cells(1,1)).FormulaArray = DataArray
End Sub

Site Configuration    Action Definition   DDE Control

 

The Action Definition window will be opened after you double-click any of the A1-A100 button in the Site Configuration Window.

ExcelLink3.gif (10803 bytes)

ExcelLink34.gif (270 bytes) Record the currently defined parameters for this Action.

ExcelLink35.gif (296 bytes) Cancel the current Action definition. The original Action parameters will be restored.

ExcelLink36.gif (275 bytes) Delete this Action definition so that the parameters defined here will not show up in the "Site Configuration" windows at the area of the A1-A100 buttons.
  • Active checkbox - Determine whether this Action you defined here is active. You can define a number of Actions and selectively activate some of them in accordance to your current objective. An "active" Action will be displayed as a light blue (A1-A100) button in the Site Configuration window for easy identification.  Only "active" Actions will be executed when the "Run" button in the Home Screen is pressed. ( Note: An inactive Action can  also be executed by remote DDE client using DDERequest commands as described in the document "Controlling ExcelLink from other DDE Client")
  • PLC ID: 00 to FF (hex) - Since each TLServer can connect to a network of PLCs on an RS485 network, you can specify the ID of the PLC for this Action. Theoretically up to 256 PLCs may be connected to a single TLServer. Enter the ID as a hexadecimal number from 00 to FF.
  • Period: If this is a READ Action this parameter determines how often the action should be repeated periodically. If you want to capture the value of a data every 5 seconds then set it to 50. If the period is set to 0 then this Action is only executed once whenever the "Run" button is clicked and the Action will not be repeated periodically.

    Note: The Period simply determine the "sleep" time between two consecutive actions and is NOT an exact, deterministic interval from one execution of this Action to its next execution. The time delay caused by execution of  each action is not accounted for. Some actions, such as reading or writing a range of DMs, may  take up to several seconds to complete and it will not be possible to execute other actions at the precise interval as specified in the "Period" field.

    If you define an Action as a "Write Single Variable" or "Write A Range of DMs", the program automatically changes the "Period" field  to 0.   There is no need to keep writing to the PLC unless there is a change in the data to be written. Hence these kind of Actions are only executed whenever the "Run" button in the home screen is pressed and when the data in the spreadsheet cell(s) have been changed (the latter is an automatic procedure).

  • Action: The are four choices available:  Read Single variable; Read a range of DM; Write Single variable, Write a range of DM.

    Read Single Variable  will extract the value of the target system variable (as defined by the Variable and the Var. Index fields) from the PLC and insert it into the Excel Spreadsheet cell (or range of cells) periodically.

    Write Single Variable  will take the data out of the spreadsheet cell (as defined in the "Excel Linkage Cell Locations" section) and write it into the PLC. 

    If  "Read A range of DMs" or "Write A Range of DMs" is  selected then the Var.Count field will become visible for you to define the number of DMs that you want read into the spreadsheet or write from the spreadsheet.  

excellink31.gif (2166 bytes)
  • Variable:  Select  from a list of all the system variables such as DM[n], INPUT[n]. OUTPUT[n].....etc. All the PLC's system variables, including all timer and counter Present Values (PV) as well as Set Values (SV) can be read from or written to the PLC.

    This selection, combined with the index to the system variable defined in Var. Index,  is used to select  the exact variable that you wish to read from or write to the PLC.

Note:  if the "Action" choice is either "Read A Range of DMs" or "Write A Range of DMs" then this selection is automatically fixed at "DM[n]" and cannot be changed.

ExcelLink32.gif (3692 bytes)
  • Var.  Count: This field only appears when the "Action" choice is either to "Read A Range of DMs" or "Write A Range of DMs".  It indicates the total number of DMs of interest, starting from DM[ value in Var. Index].

In this example, 10 DMs  starting from DM[3],   will be taken from the PLC and fill into the spreadsheet cells. i.e. DM[3] to DM[12] are the data to be captured into the spreadsheet.

Excel Linkage Cell Locations

The TRi-ExcelLink software lets you define where and how you want the captured data to be stored into the Excel spreadsheet. When capturing data from a single variable or a range of DMs, you can define it such that the captured data will either occupy a single cell (for "Read Single Variable") or populate a range of cells defined here. You even have a choice of whether the cells are being filled from left to right or top to bottom and whether the data should be rolled over once the data have populated the entire defined range of cells.

ExcelLink33.gif (3493 bytes)

  • Start Col & Start Row: The first cell location where data will be captured to or obtained from.  E.g. Cell A10 is Col.= A and Row = 10.
  • No. of Col. & No. of Row: These two numbers define a range of cells for capturing the data from READ actions or holding the data to be used for WRITE actions. If the action is to  read a single variable, then the entire range of cells will be filled one at a time with the direction defined by the radio buttons: "Fill Row, Then Down" or "Fill Column, Then Right". If you want only a single cell to capture the data in real time, then set both numbers to "1".

When "Write Single Variable" is selected, both the No. of Row and No. of Col. fields will be set to1. When  "Read A Range of DMs" or "Write A Range of DMs"  are selected, the No. of Row x No. of Col. has to be greater than or equal to the number defined in the "Var. Count" field to be accepted. Roll Over are not allowed in reading or writing DMs.

  • "Fill Row, Then Down" or "Fill Column, Then Right" radio buttons:  If "Fill Row, Then Down" button is selected, then in this example, the data will be filled starting from cell A10, B10, C10, A11, B11, C11..... all the way until C13.  If "Fill Column, Then right" button is selected, then the cells will be filled in the following order: A10, A11, A12, A13, B10, B11, B12, .... until C12, C13.
  • Roll Over when filled checkbox. When unchecked, the data capture operation will stop after the last cell (C13 in this example) is filled. If checked, then the data filling process will roll over to the first cell when the last cell has been filled. In this example, after filling the cell C13, the next captured data will be filled to the cell A10, B10...... and so on. If both No. of Row and No. of Col. are "1" then this checkbox will be automatically checked by the software to avoid the misperception that the data were not captured.
  • Time Stamp - If checked, then the column before (to the left) the "Start Col." will be used to record the time-stamp of when the data are being captured . In this case it is obvious that "Start Col." cannot begin from Column A because then there wouldn't be any column before column "A"  for recording the time-stamp.  The program automatically pushes the "Start Col." to column "B" when this happens.

Note that the time-stamp recorded will be based on the clock in the PC where the TRi-ExcelLink runs on, and not from the real time clock in the PLC.

Site Configuration    Action Definition   DDE Control