Why is there a permission error when repeat writing to excel file?

조회 수: 248 (최근 30일)
Udit Gupta
Udit Gupta 2016년 9월 5일
댓글: Hugues 2024년 2월 15일
I am writing results of multiple analyses to different sheets of the same excel file using "writetable" command. The first instance of execution completes successfully but any subsequent calls to write to the same file generates error.
Unable to write to file 'RegressionResults.xlsx'. You may not have write permissions or the file may be open by another application.
I am using Windows 10 Education Edition 64 bit, Matlab R2016a, and Excel 2016. I have checked using process explorer, and the only two processes accessing the excel file are matlab.exe and excel.exe (so conflicts with any antivirus application).
Can anyone suggest a solution or a workaround for the problem.
  댓글 수: 3
Jan Siroky
Jan Siroky 2017년 12월 7일
I do face the same problem (Matlab 2016b, Win10, MS Office 2013). Killing of all Excel processes does not solve the problem. The only workaround I hade found is change of the file name. Note, that file with original name does not exists. There is more that 8 GB RAM available (no memory issues) when trying to write table.
Jan Siroky
Jan Siroky 2018년 1월 18일
Update
I do face such strange behavior:
This code fails (Unable to write to file 'ERRORS2.xlsx'. Ensure the file is a valid spreadsheet file and is not password protected.)
writetable(this.errors_report, 'ERRORS2.xlsx')
however, this code writes a valid xlsx file
writetable(this.errors_report, fullfile(pwd, 'ERRORS2.xlsx'))
I do not understand this.

댓글을 달려면 로그인하십시오.

답변 (11개)

Quoc Vo
Quoc Vo 2022년 11월 4일
Hi,
I had similar issue. I figured out that it was because the files I am working on was on OneDrive and there is conflict between writing command from MATLAB and synchronizing command from OneDrive. I temporarily turned off the synchronizing function of Onedrive and it works fine.
One more thing to note is that this issue only happen when I run the script on Windows. It works fine in MacOS without turning off OneDrive.
Hope that this would help.

Tom Salomon
Tom Salomon 2018년 12월 6일
편집: Tom Salomon 2018년 12월 6일
I had this issue too, and even more bizzare. I have around 50 trials. When I tried saving the data from MATLAB to excel using writetable or xlswrite, the script failed if the trial number was larger than 9 (?!)
for i = 1:50
file_name = sprintf('trial_%02i_sess_1.xlsx',i);
writetable(table2write{i},file_name);
end
The code would run up until trial 9 and then end with an error: 'Unable to write to file.... Ensure the file is a valid spreadsheet file and is not password protected'. Changing the file name to include the path (using pwd) fixed it!
for i = 1:50
file_name = sprintf('%s/trial_%02i_sess_1.xlsx',pwd,i);
writetable(table2write{i},file_name);
end

nobrain
nobrain 2021년 1월 20일
I was running into the same issue. Writing multiple times to the same excel spreadsheet with either writematrix or writecell eventually results in a permission denied error. Some more docu would be great on this issue.
I don't understand how functions can be deployed which cannot repeatedly be called??
I can suggest two workarounds which solved the issue for me:
Either use the windows excel. Be aware that, for me, this resulted in much more execution time than before.
writematrix(header, fullFileName, "Sheet", mirror.serialnumber, "Range", "C1", 'UseExcel', true);
or close all files after a some calls to a write function.
fclose('all')
In my case, I have a function writing to a spreadsheet with multiple writematrix calls. A single call of this function works fine, calling the function multiple times on the same sheet resulted in an error. Closing alll files at the end of the function resolved the issue.
In all cases I used the full file name.
Hope this helps someone.
  댓글 수: 1
Taehoon Kee
Taehoon Kee 2023년 3월 28일
This works for me. Thanks... :-)
I tried all the solutions suggested... But, only this.... was perfect for me!

댓글을 달려면 로그인하십시오.


