Using VBScript in XLS Command Notes

From Support

Your project is, you need to protect an Excel worksheet, yet allow filtering. There is not a ready-made function for doing this with RPG2SQL Integrator. You can do this, however, using the XLS_RunScript function of RPG2SQL Integrator to run a VBScript file that contains the Excel functions you need. See ExcelRunScript RPG2SQL Function to run custom user VB script macros for basic information on using XLS_RunScript.

To get the code you need for the VBScript file, first open the workbook in Excel. Record a macro that performs the functions you want to do. In this case, go to Tools->Protection and click on Protect sheet.... In the dialog, go near the bottom of the list and check Use AutoFilter. Then open the VBA editor, using Alt-F11 and see something like the following in the code (this also shows selecting the active sheet):

If you set a password, it is not included in the recorded macro.

Sheets("Sheet1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True

VBScript does not work the same way as VBA does in Excel. One difference is, that it does not know what to do with named arguments, like Contents:=True. So you have to take off the keywords and use only the values, plus insert any empty arguments as placeholders.

The position of the arguments can be found by clicking F1 on the method in a macro in Excel. Keywords are ignored, only the value is necessary. Be sure that a comma is included for each skipped argument.

The modified form of the above code could look like the following, using the With block. sExcelObject is an argument passed in to the VBScript that represents the running instance of Excel.

VB Script

With sExcelObject
  .Sheets("sheet name").Select
  .ActiveSheet.Protect "password", True, True, True, , , , , , , , , , , True
End With

The first 3 True arguments are, respectively, DrawingObjects, Contents, and Scenarios. There are 10 arguments between Scenarios and AllowFiltering, so there have to be 10 commas.

In the RPGLE program, you enter a statement to execute XLS_RunSript and specify the name of the VBScript file and the name of the script within that file.

RPG code

C     Eval   Rtn = XLS_RunScript(SQL_Socket : filepathname : scriptname : ' ')

This will run the VBScript against the Excel file you opened earlier in the RPGLE program.