Blog

How to use Application.AutomationSecurity in the xlwings API way

The AutomationSecurity property of the Application object in Excel is a crucial setting for controlling macro security when automating Excel through external applications like Python using the xlwings library. It determines the security level that Excel uses when opening files programmatically, which can affect whether macros are enabled or disabled automatically. This property is particularly important in scenarios where automation scripts need to ensure consistent security behavior, especially in environments with varying macro settings. By setting AutomationSecurity, developers can programmatically override the default security settings of Excel, providing more control over macro execution during automated processes. This helps in maintaining security protocols while allowing necessary macros to run in controlled automation tasks.

In xlwings, the AutomationSecurity property is accessed through the app object, which represents the Excel application. The syntax for setting or getting this property is straightforward, as it corresponds directly to the Excel Object Model. The property accepts integer values that correspond to specific security levels defined by Excel. The primary values are:

• 1 (msoAutomationSecurityLow): This setting enables all macros to run without prompting. It is useful in trusted environments but poses security risks if used with untrusted files.
• 2 (msoAutomationSecurityByUI): Excel uses the macro security level set in the user interface (via Trust Center settings). This is the default behavior when automation is initiated.
• 3 (msoAutomationSecurityForceDisable): This setting disables all macros automatically, regardless of the file’s trust settings. It is the most secure option, preventing any macro execution.

To use this property in xlwings, you first need to instantiate an Excel application object. The property can be set before opening a workbook to influence how Excel handles macros in that file. For example, if you want to ensure macros are disabled during an automated data processing task, you can set AutomationSecurity to 3. Conversely, if you trust the source and need macros to run, set it to 1. It’s essential to note that changing this property affects all subsequent workbooks opened in that instance of Excel until it is changed again or the application is closed.

Here is a code example demonstrating the use of AutomationSecurity in xlwings:

import xlwings as xw

# Start a new Excel application instance
app = xw.App(visible=False) # Run Excel in the background

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

# Set AutomationSecurity to disable all macros (msoAutomationSecurityForceDisable)
app.api.AutomationSecurity = 3
print("AutomationSecurity set to disable all macros.")

# Open a workbook that contains macros
wb = app.books.open('example_with_macros.xlsx')

# Perform some operations, such as reading data
data = wb.sheets['Sheet1'].range('A1').value
print(f"Data from A1: {data}")

# Set AutomationSecurity back to use UI settings (msoAutomationSecurityByUI)
app.api.AutomationSecurity = 2
print("AutomationSecurity reset to UI default.")

# Close the workbook without saving
wb.close()

# Quit the Excel application
app.quit()

How to use Application.AutoFormatAsYouTypeReplaceHyperlinks in the xlwings API way

The AutoFormatAsYouTypeReplaceHyperlinks member of the Application object in Excel is a property that controls whether Excel automatically formats text that resembles a hyperlink address into a clickable hyperlink as you type. This is part of the “AutoFormat as you type” feature, which can help in quickly creating interactive documents by converting typed URLs or network paths into functional links without manual formatting. In xlwings, this property is accessible via the Application object’s API, allowing you to programmatically check or set this automation setting, which can be particularly useful in scripts that prepare or clean Excel workbooks for specific user interactions.

Syntax in xlwings:
In xlwings, you interact with this property through the app object, which represents the Excel Application. The property is exposed as a boolean attribute. The syntax is straightforward:

app.api.AutoFormatAsYouTypeReplaceHyperlinks

This property can be both read and written. When reading, it returns True if the feature is enabled, and False if disabled. When writing, you can set it to True to enable automatic hyperlink formatting, or False to disable it. There are no additional parameters or arguments for this property, as it is a simple toggle.

Example Usage with xlwings:
Below is a practical example demonstrating how to use the AutoFormatAsYouTypeReplaceHyperlinks property in a Python script with xlwings. This example checks the current setting, toggles it based on a condition, and then types some text to observe the effect (if Excel is visible). Note that changes to this setting may affect the user’s Excel session, so it’s often used in controlled environments or reset afterward.

import xlwings as xw

# Start or connect to an Excel application
app = xw.App(visible=True) # Set visible=True to see the typing effect
wb = app.books.add()

# Access the AutoFormatAsYouTypeReplaceHyperlinks property
current_setting = app.api.AutoFormatAsYouTypeReplaceHyperlinks
print(f"Current AutoFormatAsYouTypeReplaceHyperlinks setting: {current_setting}")

