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.
