Legacy Excel reports

This article covers the Office-generated legacy Excel reports. We recommend that you use the Office reports (word, excel, pdf) instead, which can also be output in Excel format.

Legacy Excel reports are registered as Legacy Office reports in Vertec, just like Legacy Word reports. The file extension determines whether Excel or Word should be started to run the report.

Unlike Legacy Word reports, there is no built-in Vertec report builder for Legacy Excel reports. A Legacy Excel report must contain a macro written specifically for the report called DoReport2. This macro is called when the report runs and is responsible for inserting the report data.

The syntax is as follows:

Function DoReport2(vertec as Object, rootObj as Object, optargObj as Object, wrkbook as Workbook) as Boolean

This macro allows you to pass Vertec and Argument Objects, as well as the Workbook. This allows Legacy Excel Reports to run correctly even when multiple Vertec instances are running or when switching between different Workbooks.

The return value of the feature must be on True set, otherwise the Excel document will not be shown.

Function DoReport2(vertec as Object, rootObj as Object, optargObj as Object, wrkbook as Workbook) as Boolean
DoReport2 = True

End function

For long-running legacy Excel reports, the following message may occur after a certain period of time:

Microsoft Excel waits for an OLE action to finish in another application

This can be remedied by: in the code at the beginning:

Application.DisplayAlerts = False

is inserted.

It is important that this is turned off again at the end of the feature:

Application.DisplayAlerts = True

Evaluate OCL in comment fields

In a Legacy Excel report, as in Legacy Word reports, OCL expressions can be evaluated in comment fields. However, this has to be done manually.

In the cells where you want to calculate something, you need to add a comment and add the OCL expression. The author that Excel automatically sets must be removed manually. This is important because the following code can only handle pure OCL.

For example, a comment would look like this:

In the following example, the code is built directly into the DoReport2 feature:

Function DoReport2(vertec As Object, rootObj As Object, optargObj As Object, wrkbook As Workbook) As Boolean

'---"Generischer" Excel-Report Generator, evaluiert Kommentare und füllt einzelne Zellen.
Dim Sheet As Worksheet
Dim Comment As Comment
Dim OCL As String

Set Sheet = wrkbook.ActiveSheet

'---Fahre durch alle Kommentare durch und evaluiere das OCL.
'   ACHTUNG: die Kommentar müssen OHNE Autor sein, nur reines OCL.
On Error Resume Next
For Each Comment In Sheet.Comments
  Comment.Parent.Value = rootObj.eval(Comment.Text)
  Comment.Delete
  If Err <> 0 Then
    MsgBox Err.Description
    Err = 0
  End If
Next

DoReport2 = True

End Function