Phuoc Ho
Phuoc Ho 2021년 10월 2일
hey guy, i did exit that .xlxs and run code , then code havent error , you should try it

John Rogers
John Rogers 2022년 1월 18일
Setting 'UseExcel' to false fixed this problem for me:
writematrix(tempDataTableTime.TimeSpent,outputFileNameTime, 'UseExcel',false)
  댓글 수: 1
Hugues
Hugues 2024년 2월 15일
This worked perfectly for me, despite One drive or other fixes proposed above not working.

댓글을 달려면 로그인하십시오.


Andrew Thoesen
Andrew Thoesen 2019년 7월 13일
편집: Andrew Thoesen 2019년 7월 13일
I used task manager to kill all excel processes and I was able to get this problem to successfully resolve. I read somewhere else that someone reset their computer and it magically decided to work. There's no clear mechanism or cause-effect for this error, it seems like a "try everything" type deal.
Edit: Small correction. I believe it may be caused (for me at least) by attempting to write files when I have multiple scripts open that are both on path but one is not in the working directory. Use pwd and ensure that the working path is the same one you're running the write script from.
  댓글 수: 1
yojitha etikala
yojitha etikala 2021년 12월 12일
Hello,
Yes, it worked for me. closed all the scripts and ran the one I need.

댓글을 달려면 로그인하십시오.


Daniel Rubin
Daniel Rubin 2019년 9월 25일
I have encountered similar problem and I think I was able to pin-point a specific bug in the writetable function. The permission error is generated when the string with the xls filename is exactly 255 chars long. It works for lengths of 254 and 256, but not for 255. I am working with 2017b Matlab version.
  댓글 수: 1
vinyas babu
vinyas babu 2019년 12월 17일
I have an issue where in 2017a it working fine but has stopped working or to say not working in Win10 2018b...

댓글을 달려면 로그인하십시오.


NDuclaux
NDuclaux 2020년 9월 9일
Hi all,
I get the same issue with one script. In my case the file created with writetable is incremental so there are mutiple writing event.
The script work for a while and then the bug apear at some point.
Also this issue appera on specific and fast laptop.
Putting a breakpoint on this line and pressing continue, the issue doesn't occur.
THerfore I think there is an issue at least in my case where there is not enough time between 2 writing attemp to refesh the status of the file, and it's still seen as being open, while it attempt to add information into it.
Having said that, I haven't find a fix yet.
Any idea how to make sure the file is actually close before trying to write on it?

DocWalo
DocWalo 2021년 3월 29일
I don't know if I help to advance the problem but on my own experience to allow my script to write my excel I need to close all my workbook I am currently using to go through the script. If not I have the same error as describe in the orignal post.

John Draper
John Draper 2021년 12월 10일
I had the same issue. Interestingly, the error occured shortly after I switched to an open excel document. I think excel might be restricting permissions when the executable is running (i.e., when you have any .xlsx file open).
In short: closing all excel files and rerunning the script worked fine for me.
  댓글 수: 1
Image Analyst
Image Analyst 2021년 12월 12일
Almost right. You can go to Excel and look at stuff, like switch worksheets, etc. and MATLAB will still be able to write to it. I do this all the time to check how my ActiveX code is poking stuff onto the worksheets so I know for a fact it works.
However if you change anything while in Excel, Excel will restrict other apps from doing anything anymore and you'll get a "rejected by callee" error message because Excel doesn't seem to want two apps changing data at the same time. I guess it might get confused or be ambiguous as to which is the change you want: from Excel, from your App, or from both, so it just prevents you from doing anything from MATLAB to avoid conflicts.

댓글을 달려면 로그인하십시오.


Justin Curtiss
Justin Curtiss 2021년 12월 29일
I had a similar issue where I was creating a new sheet in a loop and writing it to the same excel file, 30 sheets total. Adding a pause at the bottom of the loop worked, 2 seconds. Not efficent, but solved the issue

카테고리

Help CenterFile Exchange에서 Spreadsheets에 대해 자세히 알아보기

제품

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by