How to use Application.SheetsInNewWorkbook in the xlwings API way

The SheetsInNewWorkbook property of the Application object in Excel specifies the number of worksheets that are automatically included when a new workbook is created. This setting is a global option within the Excel application instance, allowing users or automation scripts to define a default sheet count, which can improve efficiency by avoiding the need to manually add sheets after workbook creation. In xlwings, this property is accessed through the Application object, providing a programmatic way to both retrieve and modify this default value.

Functionality:
The primary function is to control the default number of worksheets in new workbooks. This is particularly useful in automation scenarios where a consistent starting structure is required, or when preparing templates that need multiple sheets by default.

Syntax:

# To get the current setting
current_sheet_count = xw.apps[0].api.SheetsInNewWorkbook

# To set a new value
xw.apps[0].api.SheetsInNewWorkbook = new_count

• xw.apps[0]: Represents the first (or a specific) Excel application instance controlled by xlwings. Use xw.apps.active for the active instance if multiple are open.
• .api: Provides direct access to the underlying Excel object model (the COM API).
• SheetsInNewWorkbook: The property being accessed. It expects an integer value.

Parameter/Value Details:

• Type: Read/Write Property (Integer).
• Value Range: The number must be an integer between 1 and 255, inclusive. Excel enforces these limits.
• Default: Typically 1 in a standard Excel installation.
• Persistence: This is an application-level setting in the current session. It is not permanently saved between Excel sessions unless configured within Excel’s options or set via a macro that runs on startup.

Code Examples:

1. Retrieving the Current Default:

import xlwings as xw

# Connect to the active Excel instance
app = xw.apps.active

# Get the current default number of sheets
default_sheets = app.api.SheetsInNewWorkbook
print(f"New workbooks currently start with {default_sheets} sheet(s).")
# Output example: New workbooks currently start with 1 sheet(s).

2. Changing the Default and Creating a Workbook:

import xlwings as xw

app = xw.apps.active

# Set the default to 3 worksheets
app.api.SheetsInNewWorkbook = 3

# Create a new workbook. It will now contain 3 worksheets automatically.
new_wb = app.books.add()
print(f"New workbook has {len(new_wb.sheets)} sheets.")
# Output: New workbook has 3 sheets.

# List the sheet names
for sheet in new_wb.sheets:
print(sheet.name)
# Output: Sheet1, Sheet2, Sheet3

3. Resetting to the Standard Default:

import xlwings as xw

app = xw.apps.active
# Reset to the common default of 1 sheet
app.api.SheetsInNewWorkbook = 1