How to Save And Close Excel File using actxserver?

55 ビュー (過去 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 件)

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

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 件のコメント
Ibro Tutic
Ibro Tutic 2017 年 8 月 2 日
編集済み: Ibro Tutic 2019 年 4 月 20 日
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 2017 年 8 月 4 日
Why is it referring to Open it should be open.m right?

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

カテゴリ

Help Center および File ExchangeSpreadsheets についてさらに検索

Community Treasure Hunt

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

Start Hunting!

Translated by