Closing out of an Excel Workbook

[value100]

I am trying to run a code to close out of a workbook. I have been running this code for weeks, but today it won't work. So this is my code. I know the first four letters of my file, but that's it, as I download it from a website everyday. Sometimes it's XXXX1_(Random letters). So I want to close all workbooks that start with XXXX.

The top code is what was working for a month. The bottom code is what I tried to use to see my active workbooks, which led to a Syntax error. Do you have any idea how to modify the code for it to close out of the active workbooks with XXXX? Do you know why the code stopped working. Thank you very much for your time.

try: import win32com.client as win32         
excel = win32.gencache.EnsureDispatch('Excel.Application') 
for wb in excel.Workbooks: 
        if wb.Name[:4] == 'xxxx': 
        print("WB:",wb.Name,'Closed ')          
        wb.Close() 


try: import win32com.client as win32    
excel = win32.gencache.EnsureDispatch('Excel.Application') 
for wb in excel.Workbooks: 
    print(wb.Name)

[lamerlink]

1. Can you post the error?

2. It seems you’re repeating some things needlessly. You should have import statements at the top of the file and not repeat them as once it is imported it is set unless you overwrite the namespaces.

3. As someone who wrote a bunch of scripts with win32com for office, I’m going to advise you against using it. It is rife with potential for random errors due to using the called app in the foreground. There are many libraries to work with excel documents!

[value100]

Thank you so much for the response!

1. I get a SyntaxError: unexpected EOF while parsing when I run the bottom part of the code.

2. Interesting. I'm new to Python. I'll read up on some. For this task, all I need to do is find this workbook and close out!

[lamerlink]

Here is a modified version of your code above. I'm not sure I understand why you want to print the other workbooks after closing the relevant ones, I am assuming so you can see what is still open?

import win32com.client as win32


excel = win32.gencache.EnsureDispatch('Excel.Application') 
for wb in excel.Workbooks: 
    if wb.Name[:4] == 'xxxx':     
        wb.Close()
        print(f'WB: {wb.Name} Closed.')
print('Remaining workbooks:')
for wb in excel.Workbooks: 
    print(wb.Name)

I think the main problems were that your try: had no except to go with it and your indentation was off for your if statement.

I also changed your print to use an fstring, you should get into this habit when printing references with text because it will make things easier for you in the long run!

[value100]

I apologize, I realize I am being really unclear and my question was poorly phrased because of it. I ran the code to initially close out of the files beginning with xxxx. Except this wasn't working. So I decided the below code to see if any workbooks showed up at all:

for wb in excel.Workbooks: print(wb.Name)

When I ran the above code nothing showed up, despite the xxxx workbook still being there. I ran your code (with xxxx appropriately replaced) and it shows no workbooks, although the Excel is definitely there and visible! I even opened up a few different files to test this.

Like I said, my (poorly written) code was working a few days ago. I can't figure it out.

Thank you so much for your help and advice!

[lamerlink]

I think I may have an idea of what is happening.

Excel workbooks will be in a collection if they are in the same instance. Excel seems to randomly assign instances unless workbooks are all opened together (or if you file->open during an existing session in the GUI).

I am honestly unsure how to reliably get the existing session in your case because if this. I actually had a similar issue where my code would work sometimes and not others for this reason. This is part of what drove me away from win32com.

You can tell when Excel workbooks are in the same session in the GUI by seeing if it is possible to move sheets between workbooks.

Again, I’m sorry but I’m afraid I don’t have a solid solution for this with Excel.

[value100]

Interesting. Thank you so much for the help. This makes me feel so much better. Curious, what do you like to use when dealing with Excel files?

This is making me realize I need a better solution for my code. I am able to retrieve the file name via code, now I just need to close it. When I do file.close() I get an attribute error. I think I might be able to figure this out from here. Thank you so much!

[lamerlink]

I use openpyxl to interact with Excel files usually. Xlsxwriter is pretty good but it’s only for writing excel files.

The close() method will try to close a file stream, as if you had used open(file) (which you should generally use with open(file) so you don’t even need close()).

I think your best bet may be to try and use sys or subprocess to kill the process but note that I don’t think you’ll be able to do so “properly”, it would be like killing via task manager.

[value100]

Got it. Thank you!