Someone else is working in "path" right now excel after saving it on a shared drive in python using to_excel

Question

I have a python script reading, modifying and saving an excel file with several sheets -which works as expected. The file is saved on a shared drive. My save function looks as follow:

def save_xls(dict_df, path):
    writer = ExcelWriter(path)
    for key in dict_df:
        dict_df[key].to_excel(writer, key,index=False)
    writer.close()

However when I open the excel manually and would like to change a value in it I receive the following error message: Someone else is working in "path" right now. Please try again later.

I can re run the code, which will overwrite the excel.

Pandas: 1.5.3 Python 3.9

Thank you

I read online but unforunately was not able to find a solution to my problem

Answer 1

The error message you're encountering indicates that the Excel file is currently locked by another user or process, preventing you from making changes to it. In your case, the process that locks it for editing could be the editor you're using to run the code (such as VSC or Jupyter Notebook).

To avoid this, use the with statement instead of writer and writer.close():

import pandas as pd

def save_xls(dict_df, path):
    with pd.ExcelWriter(path) as writer:
        for key in dict_df:
            dict_df[key].to_excel(writer, sheet_name=key, index=False)

#test with these examples
df1 = pd.DataFrame({'Column1': [1, 2, 3], 'Column2': [4, 5, 6]})
df2 = pd.DataFrame({'Column3': ['A', 'B', 'C'], 'Column4': ['D', 'E', 'F']})
dict_df = {'Sheet1': df1, 'Sheet2': df2}

save_xls(dict_df, 'C:\\Users\\admin\\OneDrive\\Documents\\my_file.xlsx')

After this, you're able to open the 'my_file.xlsx' to edit and save freely without being locked.