MATLAB Answers

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

365 views (last 30 days)
Keilan
Keilan on 9 Dec 2014
Edited: Walter Roberson on 13 Apr 2020
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

  1 Comment

matt dash
matt dash on 9 Dec 2014
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.

Sign in to comment.

Accepted Answer

Guillaume
Guillaume on 9 Dec 2014
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!

  4 Comments

Show 1 older comment
Image Analyst
Image Analyst on 27 Sep 2016
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
Monty on 6 Apr 2018
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.

Sign in to comment.

More Answers (2)

Image Analyst
Image Analyst on 10 Dec 2014
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.

  1 Comment

Keilan
Keilan on 18 Dec 2014
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!

Sign in to comment.


Pruthvi G
Pruthvi G on 13 Apr 2020
%%**********************************************************************************************************
% 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'})

  0 Comments

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!