Python and Excel - check if a file is open
Hey guys, I need help given win32com in Python: I have a routine that opens a Workbook, creates a sheet, and puts some data on it. If everything works fine, the woorkbook is saved and closed. If the python session is not terminated but the woorkbook remains open. Thus, the link is lost. Now when reloading the Excel code asks you for the msg message, "The workbook is still open, do you want to reopen it?" So I want to suppress this msg. I found a solution that works for me when python ends before writing to the sheet:
open_copys = self.xlApp.Workbooks.Count
if open_copys > 0:
""" Check if any copy is the desired one"""
for i in range(0, open_copys):
if(self.xlApp.Workbooks[i].FullName == self.file_path):
self.xlBook = self.xlApp.Workbooks[i]
else:
self.xlBook = self.xlApp.Workbooks.Open(self.file_path)
But if any changes were made to the EXCEL sheet, this method is deprecated. Has anyone got and how to return a link to open and modified sheets from a new python session? THH
a source to share
Have you tried removing all references to COM objects before exiting the Python interpreter? You can force them to be garbage collected (using gc.collect ()) to make sure they aren't there. This way, the book should not stay open in memory and you will not receive an error message.
Try adding a "close ()" method to your class with something like the following and calling it before the end of your script.
import gc
...
def close(self):
del self.xlApp
if hasattr(self, 'xlBook'):
del self.xlBook
gc.collect()
a source to share
I am not familiar with Python but have made Excel / Word COM code in other languages.
The Excel property Application.DisplayAlerts
can help. Setting this to False
suppresses most of the messages that Excel normally displays and automatically selects the default answer, although I think there are some exceptions. If you look at the existing code, let's say you enter this line before opening a workbook:
self.xlApp.DisplayAlerts = False
a source to share
You are going on this wrong. You do NOT want Python to terminate, leaving Excel processes orphaned. This is especially important if you intend to install and run this code on other machines. Instead, find your errors and handle them - then you never have orphaned processes.
However, there are a few things you can consider. You can create an instance of a new Excel process each time (Dispatch) or work with an existing one (DispatchEx). This allows you to do things like see which books are open and close them, or make sure your process doesn't get in the way of others. Also, as Scott said, Excel has some interesting properties, such as error suppression for automatic operation, that are worth exploring.
a source to share