How to use Application.ExecuteExcel4Macro in the xlwings API way

The ExecuteExcel4Macro member of the Application object in Excel’s object model provides a way to run Excel 4.0 macro functions, which are legacy commands from older versions of Excel. While modern Excel primarily uses VBA, these functions can still be useful for specific tasks that are not directly supported by newer APIs, such as certain financial or engineering calculations. In xlwings, this functionality is accessed through the api property, which exposes the underlying Excel object model, allowing Python scripts to interact with Excel in a manner similar to VBA.

The syntax for calling ExecuteExcel4Macro via xlwings is straightforward. First, you need to obtain the Application object from an xlwings App or Book instance. Then, you can invoke the method. The method takes a single string argument, String, which represents the Excel 4.0 macro function you want to execute. This string should be formatted exactly as it would be in Excel 4.0, including any required arguments. For example, a common function is GET.CELL, which retrieves information about a cell. The parameter is provided as a plain string, and you must ensure it is correctly quoted and concatenated if variables are involved. There is no return value specification in the syntax itself; the output depends on the macro function called.

Here is a basic example that demonstrates how to use ExecuteExcel4Macro with xlwings to get the full path of the active workbook, using the Excel 4.0 function GET.DOCUMENT(1):

import xlwings as xw

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

# Execute the Excel 4.0 macro function to get the full path
full_path = app.api.ExecuteExcel4Macro("GET.DOCUMENT(1)")

print(f"Full path of active workbook: {full_path}")

Another example involves retrieving a specific cell’s value using GET.CELL. This can be useful for getting properties like the cell’s format or formula. In this case, you need to construct a reference string:

import xlwings as xw

# Connect to the active workbook and sheet
wb = xw.books.active
sheet = wb.sheets.active

# Define the cell address, e.g., A1
cell_address = "A1"

# Execute GET.CELL(6, A1) to get the formula in the cell (6 is the type_num for formula)
# Note: The reference must be provided as an R1C1-style reference or a named range
formula_result = wb.app.api.ExecuteExcel4Macro(f'GET.CELL(6, {cell_address})')

print(f"Formula in {cell_address}: {formula_result}")