OVERVIEW

SPEL comes with a number of canned reports OOTB but chances are they don't fit all your needs or they do not look like reports your company usually puts out.

The good thing is that the SPEL reporting engine simply propagates whatever data you want to an excel spreadsheet report and once this data has been dumped you can apply whatever VBA code you want to modify the formats or even manipulate data.

SOLUTIONS AND EXAMPLES

If you have taken Intergraphs training courses for SPEL you should be familiar with how to use their Add-In in Excel to setup reports and map properties to columns to get the raw data from SPEL to Excel.

When the report is initiated the raw values are dumped into the mapped columns row by row as text.

Once this is complete you can 'jump-in' to the process before it starts or finishes by adding code under the ThisWorkbook  in the VBA editor of the report once you have it open in Edit Mode.

Usually most of your code will be performed once the data has already been dumped by using

Public Sub EndReport()  
End Sub

Now you can add your code in this Sub but generally you would want to segregate it away in a module.

So for example

Public Sub EndReport()        SetPrintArea     End Sub  Public Function SetPrintArea()       Dim i As Integer       Do While Not IsEmpty(Cells(i,1).Value)        i = i + 1    Loop         ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(i, 14)).Address   End Function

This function will find the last row that has data in it and will set the print area of the report to that row and column N (14).

It would be interesting to see other snippets of VBA code others have used to clean up their reports.  Feel free to share them in the comment section.