1

I want to to close all open excel files from a folder only. Other excels should not be impacted. If I use below code then it kills all the open excel sessions, which I don't want. Kindly help-

os.system('TASKKILL /F /IM excel.exe')

Or

If I use below code then it is closing only one particular file, so this is also not working for me-

from win32com.client import Dispatch
xl = Dispatch('Excel.Application')
wb = xl.Workbooks.Add()
wb.Close(True, r'C:\Path\to\folder\Test.xlsx')

1 Answers1

2

As per What defines which Excel instance an Excel.Application COM object connects to? :

  • You connect to an existing Excel instance with win32com.client.GetActiveObject(<ProgID>)
  • You can only connect to one specific running Excel instance. If there are multiple running, the COM class always selects one of them according to certain logic (I call it "the "priority" one" further below).
  • You cannot connect to Excel instances running as different users (this includes with vs without elevation)

So if your files are open in multiple Excel instances, or in an instance that is not the "priority" one, the only way to iterate over them is to repeatedly connect to an existing instance then terminate it so that another instance comes "on top" (until there are none left in the 1st case, or until you get to the right one in the 2nd case). If there are instances running as different users, you'll need to run your code multiple times as all the corresponding users.

  • Fortunately, there can normally only be one "foreground" (i.e. visible and manually started) Excel instance per user which all the workbooks you open go to -- even though they show as separate windows (normally = unless you manually fiddled with the Application.Visible property), and any "background" ones (launched via COM programmatically) take priority.
    • So it should probably be safe to terminate any invisible instances and stop once you get to a visible one.

If however all your files are open in a single Excel instance that is the "priority" one, then you connect to it, iterate over the open workbooks and close the appropriate ones:

import os.path
import win32com.client

x = win32com.client.GetActiveObject("Excel.Application")
for wb in x.Workbooks:
    if os.path.dirname(wb.FullName) == your_dir:
        wb.Close(True)    # do specify the SaveChanges arg (as shown) --
                          # otherwise, Excel will ask you interactively if there are changes,
                          # hanging the program until you make a choice
                          # (unless that's what you want, of course)
    del wb
del x
ivan_pozdeev
  • 33,874
  • 19
  • 107
  • 152
  • thanks. May I know what is the use of ` del wb del x` –  Jul 17 '21 at 09:45
  • They make sure that you release any references to Excel COM objects -- [an Excel instance can't terminate while they are alive](https://stackoverflow.com/questions/42113082/excel-application-object-quit-leaves-excel-exe-running). – ivan_pozdeev Jul 17 '21 at 09:48
  • `x = win32com.client.GetActiveObject("Excel.Application") for wb in x.Workbooks: if path.dirname(wb.FullName) == new_download_path: wb.Close(SaveChanges=False) del wb del x ` I am using this script. but no luck :( –  Jul 17 '21 at 09:54
  • 1
    ["No luck" is not a problem statement](http://idownvotedbecau.se/itsnotworking/). – ivan_pozdeev Jul 17 '21 at 09:56
  • Sorry I mean, its neither working nor throwing any error. –  Jul 17 '21 at 09:58
  • Then debug it to see what's happening. Find out which kinds of Excels you have running (Process Explorer will help you much here), find out which of them you're connecting to and if the paths are what you expect them to be. – ivan_pozdeev Jul 17 '21 at 10:01
  • I found the error. Those excel reports are open in different session hence above code is not reading them. Trying to fix this. –  Jul 17 '21 at 10:14
  • 1
    Then you have the case of multiple instances that I've described in the 1st part of the answer. – ivan_pozdeev Jul 17 '21 at 12:16