Archive

How to use Worksheets.Move in the xlwings API way

The Move member of the Worksheets object in Excel’s object model allows for repositioning a worksheet within a workbook. In xlwings, this functionality is accessed through the api property, which provides direct access to the underlying Excel object model. This is particularly useful for organizing sheets in a specific order, such as moving a newly created sheet to the beginning or end of the workbook.

Functionality:
The Move method relocates a specified worksheet to a new position relative to other sheets. It can place the sheet before or after another worksheet, enabling precise control over the sheet order. This is essential for creating reports or dashboards where the sequence of sheets impacts usability and presentation.

Syntax:
In xlwings, the syntax for moving a worksheet is:

worksheet.api.Move(Before, After)

• Before (optional): A Worksheet object representing the sheet before which the moved sheet will be placed. If specified, the moved sheet is positioned immediately before this sheet.
• After (optional): A Worksheet object representing the sheet after which the moved sheet will be placed. If specified, the moved sheet is positioned immediately after this sheet.

Notes:

• You must specify either Before or After, but not both. If both are omitted, Excel creates a new workbook containing the moved sheet.
• The parameters accept Worksheet objects, which can be obtained via xlwings (e.g., wb.sheets['SheetName']).
• Moving a sheet does not affect its content or formatting, only its position in the workbook tab order.

Code Examples:

1. Move a sheet to the beginning of the workbook:

import xlwings as xw
wb = xw.Book('workbook.xlsx')
target_sheet = wb.sheets['DataSheet']
first_sheet = wb.sheets[0] # Get the first sheet
target_sheet.api.Move(Before=first_sheet.api)

This moves ‘DataSheet’ to appear before the first sheet, making it the new first sheet.

2. Move a sheet to the end of the workbook:

import xlwings as xw
wb = xw.Book('workbook.xlsx')
target_sheet = wb.sheets['ReportSheet']
last_sheet = wb.sheets[-1] # Get the last sheet
target_sheet.api.Move(After=last_sheet.api)

This positions ‘ReportSheet’ after the last sheet, placing it at the end.

3. Move a sheet relative to another specific sheet:

import xlwings as xw
wb = xw.Book('workbook.xlsx')
sheet_to_move = wb.sheets['Sheet1']
reference_sheet = wb.sheets['Sheet3']
sheet_to_move.api.Move(After=reference_sheet.api)

How to use Worksheets.FillAcrossSheets in the xlwings API way

The FillAcrossSheets member of the Worksheets object in Excel is a powerful method used to copy a specified range from one worksheet to the same range on all other worksheets within the same workbook. This functionality is particularly useful when you need to maintain consistent formatting, formulas, or static data (like headers or standard values) across multiple sheets in a report or dashboard. Instead of manually copying and pasting to each sheet, FillAcrossSheets automates this process, ensuring uniformity and saving significant time.

In the Excel object model, accessed via xlwings, the syntax for this method is as follows:

Worksheets.FillAcrossSheets(Range, Type)

The method requires two parameters:

• Range: This is a required parameter. It specifies the range of cells to be copied across the worksheets. In xlwings, this is typically provided as an xlwings.Range object. You can define it using the Range property on a sheet object, for example, sheet.range("A1:D10").
• Type: This is an optional parameter that determines what content from the source range is copied. It accepts values from the XlFillWith enumeration. The most commonly used values are:
• xlFillWithAll (default, value = -4104): Copies everything from the source range—content, formulas, and formatting.
• xlFillWithContents (value = 2): Copies only values and formulas, but not the cell formatting.
• xlFillWithFormats (value = -4122): Copies only the cell formatting (like font, color, borders), but not the values or formulas.

If the Type parameter is omitted, the default behavior is xlFillWithAll.

Example Usage with xlwings:

Imagine you have a workbook with three sheets named “Q1”, “Q2”, and “Q3”. You want to set up a standard header in cells A1 through D1 on every sheet. You would write the header on the “Q1” sheet and then use FillAcrossSheets to propagate it.

import xlwings as xw

# Connect to the active Excel instance or create a new one
app = xw.apps.active

# Specify the workbook (use .books.active for the active workbook)
wb = app.books['Financials.xlsx']

