3.1.2.6 OLE/VBScript Post-processing option

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 :

SAP Excel VBScript

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 .


  Код Результат


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


Whole example see here

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 demonstrate 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).