Why using actxserver will turn my file to read-only

Hey guys,

Read-only error been showed while I was trying to movefile, but the new directories is either my Downloads folder or my Desktop folder.

Here is what I did:

I first loaded the Excel file that I'd like to modify, and then turn on the actxserver('Excel.Application'), and then apply action for that pre-loaded excel file.

But I got error while I was trying to movefile(oldfilePath, newfilePath): "Use the 'f' option to override". This error does not make sense since neither the new location I have selected shall not be read-only space,nor there is no duplicate file existed in that directory.

I think somehow there is a delay happened between Excel and MATLAB, in another word, I feel the modifies Excel file was not saved successfully.

Does anyone has ever met this problem before?

[Walter Roberson]

I am not clear as to what is being moved? Have you told Excel to save the modified version of the file to somewhere that did not exist, and then you are trying to move the new file to somewhere else? If so then is there a reason not to just have Excel save it to the desired destination directly?

[Kat Lee]

When creating Excel file, I am building this new Excel file by adding new sheets into one template Excel file (already existed in one particular directory) that has pre-defined formats and contents.

So this new Excel file will be saved on that template Excel file directory first, and then movefile to the new directory that user has selected.

[Guillaume]

Showing us the code you use would be much better than some hazy description of what you're doing.

Note that if a file is open in excel (via actxserver or directly) you will not be able to move the file until it is closed. The OS does not allow moving open files. Nothing to do with matlab.

[Kat Lee]

I have added code

Plus, I have tried to copyfile to the user-selected path instead of movefile, failed same the reason:

Error using copyfile

Cannot write to destination: C:\Users\xxx\Downloads\user_def_ExcelName.xlsx. Use the 'f' option to override.

[Guillaume]

Kat Lee's comment mistakenly posted as an Answer moved here

Here is the code:

funtion exportExcelFile(fullFileName, templateExcel,funcPath)

[filePath,fileName] = fileparts(fullFileName);
sheet = 1;
ext = '.xlsx';
% Copy template
copyFullFile = fullfile(funcPath,[fileName ext]);
copyfile(templateExcel,copyFullFile)
% Store Row Names
cRowNames = templateExcel.Properties.RowNames;
xlswrite(copyFullFile,cRowNames,sheet,'A2');
% Store Table Contents
cData = table2cell(targetTable);
xlswrite(copyFullFile,cData,sheet,'B2');
% Open Excel as a COM Automation server
Excel = actxserver('Excel.Application');
% Open Excel workbook
Workbook = Excel.Workbooks.Open(copyFullFile);
% Clear empty contents/format
Workbook.ActiveSheet.Range(sExcelClearRange).Clear    
% Now save/close/quit/delete
Workbook.Save;
Excel.Workbook.Close;
invoke(Excel, 'Quit');
delete(Excel);

%%%%%%%%%%%%%%%%FINAL STEP: moveFile%%%%%%%%%%%%%%%%%%
%% Move to Specified Path
moveFullFile = fullfile(filePath,[fileName ext]);
movefile(copyFullFile,moveFullFile); 

end

[Walter Roberson]

Why not use a SaveAs instead of a Save, placing the file directly in the desired location ?

It is possible (and likely) that the Excel Quit process is done asynchronously and takes time.

Answer 1

[Guillaume]

The line

cRowNames = templateExcel.Properties.RowNames;

would imply that templateExcel is a matlab table. Yet on the previous line, you have:

copyfile(templateExcel,copyFullFile)

which, if templateExcel is indeed a table is meaningless.

Later on in your code, you use the variable targetTable which is undefined. So this will also cause an error. I suspect that that targetTable is meant to be templateExcel but it's hard to know since the code is not commented.

Note that it's much easier to write a table to an excel file directly with writetable rather than converting to a cell array and then using xlswrite.

Later on you use the variable sExcelClearRange which is also undefined. In any case, relying on the ActiveSheet being the correct sheet after you've just opened the file in excel is very iffy. You may very well clear a different sheet than intended.

Also,

Excel.Workbook.Close;

is not valid. I suspect you meant

Workbook.Close;

which could be combined with the previous Workbook.Save line to just:

workbook.Close(true);  %true to save and close the file all at once.

Finally,

invoke(Excel, 'Quit');

is simply

Excel.Quit;

[Kat Lee]

Thanks for pointing out, I have changed some variable names inside the copied code.

I think I have figured out the root causing this problem, it's caused by how MATLAB communicate with Microsoft. If you guys look at the directory I am trying to save my file into, they are "Downloads" and "Desktop", they are the directories that protected by Microsoft.

This is a problem that MathWorks faculties shall be able to answer when they added actxserver this command into MATLAB

Thanks for everybody's help

[Guillaume]

actxserver is in some way very simple. It just connects you to a COM process (in this case, Excel). All the clever stuff is implemented in Windows directly and the only thing matlab does is relay your instructions to Windows. So, if there is a problem it's nothing that matlab can do anything about. In this particular case, the problem would be with Excel.

However, I'm not convinced at all that you've found the problem. There is nothing special about the Download and Desktop directories. Windows does not put any extra protection on them. You may get extra protections on some files downloaded from the internet but that's independent of where they reside.

If the code you've posted is not the code you're using, that makes it difficult to identify problems, so please post the exact code you're using.