How to Save And Close Excel File using actxserver?

Question

Ibro Tutic 2017-8-2

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/351328-how-to-save-and-close-excel-file-using-actxserver

编辑： Aleksei Kukin 2021-5-30

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 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Aleksei Kukin 2021-5-30

2
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/351328-how-to-save-and-close-excel-file-using-actxserver#answer_712755

编辑：Aleksei Kukin 2021-5-30

"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');".

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

Answer 2

Akhilesh Thakur 2017-8-2

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/351328-how-to-save-and-close-excel-file-using-actxserver#answer_276548

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 个评论
显示无隐藏无

Ibro Tutic 2017-8-2

编辑：Ibro Tutic 2019-4-20

在 MATLAB Online 中打开

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