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.