# Toggle the setting: enable if disabled, or disable if enabled
new_setting = not current_setting
app.api.AutoFormatAsYouTypeReplaceHyperlinks = new_setting
print(f"Setting changed to: {new_setting}")

# To see the effect, type a URL in a cell (requires Excel to be visible and active)
if app.visible:
    sheet = wb.sheets[0]
    sheet.range('A1').value = "Visit https://www.example.com for more info."
    # If enabled, Excel will format the URL as a hyperlink automatically as you type.
    # Note: In xlwings, setting cell value programmatically may not trigger the "as you type" feature,
    # as it simulates direct value insertion rather than keystroke-by-keystroke typing.
    # The feature primarily works during manual typing in the Excel interface.

    # Restore the original setting (optional, for user convenience)
    app.api.AutoFormatAsYouTypeReplaceHyperlinks = current_setting

# Save and close
wb.save('hyperlink_example.xlsx')
wb.close()
app.quit()

Important Notes:

• The AutoFormatAsYouTypeReplaceHyperlinks property affects the entire Excel application session, not just a specific workbook. Changing it will influence all open workbooks and future typing actions.
• In xlwings, when you set a cell’s value using .value, Excel may not apply the “as you type” formatting because it is not simulating real-time keystrokes. The feature is designed for manual entry in the Excel GUI. Therefore, toggling this property via xlwings is more about configuring the environment for user interaction rather than for programmatic data insertion.
• This property is part of Excel’s application-level options, so it’s persistent across sessions if saved in the user’s settings, but xlwings changes are temporary for the current session unless explicitly saved via Excel’s options dialog.

How to use Application.AutoCorrect in the xlwings API way

The AutoCorrect feature in Excel is a powerful tool for automatically correcting common typing errors, capitalizing days of the year, and expanding text abbreviations. Through xlwings, you can programmatically access and control these settings via the Application object’s AutoCorrect property. This allows for automation of tasks such as disabling auto-capitalization of days, adding custom replacement entries, or checking if a specific text string is in the AutoCorrect list.

Functionality
The AutoCorrect object provides control over Excel’s AutoCorrect options. Key capabilities include:

• Managing automatic capitalization of days of the week.
• Controlling the first letter capitalization in sentences.
• Adding, removing, or checking text replacements (e.g., replacing “(c)” with “©”).
• Enabling or disabling various auto-formatting features.

Syntax and Parameters
In xlwings, you access it through the app object (an instance of xw.App). The basic syntax is:

app.api.AutoCorrect.MemberName

Where MemberName is a property or method of the AutoCorrect object. Common members include:

Member	Type	Description	Parameters/Values
CapitalizeNamesOfDays	Property	Gets or sets whether days are auto-capitalized.	Boolean (True/False)
TwoInitialCapitals	Property	Controls auto-correction of two initial capitals.	Boolean
AddReplacement(What, Replacement)	Method	Adds a custom text replacement.	What (String): Text to replace. Replacement (String): New text.
DeleteReplacement(What)	Method	Removes a custom replacement.	What (String): Text entry to delete.
ReplacementList(Index)	Property	Returns a specific replacement pair.	Index (Integer): Position in the list (1-based).

Code Examples
Here are practical examples using xlwings:

1. Disable auto-capitalization of days:

import xlwings as xw
app = xw.App(visible=False)
app.api.AutoCorrect.CapitalizeNamesOfDays = False
app.quit()

2. Add a custom text replacement:

import xlwings as xw
app = xw.App(visible=False)
# Replace "xlw" with "xlwings"
app.api.AutoCorrect.AddReplacement("xlw", "xlwings")
app.quit()

3. Check and delete a replacement:

import xlwings as xw
app = xw.App(visible=False)
# Delete if "xlw" exists in the list
try:
    app.api.AutoCorrect.DeleteReplacement("xlw")
    print("Replacement deleted.")
except Exception as e:
    print("Entry not found:", e)
app.quit()

4. Iterate through replacement list (first 5 entries):

import xlwings as xw
app = xw.App(visible=False)
ac = app.api.AutoCorrect
for i in range(1, 6):
try:
    item = ac.ReplacementList(i)
    print(f"Index {i}: {item}")
except:
    break
app.quit()

How to use Application.Assistance in the xlwings API way

The Application.Assistance member in Excel’s object model provides access to the Help system, allowing developers to display specific Help topics programmatically. In xlwings, this functionality is exposed through the api property, which grants direct access to the underlying Excel VBA object model. This is particularly useful for creating user-friendly applications where context-sensitive help can be triggered based on user actions or inputs.

