Use an Excel Macro Template

You can choose to send report data to a customized Microsoft Excel™ template file. Excel template files can be used to apply formatting, and to generate summary calculations using a VBA macro.

To use this feature, you must:
  1) Have Microsoft Excel installed on your PC.
  2) Use the Screen Display report output option.
  3) Provide the name of the template file that you wish to use.

The VTScada installation comes with three sample template files, each provided in ".xlt" and ".xltm" formats. These templates are stored in the Examples directory within the VTScada installation directory (e.g. C:\VTScada\Examples\).

If help is required, there are many resources available in bookstores and on-line for both Excel and VBA. Trihedral does not offer support for either Microsoft Excel or the VBA language.

When running VTScada as a Windows service, DO NOT select Excel as an output destination or option from a Report Tag. As noted in the MSDN forums, office applications assume they are being run under an interactive desktop. If Excel attempts to open a modalClosed A modal dialog is always displayed on top of the calling window and prevents further interaction with that window. dialog from a non-interactive service, the result is an invisible dialog that cannot be dismissed, stopping the thread.

Besides the technical problems, you must also consider licensing issues. Microsoft's licensing guidelines prevent Office applications from being used on a server to service client requests, unless those clients themselves have licensed copies of Office. Using server-side Automation to provide Office functionality to unlicensed workstations is not covered by Microsoft's End User License Agreement (EULA).

Within each of these template files is a macro named, "Complete". A macro is a VBA program you write or record to perform a series of commands. In the case of these template files, the Complete macro sets the visibility of the report to true or visible, and saves the report.

  • Example1 is an empty template for a macro. The subroutine is defined but contains no code. Use this if writing a completely new template.
  • Example2 expects data from the VTScada standard report. It will save the Excel file to a new Reports folder, summarize each column of data using four functions from the Excel statistics library, and apply rudimentary formatting.
  • Example3 is functionally the same as Example2, but designed for use where VTScada runs as a Windows service. The report file is created and saved, but Excel never becomes visible during the process. This option may avoid the problems caused by a model dialog from a non-interactive service but the earlier warning stands.

Create an Excel Template

You must be familiar with using Visual Basic for Applications (VBA) to create or modify a macro within Microsoft Excel. The menus to open and therefore the steps to use within Excel will vary depending on the version you are using. The following procedure is provided as a guideline rather than a set of steps to be followed.

  1. Navigate to the Examples directory within the VTScada installation directory (e.g. C:\VTScada\Examples\).
  2. Open the template file you wish to use as a guideline.

The extension will be .XLT or .XLTM, depending on which version of Excel you use.

Be careful that you do not create a new file based on this macro instead of opening the macro for editing. Right-click on the file name and be sure to select Open rather than the default option, New.

  1. If prompted to enable macros, do so.
  2. Press the Alt-F11 key combination to open the VBA development environment.
  3. Locate Module1 in the Project area.

  1. Click to open the code window to edit the macro named Complete().

  1. Modify the macro as you require.

