3.2.6 OLE/VBScript Post-processing option
The introduction
As mentioned earlier, XLSX-file is being generated on the application server, without the involvement of additional applications, and in most cases this is enough. However, in some cases, form has special requirements which can not be implemented while composing XLSX-file. In that cases, we are forced to perform post-processing of the XLSX-file using MS Excel application.
Post-processing procedure in XLSX Workbench is implemented by OLE / VBScript (depending on settings) and starts only if you have used appropriate options when developing a form.
Note: I strongly recommend you to use post-processing only when it is really needed, because it has a few negative factors:
It won't be performed in background mode of printing program run
It won't be performing if MS Excel application is not installed on frontend computer
It reduce performance
Control of Automatic page breaks
For example, we do not want to an automatic page break appears within a certain area of the sheet (such as the signature block in footer). Automatic (dotted) page breaks can't be implemented in XLSX-file in advance, because they are placed directly by the MS Excel application when opening a document (based on printer settings, etc.). Therefore, we do not know about automatic page breaks until the opening of the document by MS Excel.
Typically, this problem is being solved using VBA-macro that starts after opening the document and move the automatic page breaks beyond specified area.
Note: No reason to create macro in XLSX-Workbench to control of automatic page breaks, instead you just have to to set appropriate option for the "Folder" component. The option will be applied in the post processing using OLE/VBScript:
AutoFit for multiple merged cells (row height/ column width) by cell contents
When the cell is not merged, we can adjust the row height or column width to the fit the cell contents by using the AutoFit feature in the Excel template. But this function does not work for merged cells. Typically, this problem is being solved using VBA-macro.
Note: No reason to create macro in XLSX-Workbench to fit merged cells, instead you just have to to set appropriate option for any of the components: "Pattern" , "Resizable Pattern", "Grid". The option will be applied in the post processing using OLE/VBScript:
Final post-processing VBScript-routine for implementing Your own logic
For implementing of other specific requirements XLSX-Workbench provides ability of runnurg Final post-processing routine - VBScript macro, prepared by you. Noteworthy that - you can provide content from Context nested tables to your macro. Final post-pocessing routine starts directly after finishing of routines: Control automatic page breaks, AutoFit merged cells (see above).
As you know, XLSX format does not support VBA macro. However, we able to run VBScript routine using OLE. Noteworthy that VBScript works even with maximum level security settings in MS Excel !
VBScript is a subset of the VBA, and they are almost the same. However, there are few differences between them. For example, VBScript has below restrictions:
all variables and procedure/function arguments in VBScript have no type (are variants) ;
VBScript does not support named arguments - use positional argumentation ;
VBScript does not support debugging, therefore I recommend You write and debug macro in the VBA environment, and paste ready code to the VBScript-editor of Final post-processing.
To create Final post-processing VBScript-routine, you have to select root node in the tree of the form structure. On the properties tab, in the "Final post-processing" item, press button "VBScript". As result, VBScript code editor will appear :
You able to insert your own code into Sub-procedure Entry (this is the procedure of the Final post-processing).
You also able to add other procedures and functions, but Sub-procedure Entry must necessarily be present - it is the entry point for the Final post-processing.
Note: You must use objects XLWB_Application and XLWB_ActiveWorkbook instead objects Application and ActiveWorkbook.
Code
Result
Example 1 - Display simple message
Sub Entry()
MsgBox("This is Test message")
End Sub
Example 2 - Counting printing pages in the report
Sub Entry()
Set MySheet = XLWB_ActiveWorkbook.ActiveSheet
PgCount = MySheet.HPageBreaks.Count + 1
MySheet.Range("A1").Value = "The report contains " _
& PgCount & " pages"
End Sub
Example 3 - Call function with parameters
Sub Entry()
Call PasteValues("These", _
"are", _
"function's", _
"arguments")
End Sub
Function PasteValues(param1, param2, param3, param4)
Set MySheet = XLWB_ActiveWorkbook.ActiveSheet
MySheet.Cells(1, 1).Value = param1
MySheet.Cells(1, 2).Value = param2
MySheet.Cells(1, 3).Value = param3
MySheet.Cells(1, 4).Value = param4
End Function
Example 4 - Transferring context nested tables to macro
Sub Entry()
' add new worksheet
Set MySheet = XLWB_ActiveWorkbook.Sheets.Add
MySheet.Name = "TestVBScript"
' get table, which we have chosen from context and named T_LABELS
Set MyTable = XLWB_ActiveWorkbook.Container.Tables("T_LABELS").Table
' loop at table
For i = 1 To MyTable.Rows.Count
Set MyTableRow = MyTable.Rows(i)
MySheet.Cells(i, 1).Value = MyTableRow.Cell(1) 'Name
MySheet.Cells(i, 2).Value = MyTableRow.Cell(2) 'Street
MySheet.Cells(i, 3).Value = MyTableRow.Cell(3) 'Town
MySheet.Cells(i, 4).Value = MyTableRow.Cell(4) 'State
MySheet.Cells(i, 5).Value = MyTableRow.Cell(5) 'Zip
Next
End Sub
Example 4 demonstrates access to table T_LABELS , which is transferred from the context. To transfer table from context to the Final post-processing routine, you should press the button:
Popup screen with context tables (available for providing to routine) will appear. You should set checkbox for tables, which you want to provide to Final post-processing routine; you also should name tables (the name will be used for access to each table in the routine) .
Note 1: Only tables on top nesting level of context are available for choosing .
Note 2: Choose only tables consisting of simple fields (without nested tables and structures).
