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