Write data to excel and change sheet name using ActiveX--Matlab

Question

I need to export data to excel sheet with a specific name using Activex. Here is what I did: First i export the data to excel in first for loop and then modify the names of the sheet using second for loop. Can it be done even in one single for loop? I think there should be another better idea. Note: The size of the data varies.

try
    filename = fullfile(pwd,'example.xlsx');
    for i=1:5
        xlswrite(filename,[1 2;3 4]*i,i);
    end
    for i = 1:5
        myExcel = actxserver('Excel.Application');
        excelWorkBook = myExcel.Workbooks.Open(filename,0,false);
        excelWorkBook.Worksheets.Item(i).Name = ['new_sheet_' num2str(i)]; 
        excelWorkBook.Save;
        excelWorkBook.Close;
        myExcel.Quit;
    end

catch
    % Disp Error message.....
end

Fratyx · Accepted Answer · 2014-10-10 09:05:05Z

4

The xlswrite function also accepts a string as sheet parameter. So just replace your call by this one:

xlswrite(filename,[1 2;3 4]*i,['new_sheet_' num2str(i)]);

answered Oct 10, 2014 at 9:05

Fratyx

5,8271 gold badge16 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

danny · Accepted Answer · 2014-10-10 09:57:57Z

1

Fratyx's answer is the simplest to implement, but a bit of knowledge of the Excel Object Model goes a long way, so here's how to implement your solution with ActiveX in one loop:

     e = actxserver('excel.application');
     w = e.Application.Workbooks.Add();
     numnewsheets = 4;
     for i = 1:numnewsheets
         w.Worksheets.Add([],w.Sheets.Item(w.Sheets.Count),1);
         w.Worksheets.Item(i).Name = ['new_sheet_' num2str(i)];
         w.Worksheets.Item(i).Cells.Range('A1:B2').Value = [1 2;3 4];
     end
     w.SaveAs(filename)
     w.Close
     e.Quit
     e.delete

To calculate the range required for the size of your data, you will have to open file "xlswrite" and copy out the sub-function "calcrange".

edited Oct 10, 2014 at 9:57

answered Oct 10, 2014 at 9:20

danny

3061 silver badge6 bronze badges

1 Comment

nik Over a year ago

The range and the number of sheets are fixed in this case. How can I make it flexible. The size of the data varies.

Collectives™ on Stack Overflow

Write data to excel and change sheet name using ActiveX--Matlab

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related