12

I'm using the following code to open and display a workbook within Excel:

import win32com.client as win32excel = win32.gencache.EnsureDispatch('Excel.Application')wb = excel.Workbooks.Open('my_sheet.xlsm')ws = wb.Worksheets('blaaaa') excel.Visible = True

When the File 'my_sheet.xlsm' is already opened, Excel asks me whether I want to re-open it without saving.

How could I check in advance whether the workbook is already opened up and in case it is, just bring it to the front?

EDIT: Found out by now:

if excel.Workbooks.Count > 0:    for i in range(1, excel.Workbooks.Count+1):        if excel.Workbooks.Item(i).Name is 'my_sheet.xlsm':            wb = excel.Workbooks.Item(i)            break

And one more question: My worksheet contains some headers where I enabled some filtering. So when the filters are set, and when I open the Workbook from Python, it sometimes asks me to enter a unique name to save the filter. Why is that? This is the dialogue:

enter image description here

EDIT Ok here it says (in german language) that the latter problem is a known bug in 2007 and 2010 files:https://social.msdn.microsoft.com/Forums/de-DE/3dce9f06-2262-4e22-a8ff-5c0d83166e73/excel-api-interne-namen and it seems to exist if you open Excel-Files programmatically. Don't know whether there is a workaround.

askedOct 5, 2016 at 14:51
tim's user avatar

1 Answer1

19

While you found a solution, consider usingtry/except/finally block. Currently your code will leave the Excel.exe process running in background (check Task Manager if using Windows) even if you close the visible worksheet. As an aside, in Python or any other language like VBA, any external API such as this COM interface should always be released cleanly during application code.

Below solution uses a defined function,openWorkbook() to go two potential routes: 1) first attempts to relaunch specified workbook assuming it is opened and 2) if it is not currently opened launches a new workbook object of that location. The last nestedtry/except is used in case theWorkbooks.Open() method fails such as incorrect file name.

import win32com.client as win32def openWorkbook(xlapp, xlfile):    try:                xlwb = xlapp.Workbooks(xlfile)                except Exception as e:        try:            xlwb = xlapp.Workbooks.Open(xlfile)        except Exception as e:            print(e)            xlwb = None                        return(xlwb)try:    excel = win32.gencache.EnsureDispatch('Excel.Application')    wb = openWorkbook(excel, 'my_sheet.xlsm')    ws = wb.Worksheets('blaaaa')     excel.Visible = Trueexcept Exception as e:    print(e)finally:    # RELEASES RESOURCES    ws = None    wb = None    excel = None
answeredOct 5, 2016 at 18:03
Parfait's user avatar
Sign up to request clarification or add additional context in comments.

3 Comments

C'est parfait :-)
@Parfait your try/except/finally solution has been brilliant ! although I see recently for some reason a couple of my files has gotten stuck running in the background and I am not able to release them from taskmanager either. I want to delete those file but it says locked for editing. I dont want to be restarting my system everytime this happens. Is there a way out to release them?
Without more context on how those files are run, I cannot help. This solution releases only those resources it spawned. However, this specific answer launches the Excel application to screen and keeps both the app and workbook opened. To keep hidden in background and to release process use lines intry block:excel.Visible = False,wb.Close(True), andexcel.Quit.

Your Answer

Sign up orlog in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

By clicking “Post Your Answer”, you agree to ourterms of service and acknowledge you have read ourprivacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.