Is there a way to copy or open/read a xlsx file while it is in use by another user using Python (Windows)?

Question

I have searched far and wide, but can't find the right solution. All I want to do is get a sum and count of a column and insert that sum in to another workbook. That works fine when no one is in the source workbook. I can easily open the file by going to the file and clicking read only. I can also still right click > copy, then paste it as well, but can't seem to do either of these with Python while it is in use.

If this is a duplicate, I apologize in advance.

This is simply using the basic open or copy functions.

shutil.copy(src, dst) #For the copy procedure

openpyxl.load_workbook(file) #For just the open and read

Both of these functions basically produce the similar errors when the file is in use by another user.

Traceback (most recent call last):
File "myfile.py", line 14, in <module>
sbwb = openpyxl.load_workbook(sbfile)
File "C:\Python27\lib\site-packages\openpyxl\reader\excel.py", line 164, in 
load_workbook
archive = _validate_archive(filename)
File "C:\Python27\lib\site-packages\openpyxl\reader\excel.py", line 118, in 
_validate_archive
archive = ZipFile(filename, 'r', ZIP_DEFLATED)
File "C:\Python27\lib\zipfile.py", line 778, in __init__
self.fp = io.open(file, filemode)
IOError: [Errno 13] Permission denied: 
'file location'

UPDATE I found the issue and it wasn't related to Python at all. What was happening was that I was searching for the latest file in the folder. When the code searches in the folder, it finds the locked file before the original. I just excluded files that begin with ~$ and it found the correct one and loaded it successfully and that was using openpyxl. Thanks for everyones responses.

Actually, the dupe target here doesn't feel like an exact duplicate to me. Closer would be [this question](https://stackoverflow.com/questions/36201933/permission-denied-issue-when-trying-to-access-files-in-a-folder-with-xlrd-or-shu). (And it has a similar cause and resolution.) — John Y, May 18 '18 at 16:14

score 0 · Answer 1 · edited May 23 '17 at 11:46

I think you would have to use the library xlrd to open it, here's some code for that:

from xlrd import open_workbook

fh = open_workbook((filepath),'r')
no = 1
print ('\n')
for sheet_name in fh.sheet_names():
    print (' Sheet: ',str(no),' |  Name: ',sheet_name)
    no += 1
print ('\n\nWhich sheet would you like to read? Enter it''s number.\n\n')
while True:
    inpstr=input('    > ')
    try:
        (selected_sheet_no)=int(inpstr)
        print (('\n You have selected sheet: '),(selected_sheet_no))
        (selected_sheet_no)-=1
        break
    except:
        print ('\nInvalid input.')
        print ('\nTo go back to start enter "y".')
        continue  
s = fh.sheet_by_index(selected_sheet_no)
for row in range(s.nrows):
    col_value = []
    for col in range(s.ncols):
        value  = (s.cell(row,col).value)
        try : value = str(int(value))
        except : pass
        col_value.append(value)
    data.append(col_value)

Unfortunately I am not sure how to open a sheet by name, you will have to refer to xlrd documentation for that. My code gives a list of lists with each sublist being an entire row, each element being a cell in that row.

I am also sorry to tell you I am not sure how to write to an excel file especially if it's already open.

Apparently xlrd can open worksheets already in use, I read that here:

python library or code to read already open Excel file

Is there a way to copy or open/read a xlsx file while it is in use by another user using Python (Windows)?

1 Answers1