MATLAB Answers

How to Save And Close Excel File using actxserver?

105 views (last 30 days)
Ibro Tutic
Ibro Tutic on 2 Aug 2017
Edited: Ibro Tutic on 20 Apr 2019
I am writing some lines of data to an Excel file and I can't seem to find a way to save and close the file, the rest of the code works. I can see the Excel file and I see that the data is written, but the file is not saving and closing, giving me the error
Error using Interface.000208DB_0000_0000_C000_000000000046/Item
Error in hub>pushbutton13_Callback (line 323)
h.workbooks.Item('C:\Users\me\export.xlsm').Save;
This is my code
h = actxserver('Excel.Application');
h.workbooks.Open('C:\Users\me\export.xlsm',0,true); %file name must also have path
h.Visible = 1
nCols = h.ActiveSheet.UsedRange.Columns.Count
nRows = h.ActiveSheet.UsedRange.Rows.Count
hSheets = h.ActiveWorkbook.Sheets;
hSheet1 = hSheets.get('Item',1);
range1a = ['A' num2str(nRows+1) ':' 'L' num2str(nRows+1)];
range1b = ['M' num2str(nRows+1) ':' 'M' num2str(nRows+1)];
range1c = ['N' num2str(nRows+1) ':' 'AM' num2str(nRows+1)];
range2a = ['A' num2str(nRows+2) ':' 'L' num2str(nRows+2)];
range2b = ['M' num2str(nRows+2) ':' 'M' num2str(nRows+2)];
range2c = ['N' num2str(nRows+2) ':' 'AM' num2str(nRows+2)];
hActivesheetRange = get(h.ActiveSheet, 'Range', range1a);
hActivesheetRange.Value = metadata;
hActivesheetRange = get(h.ActiveSheet, 'Range', range1b);
hActivesheetRange.Value = '1.0';
hActivesheetRange = get(h.ActiveSheet, 'Range', range1c);
hActivesheetRange.Value = bsfc(1,:);
hActivesheetRange = get(h.ActiveSheet, 'Range', range2a);
hActivesheetRange.Value = metadata;
hActivesheetRange = get(h.ActiveSheet, 'Range', range2b);
hActivesheetRange.Value = '2.0';
hActivesheetRange = get(h.ActiveSheet, 'Range', range2c);
hActivesheetRange.Value = bsfc(2,:);
h.workbooks.Item('C:\Users\me\export.xlsm').Save;
h.workbooks.Item('C:\Users\me\export.xlsm').Close;

  0 Comments

Sign in to comment.

Answers (1)

Akhilesh Thakur
Akhilesh Thakur on 2 Aug 2017
ExObj = actxserver ( 'Excel.Application' ); % Start Excel
> ExObj.Visible = 1; % Make it visible
> AllBooksObj = ExObj.Workbooks; % No idea what this does, but it's required
> WkBkObj = AllBooksObj.Open( 'C:\MyDir\MyWorkbook.xlsx'); Open workbook
> AllSheetsObj = WkBkObj.Sheets; % Object containing all the sheets
> NumSheets = AllSheetsObj.Count; % Get the number of sheets in workbook
> SheetObj = get( AllSheetsObj, 'Item', n ); % Get sheet #n
> SheetObj.Delete; % Delete the sheet
> RngObj = SheetObj.Range('B2:C9') % object pointing to a range
> RngObj.Font.Bold = true; % Make the contents in the range bold
> RngObj.Interior.ThemeColor = 5; % Use color theme #5 (5th column in color table)
> RngObj.Interior.TintAndShade = 0.6; % Set brightness to 0.6 (valid = -1.0 to 1.0)
> RngObj.HorizontalAlignment = -4152; % Right justify the contents
> RngObj.cells.EntireColumn.ColumnWidth = 10; % Set column width to 10.
> RngObj.cells.EntireColumn.AutoFit(); % Autofit column to contents
> RngObj.cells.EntireColumn.NumberFormat = '0.00E+00'; % Use scientific notation
> WkBkObj.Save; % Save workbook
> WkBkObj.Close( false ); % Close workbook (false = no prompt?)
> ExObj.Quit % Quit Excel
> ExObj.delete % Not sure what this does. ExObj still exists afterward

  2 Comments

Ibro Tutic
Ibro Tutic on 2 Aug 2017
Now I am getting an error with the save command.
Undefined function 'Save' for input arguments of type 'COM.Excel_Application'.
Error in hub>pushbutton13_Callback (line 325)
ExObj.Save;
No longer getting the below error, missed a % before the comment.
I'm getting an error with
WkBkObj = AllBooksObj.Open('C:\Users\me\export.xlsm'); Open workbook
The Error:
Cannot find an exact (case-sensitive) match for 'Open'
The closest match is: open in C:\Program Files\MATLAB\R2015b\toolbox\matlab\general\open.m
Error in hub>pushbutton13_Callback (line 301)
WkBkObj = AllBooksObj.Open('C:\Users\me\export.xlsm'); Open
workbook
Akhilesh Thakur
Akhilesh Thakur on 4 Aug 2017
Why is it referring to Open it should be open.m right?

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!

Translated by