Changing the name of the sheet in excel

I want to change the names of sheets in excel and then i need to perform the write operation on those sheets?

Is it possible to do with xlswrite?

Answer 1

[Thomas Koelen]

You can use ActiveX directly from MATLAB:

xlswrite('test.xls',1) % # create test file

e = actxserver('Excel.Application'); % # open Activex server
ewb = e.Workbooks.Open('c:\test\test.xls'); % # open file (enter full path!)
ewb.Worksheets.Item(1).Name = 'new name'; % # rename 1st sheet
ewb.Save % # save to the same file
ewb.Close(false)
e.Quit

Be careful while testing, it overwrite the original file. Make a backup.

[R.Soerens]

I was hoping there would be a different method then going through the ActiveX, but I can always write a little function to do this.

Thank you.

Further investigation shows the Matlab function "writetable" is the culprit creating the three worksheets by default.

[Enri]

As far as I tried, Matlab function "writetable"behaves like this:

1. if you don't indicate other parameters for the property 'sheet', it creates just the sheet in which you want to write your table:

    % Example: write four tables in a specific file
    fileName = 'dunno.xlsx';
    folderName = 'someFolder';
    filePath = [folderName, '/', fileName];
    writetable(tableName_1, filePath, 'sheet', 1);
    writetable(tableName_2, filePath, 'sheet', 2);
    writeTable(tableName_3, filePath, 'sheet', 3);
    writeTable(tableName_4, filePath, 'sheet', 4);

it creates just four sheets by default, with one of the tables for each one.

2. if you indicate another sheets, with a specific name, it creates three sheets by default, and then it creates the sheets you've asked for:

        % Example: write four tables in a specific file
    fileName = 'dunno.xlsx';
    folderName = 'someFolder';
    filePath = [folderName, '/', fileName];
    % Initialize tables
    tables.tableName_1 = tableName_1;
    tables.tableName_2 = tableName_2;
    tables.tableName_3 = tableName_3;
    tables.tableName_4 = tableName_4;
    tabNames = fieldnames(tables);
    % Initialize the 
    sheets.sheetName_1 = 'sheetName_1';
    sheets.sheetName_2 = 'sheetName_2';
    sheets.sheetName_3 = 'sheetName_3';
    sheets.sheetName_4 = 'sheetName_4';
    sNames = fieldnames(sheets);
    
    for i = 1:4
        writetable(tables.(tabNames{i}), filePath ...
                   'sheets', sheets.(sNames{i}) );          
    end 
    

Funny thing, I have an Italian excel, so with the second code, it creates:

"Foglio 1", "Sheet 2", "Sheet 3" and of course sheetNames_(1,2,3,4)...

[Rami]

I am getting this error

> ewb = e.Workbooks.Open('\test.xls');
Error using Interface.000208DB_0000_0000_C000_000000000046/Open
Invoke Error, Dispatch Exception:
Source: Microsoft Excel
Description: Sorry, we couldn't find \test.xls. Is it possible it was moved, renamed or deleted?
Help File: xlmain11.chm
Help Context ID: 0

[Zou Wenbin]

> ewb = e.Workbooks.Open('\test.xls');

Here, you should enter the fullpath of the xls file, eg:

> ewb = e.Workbooks.Open('C:\test.xls');

[Laura Stewart]

Be warned if using @Enri solution it will populate the data to the Excel File as text.

This was problematic as I was exporting a matrix of cells with Excel functions (so the Excel user could open the file and easily track and modify the cell values if needed) and once the data was in Excel the cells had to be modified to a number type, then clicked & modified (space added then deleted), and entered one-by-one to show the function value.

[qing sheng]

Hi! I am new to coding, when I using it in a for loop, it broke and warned me error code 0x800A01A8. So could you please find me a solution?

Answer 2

[Ilias Patsiaouras]

Is this code portable for linux and Windows cause I think linux doesnt have ActiveX