Using a Microsoft Excel Template File to Generate Reports

VTS enables you to generate Microsoft Excel reports using a custom template file. Using such a file enables you to give each report you generate similar formatting and styles, or content and functionality.

The VTS installation comes with two default template files named, Example1.xlt, and Example2.xlt. These templates are stored in the Template directory within the VTS installation directory (e.g. C:\VTS\Template\).

Within each of these template files is a macro named, Complete. A macro is a mini-program you write or record to store a series of commands that you may run to automate complex or time-consuming tasks. In the case of these template files, the Complete macro sets the visibility of the report to true or visible, and saves the report.

The Complete macro is written using the Visual Basic programming language. The contents of the Complete macro within the Example1.xlt file are displayed below (comments are preceded by an apostrophe (')).

Sub Complete()

 

'This is an example of an Excel Template macro that can be used with VTS 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 be displayed 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

 

As you can see from the comments above, the Complete macro in Example1.xlt contains code to automatically display the spreadsheet when the report is complete (to enable this behavior, uncomment the Excel.Application.Visible = True line). An example of how to save the report spreadsheet with a hard-coded file name is included. Additionally, an example of how to save and name the report spreadsheet with the report name and range concatenated with the time at which it was generated is provided.

The contents of the Complete macro in Example2.xlt are displayed below.

Sub Complete()

 

'This is an example of an Excel Template macro that can be used with VTS 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

 

'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

ActiveWorkbook.SaveAs Filename:="C:\Reports\" + "VTSCADA Report " & Replace(Time$ + Date$, ":", "-") + ".xls"

 

' 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 

Next

' Exit here to prevent running error trap when complete

Exit Sub

' Do nothing error trap

ErrorCheck:

Resume Next 

End Sub

 

The Complete macro, as shown in the Example2.xlt file, automatically calculates the total, average, standard deviation and variance of the report data.

Topics in this section:

Creating a Custom Microsoft Excel Template File

Using a Custom Microsoft Excel Template File to Generate a Report