# Define the range to copy (the header on the first sheet)
source_range = wb.sheets['Q1'].range('A1:D1')
source_range.value = ['Product', 'Region', 'Sales', 'Target'] # Set the header values
source_range.font.bold = True # Apply some formatting

# Use FillAcrossSheets to copy this range to all other worksheets in the workbook
# This targets the Worksheets collection of the specific workbook.
wb.sheets.api.FillAcrossSheets(source_range.api)

# To copy only the formatting of a complex template area:
template_range = wb.sheets['Template'].range('A1:F20')
wb.sheets.api.FillAcrossSheets(template_range.api, Type=-4122) # xlFillWithFormats

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

How to use Worksheets.Delete in the xlwings API way

The Delete member of the Worksheets collection in the Excel object model is a method used to remove a specified worksheet from a workbook. In xlwings, this functionality is exposed through the api property, which provides direct access to the underlying Excel object model, allowing for precise control over worksheet management. This method is particularly useful for automating the cleanup of temporary sheets, restructuring workbooks dynamically, or removing unnecessary data sheets in batch processing scenarios. Understanding its usage in xlwings ensures efficient workbook manipulation while maintaining compatibility with Excel’s native behavior.

Functionality
The Delete method permanently deletes a worksheet from the workbook. It does not move the sheet to the Recycle Bin; once deleted, the sheet cannot be recovered unless the workbook is closed without saving. In xlwings, this operation is performed by accessing the Excel API, so it mirrors the exact behavior of Excel’s VBA Delete method. This includes any prompts or warnings Excel might display, depending on the application settings.

Syntax
In xlwings, the Delete method is called via the api property on a Worksheet object. The syntax is:
worksheet.api.Delete()
Here, worksheet refers to an xlwings Worksheet object (e.g., obtained through wb.sheets['SheetName'] or wb.sheets[0]). The method does not take any parameters in its basic form. However, in Excel’s object model, the Delete method can be influenced by the application’s display alerts. To suppress confirmation dialogs, you can set xlwings.App().api.DisplayAlerts = False before deletion and restore it to True afterward.

Example
Below is a code example demonstrating the usage of the Delete member with xlwings. This example assumes you have an existing workbook with multiple sheets and want to remove a specific sheet programmatically.

import xlwings as xw

# Connect to an existing workbook (adjust the path as needed)
wb = xw.Book('example.xlsx')

# Access a specific worksheet by name
sheet_to_delete = wb.sheets['TempSheet']

# Disable Excel alerts to avoid confirmation prompts
app = xw.apps.active
app.api.DisplayAlerts = False

# Delete the worksheet
sheet_to_delete.api.Delete()

# Re-enable alerts
app.api.DisplayAlerts = True

# Save the workbook to persist changes
wb.save()

# Close the workbook (optional)
wb.close()

How to use Worksheets.Copy in the xlwings API way

The Copy method of the Worksheets object in Excel VBA is mirrored in xlwings through the api property, which provides direct access to the underlying Excel object model. This method is used to duplicate one or more worksheets, placing the copies either before or after a specified sheet in the workbook, or into a new workbook entirely. It is particularly useful for creating templates, generating reports, or backing up data without altering the original sheets.

Syntax in xlwings:

The method is accessed via the api property of a sheet object. The general syntax is:

sheet.api.Copy(Before, After)

• sheet: This is the xlwings Sheet object representing the worksheet you want to copy. You typically obtain it via wb.sheets['SheetName'] or wb.sheets[0].
• Before (Optional, Variant): A sheet object before which the copied sheet(s) will be placed. You cannot specify both Before and After.
• After (Optional, Variant): A sheet object after which the copied sheet(s) will be placed. You cannot specify both Before and After.

Parameter Behavior:

• If neither Before nor After is specified, Excel creates a new workbook and places the copied sheet(s) there. The new workbook becomes the active workbook.
• You must provide either the Before or After argument to place the copy within the same workbook. These arguments are passed as Excel sheet objects, which you can get via the .api property of an xlwings Sheet object.

Examples:

1. Copy a sheet to a new workbook:

