ActiveX -- Saving Excel File

2012 9 27
2 个回答
回答已采纳
12 次查看（30 天）

0 个投票

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.

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

 采纳的回答

Image Analyst
Image Analyst 2012-9-27

0 个投票

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

Katie R
Katie R 2012-9-27
Many thanks for the help. Making use of ActiveWorkbook solved the problem. I'm doing code development without any other workbooks open, but realisitically there isn't a guarantee that that will be the case when the application is in use. I never really thought about the potential hierarchy problems since it worked ok when adding a new workbook (but thinking about it logically, creation of a new workbook likely automatically makes it the active one without doing so explicitly).
This is my first foray into using ActiveX vs. xlswrite for data output, and it definately seems to take some working with it to discover all the tricks.
Eric
Eric 2012-9-27
One caveat about using ActiveWorkbook: If you ever make the Excel application visible, the user may change the active workbook.
I believe you are better off using something like
hWB = hExcel.Workbooks.Open(filename_ext);
to get the workbook object of interest. This isn't a problem for the code you've shown since you don't make Excel visible, but this has bitten me in the past.
-Eric
Image Analyst
Image Analyst 2012-9-27
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.
Katie R
Katie R 2012-9-27
I actually tried using
hWB = hExcel.Workbooks.Open(filename_ext);
but had some trouble with it originally, which is why I went to the invoke statement.
I'm actually currently using hExcel.visible = true at the moment for debugging purposes, but the intention is that it will be set to false before I actually compile up the end results for distribution. Thanks for the DisplayAlerts tip...I'll probably end up making use of that one.

请先登录，再进行评论。

更多回答（1 个）

Tom
Tom 2012-9-27
编辑：Tom 2012-9-27

0 个投票

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

Katie R
Katie R 2012-9-27
Nope, no errors with that. I'm actually already using basically that code under another condition in conjuction with the code I'm having issues with.
hExcel = actxserver('Excel.Application');
hWorkbook = hExcel.Workbooks.Add;
hSheet = hWorkbook.Sheets.get('Item',3);
hSheet.Activate;
hSheet.Name = 'CYCLE';
%%Add Data & Formatting
%%<etc>
DrivingSim_DataSave(handles,1,hExcel,hWorkbook);
hWorkbook.SaveAs(filename_ext);
hWorkbook.Saved = 1;
hWorkbook.Close
hExcel.Quit
hExcel.delete
This works as I expect it to when I'm creating a new file, instead of trying to add to an existing one.
Image Analyst
Image Analyst 2012-9-27
编辑：Image Analyst 2012-9-27
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.

请先登录，再进行评论。

类别

在 帮助中心 和 File Exchange 中查找有关 Spreadsheets 的更多信息

产品

标签

Community Treasure Hunt

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

Start Hunting!

Translated by