Archive

How to use Worksheet.ProtectScenarios in the xlwings API way

The ProtectScenarios member of a Worksheet object in Excel’s object model is a property that returns or sets a Boolean value indicating whether scenarios on the worksheet are protected when the sheet is protected. In the context of xlwings, a Python library for interacting with Excel, this property is accessible through the api property of a sheet object, which exposes the underlying Excel VBA object model. When a worksheet is protected using the Protect method, the ProtectScenarios property can be used to control whether users are allowed to edit, delete, or modify scenarios associated with the sheet. Scenarios are part of Excel’s What-If Analysis tools, allowing users to store and compare different sets of input values. By setting ProtectScenarios to True, you can lock these scenarios to prevent unauthorized changes, which is particularly useful in shared workbooks or templates where data integrity is critical.

In xlwings, the syntax for accessing and manipulating the ProtectScenarios property is straightforward. You first obtain a reference to the desired worksheet, typically through the Book object, and then use the api property to access the Excel object model. The property can be read or written using standard Python assignment. For example, to check the current protection status for scenarios on a sheet named “Sheet1”, you can use sheet.api.ProtectScenarios. To enable or disable this protection, you assign a Boolean value to it. Note that this property only takes effect when the worksheet itself is protected via sheet.api.Protect(). The parameters for the Protect method can be specified to customize protection settings, such as allowing users to select cells or format rows, but ProtectScenarios specifically focuses on scenario protection.

Here is a code example demonstrating the use of ProtectScenarios in xlwings:

import xlwings as xw

# Connect to an existing workbook or create a new one
wb = xw.Book('example.xlsx')
sheet = wb.sheets['Sheet1']

# Check if scenarios are currently protected
current_status = sheet.api.ProtectScenarios
print(f"Scenarios protection status: {current_status}")

# Protect the worksheet with scenarios protection enabled
sheet.api.Protect(Password="mypassword", ProtectScenarios=True)
# Alternatively, set the property after protection
sheet.api.ProtectScenarios = True

# Verify the setting
print(f"After protection, scenarios protection: {sheet.api.ProtectScenarios}")

# To disable scenarios protection while keeping the sheet protected
sheet.api.Unprotect(Password="mypassword") # Unprotect first to modify settings
sheet.api.Protect(Password="mypassword", ProtectScenarios=False)
# Or set directly if already unprotected
sheet.api.ProtectScenarios = False

# Save and close
wb.save()
wb.close()

How to use Worksheet.ProtectionMode in the xlwings API way

In Excel’s object model, the ProtectionMode property of a Worksheet object is a read-only property that indicates whether the worksheet is currently in a protected state. Specifically, it returns True if the worksheet is protected, and False if it is not. This property is useful for programmatically checking the protection status of a sheet before performing operations that might be restricted under protection, such as editing cells or modifying the sheet’s structure.

In the xlwings library, which provides a Pythonic way to interact with Excel via its COM interface, you can access the ProtectionMode property through the api property of a worksheet object. The api property exposes the underlying Excel object model, allowing direct access to native Excel properties and methods.

Syntax:
The xlwings API call to access the ProtectionMode property is straightforward:

worksheet.api.ProtectionMode

This returns a Boolean value (True or False). There are no parameters for this property as it is read-only. It simply reflects the current protection state set via Excel’s user interface or through other programmatic means (e.g., using worksheet.api.Protect() to enable protection).

Usage Example:
Here is a practical example demonstrating how to use the ProtectionMode property in xlwings to check and respond to a worksheet’s protection status. This can be part of a larger script that conditionally performs actions based on whether the sheet is protected.

import xlwings as xw

# Connect to an existing Excel workbook and select a specific worksheet
wb = xw.Book('example.xlsx')
ws = wb.sheets['Sheet1']

# Check the protection status of the worksheet
if ws.api.ProtectionMode:
    print("The worksheet is protected. Certain operations may be restricted.")
    # Optionally, you might want to unprotect the sheet temporarily for editing
    # ws.api.Unprotect(Password="your_password") # If a password is set
else:
    print("The worksheet is not protected. Proceeding with edits.")
    # Perform operations like writing data or formatting
    ws.range('A1').value = 'New Data'

# Example of toggling protection based on current status
if not ws.api.ProtectionMode:
    ws.api.Protect(Password="mypassword", DrawingObjects=True, Contents=True, Scenarios=True)
    print("Worksheet has now been protected.")
else:
    ws.api.Unprotect(Password="mypassword")
    print("Worksheet has been unprotected.")

# Close the workbook (save changes if needed)
wb.save()
wb.close()