import xlwings as xw
wb = xw.Book('MyWorkbook.xlsx')
source_sheet = wb.sheets['Data']
source_sheet.api.Copy() # Creates a new workbook with the copied sheet

2. Copy a sheet and place it before a specific sheet in the same workbook:

import xlwings as xw
wb = xw.Book('MyWorkbook.xlsx')
source_sheet = wb.sheets['Source']
target_sheet = wb.sheets['Target'] # The sheet before which we place the copy
source_sheet.api.Copy(Before=target_sheet.api)
# The new copy will be named "Source (2)" and appear before "Target".

3. Copy a sheet and place it after the last sheet in the same workbook:

import xlwings as xw
wb = xw.Book('MyWorkbook.xlsx')
source_sheet = wb.sheets['Report']
all_sheets = wb.sheets
last_sheet = all_sheets[len(all_sheets) - 1] # Get the last sheet object
source_sheet.api.Copy(After=last_sheet.api)
# The copy is placed at the end of the workbook.

4. Copy multiple worksheets (the entire Worksheets collection):
   To copy multiple sheets, you use the Worksheets collection’s Copy method. In xlwings, you can access this via the workbook’s sheets collection api.

import xlwings as xw
wb = xw.Book('MyWorkbook.xlsx')
# Select specific sheets to copy (e.g., first and third sheet)
wb.api.Worksheets([1, 3]).Copy() # Creates a new workbook with copies of sheets 1 and 3.
# Note: The index [1, 3] uses VBA's 1-based indexing.

How to use Worksheets.Add2 in the xlwings API way

The Worksheets.Add2 method in Excel’s object model is a powerful feature for creating new worksheets within a workbook. In xlwings, this method is accessible through the api property, which provides direct access to the underlying Excel object model. The Add2 method is an enhanced version of the traditional Add method, offering additional parameters for more control over the sheet creation process, such as specifying the sheet type. It is particularly useful for automating the generation of reports, dashboards, or data logs in Excel workbooks.

Syntax in xlwings:
The xlwings API call for the Add2 method follows this general format:
workbook.api.Worksheets.Add2(Before, After, Count, Type)

• Before (optional): A Worksheet object that specifies the sheet before which the new sheet will be added. If omitted, the new sheet is added after all existing sheets.
• After (optional): A Worksheet object that specifies the sheet after which the new sheet will be added. If both Before and After are omitted, the new sheet is added as the last sheet.
• Count (optional): An Integer that specifies the number of sheets to add. The default is 1.
• Type (optional): An XlSheetType constant that specifies the sheet type. Common values include:
• xlWorksheet (default): A standard worksheet.
• xlChart: A chart sheet.
• xlExcel4MacroSheet: A macro sheet (for compatibility).
• xlExcel4IntlMacroSheet: An international macro sheet.

Example Usage:
Here are several xlwings API code examples demonstrating the use of the Worksheets.Add2 member:

1. Adding a single worksheet at the end of the workbook:

import xlwings as xw
wb = xw.Book() # Open a new workbook
new_sheet = wb.api.Worksheets.Add2()
new_sheet.Name = "DataSummary"

2. Adding a worksheet before a specific sheet:

import xlwings as xw
wb = xw.Book('report.xlsx')
target_sheet = wb.sheets['Sheet1']
new_sheet = wb.api.Worksheets.Add2(Before=target_sheet.api)
new_sheet.Name = "Introduction"

3. Adding multiple chart sheets after a specific sheet:

import xlwings as xw
wb = xw.Book('data.xlsx')
after_sheet = wb.sheets['RawData']
# Add two chart sheets
chart_sheets = wb.api.Worksheets.Add2(After=after_sheet.api, Count=2, Type=-4109) # -4109 is xlChart
chart_sheets.Item(1).Name = "Chart1"
chart_sheets.Item(2).Name = "Chart2"

4. Using constants for sheet types (requires importing win32com.client or similar):

import xlwings as xw
from win32com.client import constants
wb = xw.Book()
new_chart_sheet = wb.api.Worksheets.Add2(Type=constants.xlChart)
new_chart_sheet.Name = "AnalysisChart"

How to use Worksheets.Add in the xlwings API way