Functionality:
The primary purpose of Application.Assistance is to show a designated Help topic to the user. It can display built-in Excel Help topics using their unique Help IDs, which are often numeric or string identifiers. This enables developers to guide users directly to relevant documentation without requiring them to manually search through the Help system.

Syntax:
In xlwings, the syntax to call this member is:

app.api.Assistance.ShowHelp(HelpFile, HelpContextID)

• HelpFile: This is a string parameter that specifies the name of the Help file. For built-in Excel Help, this is typically set to "" (an empty string) or None to use the default Excel Help file. If using a custom Help file, provide its full path or name.
• HelpContextID: This parameter can be a string or numeric value that identifies the specific Help topic. For Excel’s built-in topics, this ID is often a numeric code corresponding to a particular subject. The exact IDs can be found in Excel’s VBA object model documentation or through developer resources. For example, the Help ID for the “Format Cells” dialog is "xlMainWindow" or a specific numeric ID like 27010 for certain topics.

Example:
Below is an xlwings code example that demonstrates how to use Application.Assistance to display a Help topic. This example assumes Excel is already running and a workbook is open via xlwings.

import xlwings as xw

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

# Display a built-in Excel Help topic, e.g., for general assistance
# Using an empty string for HelpFile defaults to Excel's Help
# The HelpContextID "xlMainWindow" opens the main Help window
app.api.Assistance.ShowHelp(HelpFile="", HelpContextID="xlMainWindow")

# Alternatively, to show a specific topic by numeric ID (example ID)
# This might open a topic like "About Excel" (ID varies by version)
app.api.Assistance.ShowHelp(HelpFile="", HelpContextID=27010)

# For a custom Help file (if available), specify the file path
# app.api.Assistance.ShowHelp(HelpFile="C:\\Help\\CustomHelp.chm", HelpContextID=100)

Notes:

• The effectiveness of this method depends on the availability and correctness of Help IDs. Some IDs may change between Excel versions, so testing across versions is recommended.
• If the HelpFile parameter is left empty and a valid HelpContextID is provided, Excel will attempt to open the corresponding topic in its default Help system.
• In cases where the Help topic cannot be found, Excel may display a generic Help window or an error, depending on the version.

How to use Application.AskToUpdateLinks in the xlwings API way

The Application.AskToUpdateLinks property in Excel’s object model is a Boolean value that controls whether Excel prompts the user to update links when opening a workbook containing external links. When set to True (the default), Excel displays a dialog box asking the user if they want to update the links. When set to False, Excel opens the workbook without prompting and does not automatically update the links, which can speed up the opening process in automated scripts or when the user does not need the latest linked data.

In xlwings, you can access and manipulate this property through the App object, which represents the Excel application. The syntax is straightforward: app.api.AskToUpdateLinks. Here, app is your xlwings App instance, and the .api attribute provides direct access to the underlying Excel object model, allowing you to use the standard Application object properties and methods. The property accepts and returns a Boolean value (True or False). It’s important to note that this setting is application-wide, meaning it affects all workbooks opened in that Excel instance while the setting is active.

For example, to disable the prompt for updating links when opening a workbook, you can set AskToUpdateLinks to False. This is particularly useful in automation scenarios where you want to suppress user interactions. After opening the workbook, you might want to restore the original setting to avoid affecting other operations. Here’s a code example:

import xlwings as xw

# Start Excel application (visible or not)
app = xw.App(visible=False)

# Disable the prompt for updating links
app.api.AskToUpdateLinks = False

# Open a workbook that contains external links
wb = app.books.open('workbook_with_links.xlsx')

# Perform operations on the workbook...
# For instance, you can manually update links if needed:
    # wb.api.UpdateLinks()

# Re-enable the prompt for future operations
app.api.AskToUpdateLinks = True

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

In this example, we start by creating an Excel application instance with visible=False to run in the background. Setting app.api.AskToUpdateLinks = False ensures that no dialog appears when opening workbook_with_links.xlsx. If you need to update the links programmatically, you can call wb.api.UpdateLinks() (though this is not directly related to AskToUpdateLinks). Finally, we reset the property to True before closing to maintain default behavior for other uses, and then clean up by closing the workbook and quitting the app.

Another common use case is to check the current state of this property. You can retrieve its value to determine if prompts are enabled:

import xlwings as xw

app = xw.App(visible=True)
current_setting = app.api.AskToUpdateLinks
print(f"AskToUpdateLinks is currently set to: {current_setting}")
# This might output: AskToUpdateLinks is currently set to: True
app.quit()