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

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.

Answer 1

[Jeremy Hughes]

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'))

[Nicholas]

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.