Python and Excel - check if file is open

Question

hey guys, I need help considering win32com in Python: I have a routine that opens a Workbook, creates a sheet and puts some data on it. If everything runs fine the woorkbook is saved and closed - If not the python session is terminated but the woorkbook is left open. So the reference is lost. Now when restarting the code Excel prompts you with the msg "workbook still open do you want to re-open?". So what I want is to suppress this msg. I found a solution that works for me when python terminates 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 on the EXCEL sheet this method is obsolet. Anyone got an ides how to get back a reference to an open and changed worksheet from a new python session? thx

Answer 1

I'm not familiar with Python but have done some Excel/Word COM code in other languages.
Excel's Application.DisplayAlerts property might help. Setting it to false suppresses most messages that Excel might normally show, and auto-chooses a default response, though I think there are some exceptions. Looking at your existing code, I guess you'd insert this line before opening the workbook:

  self.xlApp.DisplayAlerts=false

Answer 2

Have you tried to remove all references to your COM objects before terminating the Python interpreter ? You can force them to be garbage collected (using gc.collect()) to be really sure they are gone. This way the workbook shouldn't remain open in memory and you won't have the error message.

Try adding a "close()" method to your class, with something like the following, and call it before the end of your script.

import gc

...

def close(self):
    del self.xlApp
    if hasattr(self, 'xlBook'):
        del self.xlBook
    gc.collect()

Answer 3

You're going about this the wrong way. You do NOT want to let Python terminate, leaving orphaned Excel processes. This is especially important if you are going to install and run this code on other machines. Instead, find your errors and handle them - then you'll never have orphaned processes to deal with.

That said, there are a few things you can consider. You can choose either to instantiate a new Excel process each time (Dispatch) or work with an existing one (DispatchEx). This lets you do things like see what workbooks are open and to close them, or ensures that your process will not interfere with others. Also as Scott said, the Excel Application has some interesting properties, like suppressing errors for unattended running, that are worth learning.