Trying to delete files using Python, but permissions error says they are being used by another process

Question

I am using os, openpyxl and win32com.client in python to copy sheets from many files into 1. My code: '''

file_list = df['file_names'].unique().tolist()

#create a blank excel file
filepath = (report_path + '\\Combined Files.xlsx')
wb = openpyxl.Workbook()

wb.save(filepath)

# copy sheets in reverse order to new file
for s in (file_list[::-1]):
    path1 = report_path + '\\' + s + '.xlsx'
    path2 = filepath
    xl = Dispatch("Excel.Application")

    wb1 = xl.Workbooks.Open(Filename=path1)
    wb2 = xl.Workbooks.Open(Filename=path2)

    ws1 = wb1.Worksheets(1)
    ws1.Copy(Before=wb2.Worksheets(1))

    wb2.Close(SaveChanges=True)
    xl.Quit()

    # remove files
    for f in file_list:
    path = os.path.join(location, f)
    os.remove(path) 
    print("%s has been removed successfully" %f) 

'''

I get the following error when I run the code

os.remove(path) PermissionError: [WinError 32] The process cannot access the file because it is being used by another process: file one name

I have done a search, but could not find any answers that related to my code - or that I could figure out how to use Here, Here, Here, and Here

Answer 1

Looking at this more closely, it looks like your logic may be flawed. You should open up the output file (your "wb2 =" line) before the loop which reads in each file and adds it to the output file. So...

Open the output file for writing
For each input file in the reverse list:
    Open the input file
    Write its contents to the output file
    Close the input file
    Delete the input file
Write out/store the final output file

I think this is more along the logic you need.

==== Old Answer ==== Each time through your loop, you open two files (wb1 and wb2) but only close one (wb2). Try closing both. Could be unreleased resources due to a bug in the library code.

Also, your indentation is off, which makes it a little harder to understand what you're trying to do. And it's usually helpful if you can post code that runs, so that the error can be reproduced so that anyone posting an answer can easily verify the veracity of their posting.