The Add member of the Worksheets object in the Excel object model is a method used to create a new worksheet. In xlwings, this functionality is accessed through the api property, which provides direct access to the underlying Excel object model (via pywin32 on Windows or appscript on macOS). This allows you to programmatically add sheets to a workbook, offering control over the sheet’s position and name.

Functionality
The primary purpose of the Add method is to insert a new worksheet into a workbook. You can specify where the new sheet should be placed relative to existing sheets and what its name should be. This is essential for automating report generation, data organization, or creating dynamic dashboards where the number of sheets may vary based on the data.

Syntax in xlwings
The general syntax using xlwings is:
workbook.api.Worksheets.Add(Before, After, Count, Type)

The parameters are:

• Before (Optional, Variant): A worksheet object that specifies the sheet before which the new sheet will be added. You cannot use both Before and After.
• After (Optional, Variant): A worksheet object that specifies the sheet after which the new sheet will be added. You cannot use both Before and After.
• Count (Optional, Variant): The number of new worksheets to add. The default value is 1.
• Type (Optional, Variant): The type of sheet to add. Can be xlWorksheet (value -4167) for a standard worksheet or xlChart (value -4109) for a chart sheet. The default is xlWorksheet.

To use a parameter, you typically pass a worksheet object (e.g., wb.sheets['Sheet1'].api) for Before or After, or an integer for Count. If both Before and After are omitted, the new sheet is added before the active sheet.

Code Examples

1. Add a single worksheet with a default name (e.g., “Sheet4”):

import xlwings as xw
wb = xw.Book() # Opens a new workbook
new_sheet = wb.api.Worksheets.Add()
# The new worksheet object is now in 'new_sheet'

2. Add a worksheet after a specific sheet and rename it:

import xlwings as xw
wb = xw.Book('Report.xlsx')
# Add new sheet after the sheet named "Data"
new_sheet = wb.api.Worksheets.Add(After=wb.sheets['Data'].api)
new_sheet.Name = "Summary" # Rename the new sheet

3. Add multiple worksheets at the beginning of the workbook:

import xlwings as xw
wb = xw.Book()
first_sheet = wb.sheets[0].api # Get the API object of the first sheet
# Add 3 new sheets before the first sheet
wb.api.Worksheets.Add(Before=first_sheet, Count=3)

4. Add a chart sheet at the end of the workbook:

import xlwings as xw
from xlwings.constants import ChartType
wb = xw.Book()
last_sheet = wb.sheets[-1].api # Get the API object of the last sheet
# Add a chart sheet after the last worksheet
chart_sheet = wb.api.Worksheets.Add(After=last_sheet, Type=ChartType.xlChart)
# Note: Chart sheets are a different object type than worksheets in the Excel model.

How to use Workbook.Parent in the xlwings API way

The Parent property of a Workbook object in the xlwings API serves a fundamental role in navigating the Excel object hierarchy. It returns the parent object of the current workbook, which is typically the Application object representing the entire Excel instance. This property is read-only and is primarily used for object model traversal, allowing you to access higher-level application settings or other workbooks within the same Excel instance.

Functionality:
The main purpose of the Parent property is to provide a reference to the application that contains the workbook. This is useful when you need to perform operations at the application level, such as modifying Excel-wide settings (e.g., Application.ScreenUpdating), accessing other open workbooks via Application.Workbooks, or retrieving application properties like the version. It establishes a clear parent-child relationship where the workbook is a child of the application.

Syntax:
In xlwings, the syntax for accessing the Parent property is straightforward, as it follows the standard attribute access pattern in Python. The property does not accept any parameters.

workbook_parent = workbook_object.parent

• workbook_object: This is a required variable representing an instance of a workbook, typically obtained by using xw.Book() or through the books collection.
• The return value is an xlwings.main.App object, which is xlwings’ wrapper for the Excel Application object.

Code Examples:

1. Basic Access and Type Verification:
   This example demonstrates how to get the parent of an active workbook and check its type.

import xlwings as xw

# Connect to the active Excel instance and workbook
app = xw.apps.active
wb = app.books.active

# Get the parent of the workbook
parent_app = wb.parent

