How to Save And Close Excel File using actxserver?

88 visualizaciones (últimos 30 días)
Ibro Tutic
Ibro Tutic el 2 de Ag. de 2017
Editada: Aleksei Kukin el 30 de Mayo de 2021
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;

Respuestas (2)

Aleksei Kukin
Aleksei Kukin el 30 de Mayo de 2021
Editada: Aleksei Kukin el 30 de Mayo de 2021
"true" in line "h.workbooks.Open('C:\Users\me\export.xlsm',0,true);" mean that this book was opened as read-only.
so you need just change it to "h.workbooks.Open('C:\Users\me\export.xlsm',0,false);"
and save by "h.ActiveWorkbook.Save;"
or just change name of workbook when you'll be saving it, for example "h.ActiveWorkbook.SaveAs('C:\Users\me\export_new.xlsm');".

Akhilesh Thakur
Akhilesh Thakur el 2 de Ag. de 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 comentarios
Ibro Tutic
Ibro Tutic el 2 de Ag. de 2017
Editada: Ibro Tutic el 20 de Abr. de 2019
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 el 4 de Ag. de 2017
Why is it referring to Open it should be open.m right?

Iniciar sesión para comentar.

Categorías

Más información sobre Use COM Objects in MATLAB en Help Center y File Exchange.

Community Treasure Hunt

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

Start Hunting!

Translated by