Sample Code

The following is a simple working example that reads and writes spreadsheet cells in Microsoft Excel™. Each of the main segments of code in this section can be joined to make a complete script application that will compile and run.

{=========================== System ============================}
{===============================================================}
[
  Graphics Module { Contains user graphics }; 
  WinTitle = "Excel COM Tester" { Window title }; 
  System { Provides access to system library functions}; 
  RunningOnVIC            { TRUE if this is a VIC session           };
]


Init [
  If 1 Main;
  [
    RunningOnVIC = IsVICSession();
  ]
]

Main [
  Window(  0,   0           { Upper left corner   },
         800, 600           { View area           },
         800, 600           { Virtual area        },
         Graphics()         { Start user graphics },
          {65432109876543210}
         0b00010000000110011,
         Concat(WinTitle, RunningOnVIC ? " - %S" : ""),
         System\DialogBGnd, 1);
]

<
{====================== System\Graphics ======================}
{ This module handles all of the graphics for the application }
{=============================================================}
Graphics
[
   ExcelObj { The COM interface to the Excel object }; 
   ProgID { The ProgID for the Excel object }; 
   RangeObj { The COM interface to a "range of cells" object}; 
   CellsRead { Result of reading back some cells from Excel }; 
   CellsWritten { Cell values to be written to Excel }; 
   Kill = 0 { Gets set non-zero to terminate things }; 
   Row { Loop counter variable }; 
   Col { Loop counter variable }; 
   ValuesText { Text of the values read from spreadsheet }; 
]
Main [
{***** Instantiate the Excel object...Nothing happens yet *****} 
   ExcelObj = COMClient(ProgID); 
   {* But when the Excel object does become valid, make it visible ***} 
   If Edge(Valid(ExcelObj), 1); 
   [ 
      ExcelObj\Visible = 1; 
   ] 
   {***** When the Create button is pressed, make the ProgID valid. This causes the COMClient statement, above, to retrigger and ExcelObj to become valid *****} 
   If ZButton(10, 30, 90, 10, "Create", 1, System\DefFont); 
   [ 
      ProgID = "Excel.Application"; 
   ] 

Initially, "ExcelObj" will be Invalid. When the "Create" button is pressed, the ProgID is made valid, the COMClient statement re-triggers, and the Excel object is instantiated. On instantiation, the Excel application is run (if not already running), but remains invisible. "ExcelObj" becoming valid causes the second statement to execute, which makes the Excel application window visible. Invisibility is not a general trait of such COM objects, but rather the behavior of Excel. It is possible to leave the Excel application invisible and still use it. You can try this by removing the property set:

ExcelObj\Visible = 1; 

The completed application will function identically, but Excel will remain invisible.

The next requirement is code to shut down the COM object. Changing state would be sufficient to release the reference held on Excel and cause it to shutdown, however, Excel will not shutdown automatically if you have modified data within it, so you must make additional method calls to cause this to happen.

{***** Shutdown code. Pressing the "Kill" button stops the object only. Hitting the toaster bar close stops the object and kills this application *****} 
If ZButton(110, 30, 190, 10, "Kill", 2, System\DefFont); 
[ 
   Kill = 1; 
] 
If WindowClose(Self()); 
[ 
   Kill = 2; 
] 
If Kill; 
[ 
   { Close down the Excel workbook and quit Excel } 
   ExcelObj\Workbooks(1)\Close(0); 
   ExcelObj\Quit(); 
   { Excel will run until the hold is released} 
   ProgID = Invalid; 
   { Terminate this application if so instructed } 
   IfElse(Kill == 2, 
      Slay(ParentObject(Self()), 0); 
   { else } 
      Kill = 0; 
   ); 
] 

In the above code, the variable "Kill" is set to "1" if the "Kill" button is pressed, and is set to "2" if the application is closed. To force Excel to close with modified data, the following two method calls are made on the Excel object:

ExcelObj\Workbooks(1)\Close(0); 
ExcelObj\Quit(); 

Note that this would not be necessary if no changes were made to the data that Excel was operating with, and...

ProgID = Invalid; 

...would be sufficient to close Excel. Similarly, a change of state would cause the Excel COM object to shut down. However, the simple application given here has only one state, with the COMClient statement being steady-state, so invalidating the ProgID or terminating the application is the only way to release the object.

The statement above:

ExcelObj\Workbooks(1)\Close(0); 

…illustrates the use of "nested" interfaces. "ExcelObj" contains one or more "WorkBook" objects that can be accessed via the WorkBooks() method. For example:

ExcelObj\Workbooks(1); 

returns a COM Client Interface to the first workbook. Because the value returned is a COM Client Interface, it can be used to call methods on the object to which it is connected. This can be done either by storing the COM Client Interface in a variable for later use:

WorkBookObj = ExcelObj\Workbooks(1); 
WorkBookObj\Close(0); 

or by a direct call:

ExcelObj\Workbooks(1)\Close(0); 

In the latter case the "Workbooks" COM Client Interface will only exist temporarily - for the duration of statement execution.

The Excel object just instantiated, though, has no "workbook" object. To create one is simply another method call, which is made in response to the user pressing he "Workbook Add" button:

{***** Other buttons...Add a workbook *****} 
If ZButton(10, 90, 90, 70, "Workbook Add", Valid(ExcelObj) ? 3 : 0, System\DefFont); 
[ 
   ExcelObj\Workbooks\Add(); 
] 

Excel cells are represented by a "Range" object, which is an interface that can be obtained from the ExcelObj by a simple method call. This is made in response to the "Range Get" button:

{***** Get an object which represents a range of cells *****} 
If ZButton(110, 90, 190, 70, "Range Get", Valid(ExcelObj) ? 4 : 0, System\DefFont); 
[ 
   RangeObj = ExcelObj\Range("A1:C1"); 
] 

Assigning values to a range of cells is done by setting the "Value" property of the Range object to the VTScada values that will occupy those cells. Note that you can pass VTScada arrays as well as scalar values to a COM method or property. This is done in response to the user pressing the "Put A1:A3" button:

{***** Set the values of a range of cells *****} 
If ZButton(10, 120, 90, 100, "Put A1:A3", Valid(ExcelObj) ? 5 : 0, System\DefFont); 
[ 
   CellsWritten = New(3, 1); 
   CellsWritten[0][0] = 11; 
   CellsWritten[1][0] = 12; 
   CellsWritten[2][0] = 13; 
   ExcelObj\Range("A1:A3")\Value = CellsWritten; 
] 

Similarly, reading back a range of cells is done through the same "Value" property. However, this time the property is used on the right-hand side of the expression, and so is an implicit "property get" rather than the implicit "property put" that resulted from using the property on the left-hand side, above:

{***** Get the values of a range of cells *****} 
If ZButton(110, 120, 190, 100, "Get A1:A3", Valid(ExcelObj) ? 6 : 0, System\DefFont); 
[ 
   CellsRead = ExcelObj\Range("A1:A3")\Value; 
] 

Excel requires that the data supplied to the "Value" property put is dimensioned exactly the same as the range of cells into which the data is being put. Hence, the array dimension used was 3 rows by 1 column. Exactly the same holds true for putting data into multiple columns of the same row. This example places 1 row of 3 columns into the workbook:

{***** Set the values of a range of cells *****} 
If ZButton(10, 150, 90, 130, "Put B1:D1", Valid(ExcelObj) ? 7 : 0, System\DefFont); 
[ 
   CellsWritten = New(1, 3); 
   CellsWritten[0][0] = 21; 
   CellsWritten[0][1] = 22; 
   CellsWritten[0][2] = 23; 
   ExcelObj\Range("B1:D1")\Value = CellsWritten; 
] 
{***** Get the values of a range of cells *****} 
If ZButton(110, 150, 190, 130, "Get B1:D1", Valid(ExcelObj) ? 8 : 0, System\DefFont); 
[ 
   CellsRead = ExcelObj\Range("B1:D1")\Value; 
] 

In both cases, the result of the property get is an array dimensioned exactly the same as the cell dimensions.

ZText(210, 117, Concat("Cells Read Dimensions: ," PickValid(ArraySize(CellsRead, 0), "Invalid"),
      " ", PickValid(ArraySize(CellsRead, 1), "Invalid")), 
      0, System\DefFont); 

Finally, the last code segment renders the values read from the cells into a text string and displays it:

If Watch(1, CellsRead); 
[ 
   Row = 0; 
   ValuesText = "Cells Read: "; 
   WhileLoop(Row < ArraySize(CellsRead, 0), 
      Col = 0; 
      WhileLoop(Col < ArraySize(CellsRead, 1), 
         ValuesText = Concat(ValuesText, CellsRead[Row][Col], " "); 
         Col++; 
      ); 
      Row++; 
   ); 
] 
ZText(210, 147, ValuesText, 0, System\DefFont); 
]
{ End of System\Graphics }
> 

Compiling and running the application will result in the following user interface display: