Discover MakerZone

MATLAB and Simulink resources for Arduino, LEGO, and Raspberry Pi

Learn more

Discover what MATLAB® can do for your career.

Opportunities for recent engineering grads.

Apply Today

To resolve issues starting MATLAB on Mac OS X 10.10 (Yosemite) visit: http://www.mathworks.com/matlabcentral/answers/159016

ActiveX -- Saving Excel File

Asked by Katie R on 27 Sep 2012

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

Katie R

Products

3 Answers

Answer by Image Analyst on 27 Sep 2012
Accepted answer

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

Eric on 27 Sep 2012

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 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.
Katie R on 27 Sep 2012

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.

Image Analyst
Answer by Tom on 27 Sep 2012
Edited by 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

Katie R on 27 Sep 2012

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 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.

Tom
Answer by Jake on 5 Apr 2013

For opening and saving excel file try repair xlsm files

http://www.repairxlsm.xlsrepairtoolbox.com recovery of Microsoft Excel documents in all versions, support of MS Windows 98/.../7

0 Comments

Jake

Contact us