# Verify it is the same application object
print(f"Workbook's parent is the same as 'app': {parent_app is app}")
# Output: Workbook's parent is the same as 'app': True
print(f"Parent object type: {type(parent_app)}")
# Output: Parent object type: <class 'xlwings.main.App'>

2. Using the Parent to Control Application Settings:
   A common use case is to use the workbook’s parent to toggle application-level properties for performance optimization during a script.

import xlwings as xw

wb = xw.Book("Financial_Model.xlsx")
excel_app = wb.parent # Get the Application object

# Disable screen updating and alerts for faster execution
excel_app.screen_updating = False
excel_app.display_alerts = False

# ... Perform data processing or formatting operations on the workbook ...

# Re-enable screen updating and alerts
excel_app.screen_updating = True
excel_app.display_alerts = True
wb.save()

3. Accessing Other Workbooks via the Parent:
   This example shows how you can use the parent application to iterate through or access other workbooks that are currently open.

import xlwings as xw

# Start with a specific workbook
current_wb = xw.Book("Report_Q1.xlsx")
app = current_wb.parent

# Print the names of all other open workbooks
print("Other open workbooks in the same Excel instance:")
for other_wb in app.books:
    if other_wb is not current_wb: # Avoid listing the current workbook
    print(f" - {other_wb.name}")

# You can now activate or manipulate another workbook, e.g.:
# target_wb = app.books["Data_Source.xlsx"]

How to use Workbook.Item in the xlwings API way

The Item member of the Workbook object in the Excel object model is a property that provides access to individual worksheets or charts within a workbook by their index number or name. In xlwings, this functionality is not exposed through an explicit Item property as in VBA, but is instead accessed directly through the workbook’s indexing or via methods like .sheets[]. This design offers a more Pythonic and intuitive way to retrieve specific sheets, aligning with common Python container behaviors.

Functionality
The primary function is to return a single Sheet object (which can be a Worksheet or Chart object) from the Sheets collection of a workbook. This allows for targeted operations on a specific sheet, such as reading data, writing values, or formatting cells, without needing to activate or select it first.

Syntax & Parameters
In xlwings, you access a sheet by its index or name using the sheets property of a Book object (xlwings’ equivalent of a Workbook). The syntax is:

sheet = wb.sheets[index_or_name]

• wb: The xlwings Book object instance.
• index_or_name: This parameter can be:
• An int representing the sheet’s position (1-based index). For example, 1 refers to the first sheet tab from the left.
• A str representing the exact name of the sheet as it appears on its tab.

Unlike the VBA Item property, xlwings does not use a separate property call; the indexing is performed directly on the sheets collection.

Code Examples
Here are practical examples demonstrating how to use this functionality in xlwings:

1. Accessing a sheet by its index:

import xlwings as xw
# Connect to an existing workbook (ensure Excel is open or use app.books.open)
app = xw.App(visible=False)
wb = app.books.open(r'C:\path\to\your\workbook.xlsx')
# Access the first worksheet in the workbook
first_sheet = wb.sheets[1]
# Read a value from cell A1 of the first sheet
value_a1 = first_sheet.range('A1').value
print(value_a1)
wb.close()
app.quit()

2. Accessing a sheet by its name:

import xlwings as xw
# Start a new instance of Excel and create a new workbook
app = xw.App()
wb = app.books.add()
# Rename the first sheet for demonstration
wb.sheets[0].name = 'SalesData'
# Access the sheet by its exact name
sales_sheet = wb.sheets['SalesData']
# Write a value to cell B5
sales_sheet.range('B5').value = 'Quarterly Revenue'
# Save and close
wb.save('report.xlsx')
wb.close()
app.quit()

3. Iterating through all sheets using the collection:
   While the Item concept is for single access, you can loop through the sheets collection, which is the parent of the Item.

import xlwings as xw
wb = xw.Book('data_workbook.xlsx')
# Print the name of every sheet in the workbook
for sheet in wb.sheets:
    print(sheet.name)
    # You could perform operations on each 'sheet' object here
wb.close()

How to use Workbook.Creator in the xlwings API way

