MATLAB Answers

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

197 views (last 30 days)
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
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 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:
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);
And certainly don't use double as a variable name!
Monty on 6 Apr 2018
xlApp = actxserver('Excel.Application');
xlWorkbook =xlApp.workbooks.Open(fullfile([PathName,FileName]));
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 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 :: )
% 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 ::

Community Treasure Hunt

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

Start Hunting!