Blog

How to use Application.Creator in the xlwings API way

The Creator property of the Application object in Excel’s object model is a read-only attribute that returns a 32-bit integer representing the application that created the file. In Excel, this value is typically used to identify whether the file was created by Microsoft Excel or another application, such as a third-party tool or a different version of Excel. In xlwings, the Creator property can be accessed to retrieve this identifier, which can be useful for compatibility checks, file validation, or logging purposes when automating Excel tasks.

Functionality:
The Creator property helps determine the origin application of an Excel file. For instance, if a file was created by Excel, the Creator value will correspond to Microsoft Excel’s identifier. This can be essential in scenarios where you need to ensure that files are processed only from specific sources or to troubleshoot issues related to file creation.

Syntax in xlwings:
In xlwings, you can access the Creator property through the app object, which represents the Excel application. The syntax is straightforward, as it is a property without parameters. Here’s the general format:

creator_value = app.api.Creator

• app: This is the xlwings App instance connected to Excel.
• api: This attribute provides access to the underlying Excel object model, allowing direct interaction with properties like Creator.
• Creator: The property that returns an integer representing the creator application.

The returned value is an integer. For Microsoft Excel, the typical value is 1480803660 (which corresponds to the hexadecimal 0x5843454C, representing “XCEL” in ASCII). Other applications may have different values. You can compare this integer to known constants to identify the creator.

Example Usage:
Below is a code example that demonstrates how to use the Creator property in xlwings to check if the current Excel file was created by Microsoft Excel. This example assumes you have an Excel application open and connected via xlwings.

import xlwings as xw

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

# Access the Creator property
creator_code = app.api.Creator

# Define known creator codes (example for Microsoft Excel)
EXCEL_CREATOR = 1480803660 # This is the standard value for Excel

# Check the creator and print the result
if creator_code == EXCEL_CREATOR:
    print("The file was created by Microsoft Excel.")
else:
    print(f"The file was created by another application. Creator code: {creator_code}")

# You can also convert the code to a hexadecimal string for easier interpretation
hex_creator = hex(creator_code)
print(f"Creator code in hexadecimal: {hex_creator}")

How to use Application.CopyObjectsWithCells in the xlwings API way

Application.CopyObjectsWithCells Property in xlwings

The Application.CopyObjectsWithCells property in Excel, accessible via the xlwings API, controls whether drawing objects (such as shapes, charts, pictures, and other embedded objects) are copied or moved along with their associated cells during cut, copy, or fill operations in a worksheet. This property is a global application-level setting, meaning it affects the behavior across all open workbooks in the Excel instance controlled by xlwings. It is particularly useful for automating tasks where you need to ensure that graphical elements remain attached to specific data ranges when those ranges are manipulated.

Syntax and Parameters in xlwings

In xlwings, you interact with this property through the app object, which represents the Excel Application. The property is exposed as a read/write Boolean attribute.

• Property Access:
• app.api.CopyObjectsWithCells (using the .api attribute to access the underlying Excel object model directly).
• Alternatively, you can use app.engine.api.CopyObjectsWithCells if working with a specific engine context in more advanced scenarios, but typically the app.api route is standard.
• Value:
• True: (Default) Drawing objects are copied, moved, or filled along with cells.
• False: Drawing objects remain in their original positions on the worksheet; only the cell contents and formats are affected by the operation.

Code Examples

Here are practical xlwings code snippets demonstrating how to get and set this property, and its impact on operations.

1. Checking the Current Setting:

import xlwings as xw

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

# Get the current value of CopyObjectsWithCells
current_setting = app.api.CopyObjectsWithCells
print(f"CopyObjectsWithCells is currently set to: {current_setting}")
# Output will be True or False

2. Changing the Setting and Performing a Copy Operation:
   This example disables the copying of objects, copies a cell range, and then restores the original setting.

import xlwings as xw

app = xw.apps.active
wb = app.books.active
sheet = wb.sheets[0]

# Assume cell A1 has a shape (e.g., a rectangle) over it and contains the number 10.
original_setting = app.api.CopyObjectsWithCells

# Set to False: Objects will NOT move with cells.
app.api.CopyObjectsWithCells = False
print("Set CopyObjectsWithCells to False.")

