Writing part of table to an Excel file with variable sheet name

Question

I am writing a code that I need to have output as an Excel file. Because I need to save some parts of table into different Excel files then my output is created inside a for loop. Something like this:

for j=1:5
    Table = table(ColAA(:,j),ColBB(:,j),ColCC(:,3*j-2:3*j),ColWW(:,j));
    filename_excel = [ name '_N' num2str(j) '_Seg' num2str(j) '.xlsx'];
    writetable(Table,filename_excel,'Sheet', j, 'Range','A5');
end

The output table is:

Table = 

        Var1    Var2    Var3    Var4 
        ____    ____    ____    ____

         38      71      176     124
         43      69      163     109
         38      64      131     125
         40      67      133     117
         49      64      119     122

where my desired table is:

Table = 

        ColAA   ColBB   ColCC   ColWW 
        ____    ____    ____    ____

         38      71      176     124
         43      69      163     109
         38      64      131     125
         40      67      133     117
         49      64      119     122

I have two questions regarding above code:

1. The name of the columns are not going to be the original name and it is just because some parts of table are needed. How could I have the original name in output,too?

2. The difficult part for me is that I need to save each sheet inside a loop with the different name and with a different indices. How could be done this inside that for loop?

Answer 1

Edit, error with changing name of sheet4 or 5 (solved)

When changing the order of commands in the for loop as followed the code generates the right files and renames the right sheets. It was only a problem of coding order. I also realized that you do not have to preinitialize the excel files. The problem was that I first tried to change the sheet names, yet the sheets hadn't been created, so the error was "index out of dimension". By saving the table first and changing the sheet names later on, everything works.

for k=1:5
    Table = table(ColAA(:,k),ColBB(:,k),ColCC(:,k),ColWW(:,k),'VariableNames',Names);
    filename_excel = [ 'name_N' num2str(k) '_Seg' num2str(k) '.xls'];
    writetable(Table,filename_excel,'Sheet', k, 'Range','A5');    
    e = actxserver('Excel.Application'); % # open Activex server
    ewb = e.Workbooks.Open(['C:\Users\common\Documents\MATLAB\',filename_excel]); % # open file (enter full path!)
    ewb.Worksheets.Item(k).Name = Sheet_names{k}; % # rename 1st sheet
    ewb.Save % # save to the same file
    ewb.Close(false)
    e.Quit         
    result1{k}= Table;
    result2{k}=filename_excel;
end

first solution below (still containing the error)

ColAA= rand(100,5);
ColBB= rand(100,5)*2;
ColCC= rand(100,5)*5;
ColWW= rand(100,5)*10;
result1=cell(1,5);
result2=cell(1,5);
Names= {'ColAA', 'ColBB', 'ColCC', 'ColWW'};
Sheet_names={'test1', 'test2', 'test3', 'test4', 'test5'};
for k=1:5
    Table = table(ColAA(:,k),ColBB(:,k),ColCC(:,k),ColWW(:,k),'VariableNames',Names);
    filename_excel = [ 'name_N' num2str(k) '_Seg' num2str(k) '.xls'];
    xlswrite(filename_excel,1)% # create test file
    e = actxserver('Excel.Application'); % # open Activex server
    ewb = e.Workbooks.Open(['C:\Users\common\Documents\MATLAB\',filename_excel]); % # open file (enter full path!)
    ewb.Worksheets.Item(k).Name = Sheet_names{k}; % # rename 1st sheet
    ewb.Save % # save to the same file
    ewb.Close(false)
    e.Quit   
    writetable(Table,filename_excel,'Sheet', k, 'Range','A5');
    result1{k}= Table;
    result2{k}=filename_excel;
end

First I generate my data (just random numbers). My two result arrays are just for checking if the code works. So you can just delete those lines. Then Names contain my column names which you want as table names later. Sheet_names contain the names I want to give the sheets.

Then I added the lines from yuk's answer to this question. To change the sheet names. Therefor I had to generate those excel files first.
Take into account that I had to change your indexing of ColCC(:,3*j-2:3*j) because it didn't make sense (see below) and that I changed the filename a bit, because I didn't have a Name variable

Previously written in Comments (deleted for readibility)

Also there should be a mistake in your index by ColCC. 3*j-2:3*j results in (1,0), (2,1,0), (5,4,3,2,1,0) and so on. First of all Index 0 is not allowed and secondly you have each time more entries...

And try to NOT use i,j as variable names. They are also used for complex numbers. Better use another letter or ii,jj!