MATLAB Answers

How to Save And Close Excel File using actxserver?

100 ビュー (過去 30 日間)
Ibro Tutic
Ibro Tutic 2017 年 8 月 2 日
編集済み: 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;

回答 (2 件)

Akhilesh Thakur
Akhilesh Thakur 2017 年 8 月 2 日
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 件のコメント
Akhilesh Thakur
Akhilesh Thakur 2017 年 8 月 4 日
Why is it referring to Open it should be open.m right?

サインインしてコメントする。


Aleksei Kukin
Aleksei Kukin 2021 年 5 月 30 日
編集済み: 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');".

Community Treasure Hunt

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

Start Hunting!

Translated by