Show TOC

SetSheetOptionLocate this document in the navigation structure

This API enables you to specify an option that applies to the specified Microsoft Excel sheet, Microsoft Excel workbook or Microsoft PowerPoint presentation.

Parameter Description
File as Object Name of the Microsoft Excel worksheet, or name of the Microsoft Powerpoint presentation. Mandatory.
sheetOption Specify the option. See below the list of values available. Mandatory.
sheetOptionValue See below the list of values available. Mandatory.
password Specify the password when protecting a worksheet or a workbook. Mandatory for options 30 and 31.
sheetProtectionOptions as Long Specify only if you have specified 300 or 301 in the sheetOption parameter
Here is the list of values corresponding to the sheet options:
Option sheetOption Option sheetOptionValue
Expand Options 0    
    Children 0
    Member and Children 1
    Descendants 2
    Member and Descendants 3
Use as Input Form 1   TRUE or FALSE
Activate Member Recognition 2   TRUE or FALSE
Activate Local Member Recognition 3   TRUE or FALSE
Not EPM Worksheet 4   TRUE or FALSE
Totals on the left 5   TRUE or FALSE
Totals at the top 6   TRUE or FALSE
Remove empty rows 7   TRUE or FALSE
Note If you specify TRUE, the "Hide empty rows" is automatically set to FALSE.
Remove empty columns 8   TRUE or FALSE
Note If you specify TRUE, the "Hide empty columns" is automatically set to FALSE.
Hide empty rows 9   TRUE or FALSE
Note If you specify TRUE, the "Remove empty rows" is automatically set to FALSE.
Hide empty columns 10   TRUE or FALSE
Note If you specify TRUE, the "Remove empty columns" is automatically set to FALSE.
Show as Excel Comment 11   TRUE or FALSE
Use Position in Axis 12   TRUE or FALSE
Row Header Indentation 100   TRUE or FALSE
    No Identation 0
    Ident Children 1
    Ident Parents 2
Display Name 101    
    Full Unique Name 1
    Caption 2
Auto Fit Column Width 102   TRUE or FALSE
Repeat Column Headers 103   TRUE or FALSE
Repeat Row Headers 104   TRUE or FALSE
Set Default Value in Empty Cell 105   TRUE or FALSE
Empty Cell Default Value 106   (text that you want to be displayed)
Default Value when Member not Found in Shared axis 107   TRUE or FALSE
Member not Found Default Value 108   (text that you want to be displayed)
Apply Dynamic Formatting 109   TRUE or FALSE
Formatting Sheet Name 110   (text that you want to be displayed)
Clear Report Format Before Applying Dynamic Formatting 111   TRUE or FALSE
Hide empty rows and zero values 14   TRUE or FALSE
Hide empty columns and zero values 15   TRUE or FALSE
Remove empty rows and zero values 16   TRUE or FALSE
Remove empty columns and zero values 17   TRUE or FALSE
Keep Formula on Data 200   TRUE or FALSE
Show Source Data in Comments 201   TRUE or FALSE
Calculate Parents in Hierarchy 202   TRUE or FALSE
Refresh Data in the Whole File When Opening it 203   TRUE or FALSE
Clear Data in Whole File When Saving it 204   TRUE or FALSE
Apply only Report Editor Definition for Faster Refresh 206   TRUE or FALSE
Restore Dynamic Selection on Refresh after Navigation 207   TRUE or FALSE
Keep Formulas Static that Reference Report Cells 208   TRUE or FALSE
Protect Active Worksheet 300   TRUE or FALSE + enter the password in the password parameter.

Optional: enter sheet protection options

Protect Active WorkBook 301   TRUE or FALSE + enter the password in the password parameter

Optional: enter sheet protection options

Apply Query Definition after Variable Change 401   TRUE or FALSE
The sheet protection options are the ones used in Microsoft Excel, except that they are all prefixed with ProtectSheet_. For details about the options, see the Microsoft Excel documentation. Here is the list of options: ProtectSheet_AllowColumnDelete, ProtectSheet_AllowColumninsert, ProtectSheet_AllowFiltering, ProtectSheet_AllowFormatCells, ProtectSheet_AllowFormatColumns, ProtectSheet_AllowFormatRows, ProtectSheet_AllowHyperlinkInsert, ProtectSheet_AllowRowDelete, ProtectSheet_AllowRowInsert, ProtectSheet_AllowSorting, ProtectSheet_AllowUsingPivotTables, ProtectSheet_None, ProtectSheet_ProtectContents, ProtectSheet_ProtectDefault, ProtectSheet_ProtectDrawingObjects, ProtectSheet_ProtectScenarios, ProtectSheet_UserInterfaceOnly.
Caution
  • Enter: SheetProtectionOptions As Long
  • Each option must be separated by the character +
  • You must use ProtectSheet_ProtectContents to activate the protection. If you do not use it, the options will not be taken into account.
Note The Microsoft Excel sheet protection always overrides the EPM plug-in sheet protection. You can unprotect a sheet using the Microsoft Excel feature or the EPM plug-in feature. However, to protect a sheet, you must use the EPM plug-in feature.

Example

Protecting a worksheet, but allowing to delete and insert columns
Sub test()

Dim cofCom As Object
Set cofCom = Application.COMAddIns("SapExcelAddIn").Object

Dim api As Object
Set api = cofCom.GetPlugin("com.sap.epm.FPMXLClient")

Dim SheetProtectionOptions As Long
SheetProtectionOptions = FPMXLClient.ProtectSheet_ProtectContents + FPMXLClient.ProtectSheet_AllowColumnDelete +
FPMXLClient.ProtectSheet_AllowColumnInsert
api.SetSheetOption Sheet1, 300, True, "mypassword", SheetProtectionOptions

End Sub
where:
  • Sheet1 is the name of the sheet
  • 300 corresponds to the Protect Active Worksheet option
  • TRUE activates the option
  • the fourth parameter is where you enter a password in quotation marks
  • SheetProtectionOptions: applies the options declared in the line above

Example

Protecting a worksheet
Sub test()

Dim cofCom As Object
Set cofCom = Application.COMAddIns("SapExcelAddIn").Object

Dim api As Object
Set api = cofCom.GetPlugin("com.sap.epm.FPMXLClient")

End Sub
where:
  • Sheet1 is the name of the sheet
  • 300 corresponds to the Protect Active Worksheet option
  • TRUE activates the option
  • the fourth parameter is where you enter a password in quotation marks

Example

Setting the option that expands descendants
Sub test()

Dim cofCom As Object
Set cofCom = Application.COMAddIns("SapExcelAddIn").Object

Dim api As Object
Set api = cofCom.GetPlugin("com.sap.epm.FPMXLClient")

api.SetSheetOption Sheet1, 0, 2

End Sub
where:
  • Sheet1 is the name of the sheet
  • 0 corresponds to the Expand Options
  • 2 corresponds to the Expand Descendants option.

Example

Activating the Auto Fit Column Width option
Sub test()

Dim cofCom As Object
Set cofCom = Application.COMAddIns("SapExcelAddIn").Object

Dim api As Object
Set api = cofCom.GetPlugin("com.sap.epm.FPMXLClient")

api.SetSheetOption Sheet1, 102, True

End Sub
where:
  • Sheet1 is the name of the sheet
  • 102 corresponds to the Auto Fit Column Width option
  • TRUE activates the option