# Copy cell A1 to B1. Only the value (10) will be copied.
sheet.range('A1').copy(sheet.range('B1'))

# Verify: B1 now contains 10, but the shape remains only over A1.

# Restore the original application setting
app.api.CopyObjectsWithCells = original_setting
print("Restored original setting.")

3. Automating a Task with Controlled Object Behavior:
   A more integrated example where you temporarily enable object copying to duplicate a data section with its associated chart.

import xlwings as xw

app = xw.apps.active
wb = app.books['Report.xlsx']
data_sheet = wb.sheets['MonthlyData']

# Ensure objects are copied with cells for this specific operation
app.api.CopyObjectsWithCells = True

# Define the source range (A1:D10) which includes data and an embedded chart object
source_range = data_sheet.range('A1:D10')
# Define the target starting cell
target_range = data_sheet.range('A12')

# Copy the entire block, including the chart
source_range.copy(target_range)

# Optional: Reset to default (True) or a previous state if needed for other macros/users.
# app.api.CopyObjectsWithCells = False

How to use Application.ControlCharacters in the xlwings API way

The Application.ControlCharacters property in Excel’s object model is a member of the Application object that controls the display of certain control characters within cells. Specifically, it determines whether control characters (such as line breaks, carriage returns, or tab characters) are shown as visible symbols or are rendered as their functional effects (like actual line breaks). This property is particularly useful when dealing with text data imported from other systems that may contain these characters, allowing users to toggle their visibility for editing or debugging purposes. In xlwings, this property can be accessed and modified to customize how Excel handles these characters in the user interface.

In terms of syntax, the ControlCharacters property is accessed through the Application object in xlwings. The xlwings API provides a straightforward way to interact with this property using Python. The property is a Boolean value, where True means that control characters are displayed as visible symbols (e.g., a small square for a line break), and False means they are rendered normally (e.g., causing an actual line break in the cell). The xlwings call format follows the pattern of accessing properties from the app object, which represents the Excel application. For example, to get the current setting, you use app.api.ControlCharacters, and to set it, you assign a value like app.api.ControlCharacters = True. Note that app must be an instance of the xlwings App class connected to a running Excel application. This property does not take additional parameters; it is a simple read/write property that affects the entire Excel instance.

Here is a code example demonstrating the usage of Application.ControlCharacters with xlwings. First, ensure you have xlwings installed and an Excel workbook open. The example will toggle the display of control characters and print the current state:

import xlwings as xw

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

# Get the current ControlCharacters setting
current_setting = app.api.ControlCharacters
print(f"Current ControlCharacters setting: {current_setting}")

# Set ControlCharacters to True to show symbols
app.api.ControlCharacters = True
print("ControlCharacters set to True. Control characters will display as symbols.")

# Set ControlCharacters to False to render normally
app.api.ControlCharacters = False
print("ControlCharacters set to False. Control characters will render functionally.")

# Verify the change by getting the setting again
updated_setting = app.api.ControlCharacters
print(f"Updated ControlCharacters setting: {updated_setting}")

How to use Application.ConstrainNumeric in the xlwings API way

The ConstrainNumeric member of the Excel Application object is a property that controls whether Excel restricts numeric entry to a specific set of characters. This setting is particularly useful in environments where data entry must be standardized, such as when using numeric keypads or in locales with specific decimal and thousands separators. When enabled, it limits the characters that can be typed into cells to digits (0-9), the decimal point (which may vary by locale), the minus sign (-), and the slash (/) for fractions. This helps prevent accidental input of non-numeric characters, ensuring data integrity in worksheets that require pure numeric values. In xlwings, this property can be accessed and modified to automate the configuration of Excel’s behavior during data entry tasks, making it valuable for scripting scenarios where consistent numeric input is critical.

In terms of syntax, the ConstrainNumeric property is a Boolean type. It can be set to True to enforce numeric constraints or False to disable them. The xlwings API provides a straightforward way to interact with this property through the Application object. The general syntax is:

app.constrain_numeric

