CODEWITHSUNDEEP
pythonexcel
Oli
Oli

Reputation: 57

How to suppress "Update Links" Alert with xlwings

I am interfacing with Excel files in Python using the xlwings api. Some Excel files I am interacting with have old links which cause a prompt to appear when the file is opened asking if the user would like to update the links. This causes the code to hang indefinitely on the line that opened the book until this prompt is closed by a user. Is there a way to modify the settings of the Excel file so that this prompt will not appear or it will be automatically dismissed without opening the actual file?

I have tried using the xlwings method: 

xlwings.App.display_alerts = False

to suppress the prompt, but as far as I can tell this can only be run for an instance of Excel after it has been opened. There are some Excel api's that do not require a file to be open in order to read data like xlrd, but they are not very convenient for reading and copying large amounts of data (Multiple/Entire sheets of data).

The following code demonstrates the issue:

import xlwings as xw

wb = xw.Book(r'C:\Path\To\File\Filename')

print('Done')

On a regular Excel file the code proceeds through and prints "Done" without the need of user interference, but on an Excel file where the "update links" prompt comes up, it will not proceed to the print statement until the prompt is dismissed by a user.

Upvotes: 4

Views: 9966

Answers (3)

Scott
Scott

Reputation: 377

I presently have 20+ source workbooks that I loop though to extract some rows of data. It was intolerable to respond to the update links prompt of each opened workbook. I tried the other solutions here but none worked for me. After reviewing the cited xlwings docs, this is the solution that worked for me:

for fname in workbook_list:
    wb = xw.books.open(fname, update_links = False)
    # Extract some data...
    wb.close()

My environment is Win10Pro / Python 3.8.1 / pywin32 version: 303 / Excel 365 Subscription / xlwings 0.26.2

Upvotes: 1

Jon
Jon

Reputation: 2567

I believe there is an implementation in xlwings to avoid update links messages now. I was able to bypass these alerts by adding the following

app.books.open(fname, update_links=False, read_only=True, ignore_read_only_recommended=True)

You can see these arguments available in the documentation xlwings.Book.open(...)

Upvotes: 3

David Zemens
David Zemens

Reputation: 53623

Expanding on your first attempt -- you're not handling an App instance, rather you're trying to assign to the xlwings.App class.

However, it seems that the display_alerts doesn't successfully suppress this alert in xlwings, try this:

import xlwings as xw
app = xw.App(add_book=False)
app.display_alerts = False
wb = app.books.api.Open(fullpath, UpdateLinks=False)

Upvotes: 7

Related Questions