ActiveX -- Saving Excel File
28 views (last 30 days)
Show older comments
I'm trying to open an existing Excel file, add data, then save the file. I've tried several different variations on saving the file, but I'm not having any luck. Code chunk:
%%Open Existing File & Activate / Re-name Sheet 3
hExcel = actxserver('Excel.Application');
hWorkbook = hExcel.Workbooks;
invoke(hWorkbook,'Open',filename_ext); %%filename_ext -- existing file
Sheets = hExcel.ActiveWorkBook.Sheets;
hSheet = get(Sheets,'item',3);
hSheet.Activate;
hSheet.Name = 'CYCLE';
%%Add Data & Formatting
%%<etc>
%%Save File & Close
% hWorkbook.SaveAs(filename_ext); %%V1
% hWorkbook.Saved = 1;
% hWorkbook.Save %%V2
% invoke(hWorkbook,'Save',filename_ext); %%V3
hWorkbook.Close
hExcel.Quit
hExcel.delete
The differet save methods above give a variety of different error outputs.
* hWorkbook.SaveAs(filename_ext);
Cannot find an exact (case-sensitive) match for 'SaveAs'
The closest match is: saveas
in C:\Program Files\MATLAB\R2012a\toolbox\matlab\general\saveas.m
* hWorkbook.Save
No appropriate method, property, or field Save for class
Interface.000208DB_0000_0000_C000_000000000046.
* invoke(hWorkbook,'Save',filename_ext);
Error using Interface.000208D8_0000_0000_C000_000000000046/invoke
Invoke Error: Unknown name or named argument
In another code section I've used the above SaveAs format (when I'm creating a new file--not adding data to an existing file)without issue, so I'm uncertain on how to correct the issue.
Any help would be appreciated.
0 Comments
Accepted Answer
Image Analyst
on 27 Sep 2012
hWorkbook = hExcel.Workbooks is collection of all the workbooks that you have open. To save one, you need the handle to one particular single workbook. Try using hWorkbook = hExcel.ActiveWorkbook instead. This will get the handle to just one workbook - the currently active one - instead of the whole collection of them. I know you may have only one open, but it's the hierarchy that's important. It's not going to automatically make hExcel.Workbooks equal to hExcel.ActiveWorkbook just because you have only one open.
4 Comments
Image Analyst
on 27 Sep 2012
Yes, excellent point Eric. You can set via the Excel.visible property.
Excel.visible = false; % or true;
There is also another useful property called Excel.DisplayAlerts which is useful. You can set it to false to get it to blow by obvious warnings:
Excel.DisplayAlerts = false; % Suppress Excel warning popups, like for overwriting a file.
More Answers (1)
Tom
on 27 Sep 2012
Edited: Tom
on 27 Sep 2012
Does this give any errors or warnings?
FileName='Test.xlsx';
%open Excel
e = actxserver('Excel.Application');
% Workbook
eWorkbook = e.Workbooks.Add;
% Make the first sheet active
eSheets = e.ActiveWorkbook.Sheets;
eSheet1 = eSheets.get('Item', 1);
%test data
Range = eSheet1.get('Range','A1:A1');
Range.Value='TEST';
% Save the workbook
eWorkbook.SaveAs(FileName);
%open:
e.Visible = 1;
2 Comments
Image Analyst
on 27 Sep 2012
Edited: Image Analyst
on 27 Sep 2012
Katie, please see my answer. Note that here (above) your handle is to just one workbook, not the whole collection of workbooks, that's why it works.
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Products
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!