Here, app refers to an instance of the xlwings App class, which represents the Excel application. The property is read/write, meaning you can both retrieve its current value and assign a new one. For example, to enable numeric constraints, you would set app.constrain_numeric = True. Conversely, to check the current setting, you can read it with current_setting = app.constrain_numeric. Note that in xlwings, property names are typically in snake_case to align with Python conventions, even though the original VBA property is in PascalCase (e.g., ConstrainNumeric in VBA becomes constrain_numeric in xlwings).

To illustrate the usage, consider the following xlwings code examples. First, you might want to ensure numeric constraints are active before performing data entry operations. This can be done by setting the property at the start of a script:

import xlwings as xw

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

# Enable ConstrainNumeric to restrict input to numeric characters
app.constrain_numeric = True

print("Numeric constraints are now enabled.")

In a more dynamic scenario, you might toggle the setting based on user input or specific conditions. For instance, if you are automating a workbook that requires temporary relaxation of numeric constraints for text entry, you could disable and re-enable it as needed:

import xlwings as xw

app = xw.apps.active

# Disable numeric constraints to allow non-numeric input
app.constrain_numeric = False
print("Numeric constraints disabled. You can now enter text or symbols.")

# Perform some operations that require non-numeric input
# ...

# Re-enable numeric constraints after the operations
app.constrain_numeric = True
print("Numeric constraints re-enabled.")

Additionally, you can retrieve the current setting to log or make decisions in your script. This is useful for ensuring that the Excel environment is configured as expected before proceeding with data processing:

import xlwings as xw

app = xw.apps.active

# Check the current state of ConstrainNumeric
if app.constrain_numeric:
    print("Numeric entry is currently constrained to digits, decimal, minus, and slash.")
else:
    print("Numeric entry is not constrained; any characters can be input.")

How to use Application.CommandUnderlines in the xlwings API way

The Application.CommandUnderlines property in Excel VBA controls the underline style used for menu command access keys (the underlined letter that, when pressed with the Alt key, activates a command). In the xlwings Python library, which provides a programmatic interface to Excel’s object model, you can access and manipulate this property to adjust the user interface behavior of the Excel application instance. This can be useful for ensuring consistency in application appearance or for automating UI configuration tasks in scripts that interact with Excel via xlwings.

Functionality
The CommandUnderlines property determines whether access key underlines in Excel menus and dialog boxes are always visible, visible only when the Alt key is pressed, or follow the system setting. This is a remnant of older UI conventions but can still be relevant for accessibility or specific user preference scenarios when automating Excel. In xlwings, you can both read the current setting and change it programmatically.

Syntax
In xlwings, you access this property through the app object, which represents the Excel Application. The property is exposed as a simple attribute.

app.api.CommandUnderlines

This property accepts and returns an integer value corresponding to the XlCommandUnderlines enumeration. The primary values are:

Value	Constant (VBA)	Description
0	xlCommandUnderlinesAutomatic	Underlines appear based on the system setting.
1	xlCommandUnderlinesOff	Underlines are never shown.
2	xlCommandUnderlinesOn	Underlines are always shown.

Code Examples
Here are practical examples using xlwings to work with the CommandUnderlines property.

1. Reading the Current Setting:
   This code retrieves the current underline setting and prints a descriptive message.

import xlwings as xw

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

# Get the current CommandUnderlines setting
current_setting = app.api.CommandUnderlines

# Map the integer value to a description
setting_map = {
0: "Automatic (follows system)",
1: "Off (never shown)",
2: "On (always shown)"
}
description = setting_map.get(current_setting, "Unknown setting")
print(f"Current CommandUnderlines setting: {current_setting} ({description})")

2. Changing the Setting:
   This script changes the setting to always show underlines.

import xlwings as xw

app = xw.apps.active

# Set CommandUnderlines to always show (xlCommandUnderlinesOn)
app.api.CommandUnderlines = 2 # You can also use the constant 2 directly

print("Command underlines are now set to be always visible.")

3. Toggling the Setting Based on Current State:
   A more advanced example that toggles the setting between “On” and “Off”.

import xlwings as xw

app = xw.apps.active

current = app.api.CommandUnderlines
if current == 2: # If currently On
    new_setting = 1 # Turn Off
    print("Toggling command underlines OFF.")
else:
    new_setting = 2 # Otherwise, turn On
    print("Toggling command underlines ON.")

app.api.CommandUnderlines = new_setting