Excel files: how to open, edit, and then close file without causing file to become "Read-Only" in MATLAB

Hi all,

I am having difficulties with editing an Excel file in MATLAB. My ultimate goal is to edit the formatting in the file, particularly the cell borders. When I run this script, it creates the borders that I want, but the Excel file becomes "Read-Only" and I cannot save the changes I've made. I am not sure what is causing the file to become read-only. Any help or suggestions would be greatly appreciated.

Thanks, K

% Link to Excel
Excel = actxserver('Excel.Application');
Excel.Workbooks.Open('C:\Users\Keilan\Documents\MATLAB Expirements\2014\MATLAB to Excel test.xlsm');

double = get(Excel.ActiveWorkBook.Sheets,'Item',3);  % Want to edit 3rd sheet in workbook

% Number associated with each border: left, right, top, bottom
lt = 1;
rt = 2;
% tp = 3;
bt = 4;

% Add in the periodic borders required
for kk = 1:ct
    % Row numbers of interest
    rn1 = 1 + 3*kk;    % R1, A(rn1):E(rn2).  R2, BG(rn1):BK(rn2)
    rn2 = 3 + 3*kk;    % R3, A(rn2):BK(rn6)

      % Ranges of interest
      R1 = sprintf('A%d:E%d',rn1,rn2); %'A4:E6';
      R2 = sprintf('BG%d:BK%d',rn1,rn2); % 'BG4:BK6';
      R3 = sprintf('A%d:BK%d',rn2,rn2); % 'A6:BK6';
      Range1 = Excel.Range(R1);
      Range2 = Excel.Range(R2);
      Range3 = Excel.Range(R3);

      % Create solid borders in desired locations
      set(get((Range1.borders),'item',lt),'linestyle',1);
      set(get((Range1.borders),'item',rt),'linestyle',1);
      set(get((Range2.borders),'item',rt),'linestyle',1);
      set(get((Range3.borders),'item',bt),'linestyle',1);
  end

Excel.Visible = 1; % Open the Excel spreadsheet
% Excel.ActiveWorkbook.Save;    % Tried this, didn't work either
delete(Excel);      % Close the activex server

[matt dash]

I have 0 experience with the excel activexserver, but quick idea: Do you also have the workbook open in an actual Excel instance? I think that would lock it.

Answer 1

[Guillaume]

If you don't save the workbook and don't quit excel, the workbook will remain open in excel and be left read-only by excel, thus before closing the activex server:

Excel.ActiveWorkbook.Save;
Excel.Quit;

Side Note: Rather than using ActiveWorkbook, I would use the workbook object returned by the Workbooks.Open method:

workboox = Excel.Workbooks.Open('C:\Users\Keilan\Documents\MATLAB Expirements\2014\MATLAB to Excel test.xlsm');
sheet = workbook.Sheets.Item(3); 
%...
workbook.Save;
Excel.Quit;

And certainly don't use double as a variable name!

[Keilan]

Thank you for your help and advice, I really appreciate it!

K

[daniel]

Does not kill the process though. Excel still running in the background. How to kill the process??

[Image Analyst]

Try this snippet:

[taskstate, taskmsg] = system('tasklist|findstr "EXCEL.EXE"');
if ~isempty(taskmsg)
  status = system('taskkill /F /IM EXCEL.EXE');
  % If it gets here, shutting down worked, (I think) unless it asked them to save any unchanged work and they said to cancel shutdown.
end

[Monty]

Try

xlApp = actxserver('Excel.Application');
xlWorkbook =xlApp.workbooks.Open(fullfile([PathName,FileName]));
xlWorkbook.Save;
xlWorkbook.Close;
xlApp.Quit;
xlApp.delete;

Using delete property closes the com for me.

Answer 2

[Image Analyst]

Look at my attached Excel class. See the methods in there like FormatRightBorder(). (Sorry, it's a work in progress and there are a few things done in different ways, but it works.)

Also, see my attached Excel demo - it doesn't make the file read only.

[Keilan]

Thank you for your help and for passing along your Excel class files. I have not read them cover to cover yet but thus far they look very interesting and useful. I appreciate it!

Answer 3

[Pruthvi G]

Download Link :: https://in.mathworks.com/matlabcentral/fileexchange/71329-delete-excel-workbook-sheets-sheet-1-delete-using-matlab

%%**********************************************************************************************************
%   Name          : Delete_sheets_Excel
%   Author        : Pruthvi Raj G  ::  (9677066394 :: www.prudhvy.com )
%   Version       : Version 1.0 - 2011b Compactible
%   Description   : Deleting Excel Sheets required after writing data to Excel. 
%   Input         : File_Name with path included , Sheet_name / Sheet_names.
%   Date          : 22-April-2019
%  
%   Examples      : Delete_sheets_Excel('D:\Pruthvi\Test_file.xls',{'Sheet1','Sheet2'})  %To delete 2 sheets
%                   Delete_sheets_Excel('D:\Pruthvi\Test_file.xls','Sheet1')
%                   Delete_sheets_Excel('D:\Pruthvi\Test_file.xls') % Takes 'Sheet1' as Default
%************************************************************************************************************

Use the Below Lines of Code ::

Delete_sheets_Excel('D:\Pruthvi\Test_file.xls',{'Sheet1','Sheet2'})