A way to do this that requires only a cursory knowledge of VBA programming is to start with a report that is saved to an unformatted Excel file. Turn on the macro recorder and adjust the report as you like. When the report looks the way you want, stop the macro recorder and examine the code that was created to learn how each step of the formatting process is coded into VBA.

  1. Expand the File menu and click, Save.
  2. Close the VBA development environment window.
  3. In Excel, select Save As from the file menu.
  4. Navigate to your application directory (e.g. "C:\VTScada\MyApplication\).
  5. Enter a name for the template file in the File name field.
  6. Close the custom template file.

Example1:

Sub Complete()

'This is an example of an Excel Template macro that can be used with VTScada reports.
'At this point the report module has already dumped the report data onto the spreadsheet.
'Your macro must be called Complete.
'To uncomment line, remove the preceding’

'If the macro makes any changes to the spreadsheet, then the user will not be able to
'close the workbook until they answer the save query dialog.
'To prevent this, set the Saved property of the workbook to 1
'Excel.Application.Visible = TRUE
'Uncomment the above line if you want your spreadsheet to display on report completion
'Leave it commented if you want report execution to take place silently (suitable for automatic reports)

'ActiveWorkbook.SaveAs Filename:="Output.xls"
'The above line is an example on how to save your file with a hard-coded filename.
'Default saving location for Excel is My Documents.

'ActiveWorkbook.SaveAs Filename:="C:\" + Replace(Worksheets("Sheet1").Range("A1").Value + " generated at " + Time$ + " on " + Date$, ":", "-") + ".xls"
'The above line is an example on how to save the file on your C drive with the filename being
'the report name and range concatenated with the time at which it was generated.

End Sub

The macro, "Complete" in file, "Example1" contains code to display the spreadsheet when the report is complete. (As noted in the comments, the relevant line of code must be uncommented.) An example of how to save the report spreadsheet with a hard-coded file name is included. Additionally, an example is provided of how to save and name the report spreadsheet with the report name and range, concatenated with the time at which it was generated.

Example2:

Sub Complete()
'This is an example of an Excel Template macro that can be used with VTScada reports.
'At this point the report module has already dumped the report data onto the spreadsheet.
'Your macro must be called Complete.
'To uncomment line remove the preceeding '
'If the macro makes any changes to the spreadsheet, then the user will not be able to
'close the workbook until they answer the save query dialog.
'To prevent this, set the Saved property of the workbook to 1
'This sample Excel template file automatically determines the extents of the data sent to it
'& calculates the total, average, standard deviation, and variance at the end of the report.
'This will work for any number of columns in any number of sheets in a workbook file provided
' that the title is always 2 rows high and that the time & date each occupy a column each.

' View the spreadsheet when it opens
Excel.Application.Visible = TRUE

'Save file name is set to current time & date
If Dir("C:\Reports", vbDirectory) = "" Then
  MkDir ("C:\Reports")
End If

If Val(Application.Version) < 12 Then
  ActiveWorkbook.SaveAs Filename:="C:\Reports\" + "VTSCADA Report " & Replace(Time$ + Date$, ":", "-") + ".xls"
Else
  ActiveWorkbook.SaveAs Filename:="C:\Reports\" + "VTSCADA Report " & Replace(Time$ + Date$, ":", "-") + ".xlsm", FileFormat:=52
' 52 = xlOpenXMLWorkbookMacroEnabled = xlsm (with macro's in 2007-2010)
End If

' Trap errors & ignore
On Error GoTo ErrorCheck
' Number of title rows
Trows = 2
' Number of timestamp columns
TCols = 3

' Cycle through all worksheets
For Each Sheet In ActiveWorkbook.Worksheets
  ' Go to the sheet
  Sheets(Sheet.Name).Select
  ' Record the active cell on the sheet
  ActiveCellPos = ActiveCell.Address
  ' Determine the area used on the worksheet using Range format
  UsedArea = Sheet.UsedRange.Address(ReferenceStyle:=xlA1)
  ' Get the number of rows & columns filled in
  C = Sheet.UsedRange.Columns.Count
  R = Sheet.UsedRange.Rows.Count
  ' Set comments Under Date/Time
  Cells(R + 1, TCols).Value = "Total"
  Cells(R + 2, TCols).Value = "Average"
  Cells(R + 3, TCols).Value = "Standard Deviation"
  Cells(R + 4, TCols).Value = "Variance"
  ' Formulas for each column
  For I = (TCols + 1) To C
    ' Formula for total
    Cells(R + 1, I).Activate
    ActiveCell.FormulaR1C1 = "=SUM(R[-" & R - Trows + 0 & "]C:R[-1]C)"
    ' Formula for average
    Cells(R + 2, I).Activate
    ActiveCell.FormulaR1C1 = "=AVERAGE(R[-" & R - Trows + 1 & "]C:R[-2]C)"
    ' Formula for Standard Deviation
    Cells(R + 3, I).Activate
    ActiveCell.FormulaR1C1 = "=STDEV(R[-" & R - Trows + 2 & "]C:R[-3]C)"
    ' Formula for variance
    Cells(R + 4, I).Activate
    ActiveCell.FormulaR1C1 = "=VAR(R[-" & R - Trows + 3 & "]C:R[-4]C)"
  Next I

  ' Select the calculated data & set to bold
  Rows(R + 1 & ":" & R + 4).Select
  Selection.Font.Bold = TRUE
  ' Bold the title line & set color
  Rows("1:" & Trows).Select
  Selection.Interior.ColorIndex = 35
  Selection.Font.Bold = TRUE
  ' Adjust column widths to fit data
  Rows("2:" & R + 4).Select
  Selection.Columns.AutoFit
  ' Set to freeze pane on first data cell
  Cells(Trows + 1, 1).Select
  ActiveWindow.FreezePanes = TRUE

  Rows(Trows + 1 & ":" & R).Select
  Selection.Rows.Group TRUE
  ActiveSheet.Outline.ShowLevels RowLevels:=1
Next

' Exit here to prevent running error trap when complete
Exit Sub

' Do nothing error trap
ErrorCheck:
Resume Next

End Sub

The macro, "Complete" in the file, "Example2" calculates the total, average, standard deviation and variance of the report data.