The Creator property of the Workbook object in Excel’s object model is a read-only attribute that returns a 32-bit integer representing the application that originally created the workbook. This value is a unique identifier, often used to distinguish between workbooks created by different versions of Excel or other applications that can generate Excel files, such as older Mac versions or third-party software. In xlwings, this property can be accessed directly from a Workbook instance, providing compatibility information that can be useful for debugging, version control, or conditional logic in automation scripts.

Syntax in xlwings:
workbook_instance.creator
This property does not accept any parameters. It returns an integer value. The meaning of specific integer values is not publicly documented by Microsoft in a comprehensive list, but common values include:

• 1480803660 (hex: 0x5843454C): Typically indicates the workbook was created by a version of Excel for Windows.
• 1480803660 (hex: 0x5843434D): Often associated with Excel for Mac.
  Other values may correspond to different creation sources.

Example Usage:
Suppose you have an Excel workbook and you want to check its origin before performing specific operations. You can use xlwings to retrieve the Creator value and act accordingly. Here’s a practical code example:

import xlwings as xw

# Open an existing workbook
wb = xw.Book('example.xlsx')

# Access the Creator property
creator_value = wb.creator

# Display the result
print(f"The workbook's creator code is: {creator_value}")

# Conditional logic based on the creator
if creator_value == 1480803660: # Common code for Excel Windows
    print("This workbook was likely created by Excel for Windows.")
elif creator_value == 1480803660: # Note: This is an example; actual Mac codes may vary
    print("This workbook may have been created by Excel for Mac.")
else:
    print("The creator application is unknown or from a different source.")

# You can also use it in automation, e.g., to log workbook origins
with open('workbook_log.txt', 'a') as log_file:
log_file.write(f"Workbook: {wb.name}, Creator Code: {creator_value}\n")

# Close the workbook if needed
wb.close()

How to use Workbook.Count in the xlwings API way

The Count property of the Workbook object in Excel’s object model is accessible through the xlwings library in Python, providing a straightforward way to retrieve the number of open workbooks in the current Excel application instance. This property is particularly useful for automation scripts that need to monitor or manage multiple workbooks dynamically, such as in scenarios involving batch processing, data consolidation, or application state checks. By using Count, developers can programmatically determine how many workbooks are active, enabling conditional logic based on this count—for example, to ensure that a specific number of workbooks are open before proceeding with operations or to iterate through all open workbooks for uniform modifications.

In xlwings, the Count property is accessed through the books collection of the App object, which represents the Excel application. The syntax for retrieving the count is as follows:

import xlwings as xw

# Connect to the active Excel instance or start a new one
app = xw.apps.active

# Get the number of open workbooks
workbook_count = app.books.count

Here, app refers to an instance of the Excel application (either retrieved via xw.apps.active or created anew), and app.books represents the collection of all open workbooks within that application. The count property is a read-only integer that returns the total number of workbooks in the collection. It does not accept any parameters, and its value is dynamically updated as workbooks are opened or closed during the session. This property is essential for scripts that require awareness of the workbook environment, ensuring robust error handling and efficient resource management.

For example, consider a scenario where you need to close all workbooks except the first one. The Count property can be used to determine how many workbooks are open and to loop through them appropriately:

import xlwings as xw

# Connect to Excel
app = xw.apps.active

# Check the number of open workbooks
if app.books.count > 1:
    # Keep the first workbook open and close the rest
    for i in range(app.books.count - 1, 0, -1):
    app.books[i].close()
    print(f"Closed {app.books.count - 1} workbooks.")
else:
    print("Only one workbook is open.")

In this code, app.books.count is used to verify if multiple workbooks are open. If so, it iterates backward through the workbooks collection to close all but the first one, preventing index errors that might occur if iterating forward while removing items. Another common use case is to log the count for auditing purposes, such as in automated reporting systems:

import xlwings as xw
import logging

logging.basicConfig(level=logging.INFO)
app = xw.apps.active
workbook_count = app.books.count
logging.info(f"Number of open workbooks: {workbook_count}")

# Proceed only if at least one workbook is open
if workbook_count == 0:
    raise ValueError("No workbooks are open. Please open a workbook and try again.")