Repeat writing to Excel workbook on OneDrive causes "You may not have write permissions or the file may be open by another application" error.

31 次查看（过去 30 天）
Nicholas
Nicholas 2023-2-8
评论： Nicholas 2023-2-8
I have a directory with text files that I am importing into MATLAB, doing analysis with in MATLAB, and then want to export the analysis into a multi-sheet excel workbook at the end. The bit of code I am using to write the data structure from MATLAB to an Excel book in OneDrive is below.
txtFil=dir('*.txt');
numFiles=numel(txtFil);
for k=1:numFiles
fileName=convertCharsToStrings(txtFil(k).name);
fprintf('Writing %s...\n',fileName)
writetable(BigDat{k},'combined.xlsx','Sheet',fileName,'UseExcel',true,...
'AutoFitWidth',false)
end
When I run this code in the OneDrive directory, I get the error:
"Unable to write to file 'C:\Users\Me\OneDrive\ProjectFolder\Topic\combined.xlsx' You may not have write permissions or the file may be open by another application"
I do not have the file open and neither does anyone else. If I delete the book, the first file and sheet writes sucessfully, but fails on the second with the same error.
When i duplicate the directory on my local Windows desktop, no such error occurs. I can pause OneDrive syncing, which also "solves" the issue. While this is a temproary workaround, this is not an acceptable solution in the long run.
Hoping others have some ideas as to what can be done to fix this, if possible.

请先登录，再回答此问题。

采纳的回答

Jeremy Hughes
Jeremy Hughes 2023-2-8
编辑：Jeremy Hughes 2023-2-8
One Drive is syncing files to the cloud when they are updated--it locks those files when that happens so you can't write to it for a short time. It's not a great idea to write to a file in a loop in One Drive. This is why disabling syncing resolves the issue.
I suggest writing to a temporary location, then moving that file when you're done processing.
txtFil = dir('*.txt');
numFiles = numel(txtFil);
tempFileName = fullfile(tempdir,'combined.xlsx');
for k=1:numFiles
fileName = convertCharsToStrings(txtFil(k).name);
fprintf('Writing %s...\n',fileName)
writetable(BigDat{k},tempFileName,...
'Sheet',fileName,...
'UseExcel',true,...
'AutoFitWidth',false)
end
movefile(tempFileName,fullfile(oneDriveLocation,'combined.xlsx'))
  1 个评论
Nicholas
Nicholas 2023-2-8
Thank you, that feels a little better to implement than either of the workarounds I found.
Also noted about writing to files in loops in OneDrive and syncing issues.

请先登录，再进行评论。

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 File Operations 的更多信息

标签

产品


版本

R2022b

Community Treasure Hunt

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

Start Hunting!

Translated by