Renaming multiple excel sheets

Hi there,

I am really struggling to get the sheet names in my code to be renamed.

I have different folders that hold different partcipants data, then within those folders I have three folders that hold different sensor data which contain a variety of activity data captured by these sensors.

I have managed to time synchronise the data for the three different sensors and I am then wanting to write this time synchronised data into an excel file. I have used the below code to write the new time synchronised data for the three trials carried out by the three differnet sensors into the excel file and into the new folder ''TimeSyncData''.

%%  Create a folder where the data will be saved
OutputFolderName =  'TimeSyncData';  
if ~exist(OutputFolderName, 'dir')
    mkdir(OutputFolderName);
end     
cd(OutputFolderName)

%creating an excel sheet with the new time synchronised data contained,
%different sheets are the different trials
for i = 1:sz
    T = struct2table(sync(i));
    writetable(T,strcat(activity,'.xls'),'Sheet',i);

I am wondering how I would go about renaming the three new sheets that contain my three different trials held within them. I have tried using this:

    %%Added 17/098/2022
    %         excel = actxserver('Excel.Application'); % # open Activex server
    %         filepath = pwd;
    %         excelfilename = activity,'.xls';
    %         excelWorkBook = excel.Workbooks.Open(fullfile(filepath,excelfilename));
    %         excelWorkBook.Worksheets.Item(1).Name = 'Trial 1';
    %         excelWorkBook.Worksheets.Item(2).Name = 'Trial 2';
    %         excelWorkBook.Worksheets.Item(3).Name = 'Trial 3';
    %         excelWorkBook.Save % # save to the same file
    %         excelWorkBook.Close(false)
    %         excel.Quit

However, it does not work, it only changes the name for one sheet and then it makes the excel file open up in the background without me being able to visibly see it. Any advice or help would be greatly appreciated. I can also attached the full working code if that would be of any use?

Thank you so much in advace,

Lexi

Answer 1

[dpb]

Just name them as you want them when created them instead...

%%  Create a folder where the data will be saved
OutputFolderName =  'TimeSyncData';  
if ~exist(OutputFolderName, 'dir')
    mkdir(OutputFolderName);
end     

%creating an excel sheet with the new time synchronised data contained,
%different sheets are the different trials
for i = 1:sz
    writetable(struct2table(sync(i)),fullfile(OutputFolderName,[activity '.xlsx']), ...
        'Sheet',"Trial "+i);

[alexandra ligeti]

Hi there,

Thank you for your response, however when I use that line of code I get this error:

Error using T03 (line 215)

Unable to save the workbook to file 'C:\Users\lexil\Documents\PhD\Patient_Study\Healthy

Participants\Participant_data\Experimental_data\H02\TimeSyncData\TimeSyncData\T06.xlsx'. Check that write permissions are

available, there is sufficient disk space, and the file can be written to or created.

[dpb]

That's a file system problem by the message -- I note that the (extremely long and convoluted) path contains "TimeSyncData" twice -- I'm guessing this is probably incorrect and that folder doesn't exist.

Check your code logic in building and creating the path -- and ensure you're running code from a known working directory and don't be changing pwd in code -- make sure to use fullfile and fully-qualified filenames everywhere to access proper files.

It may seem easier to just jump around locally and use short names initially and you may have a few places to fix, but it will be far cleaner in the end.

ADDENDUM

Once you solve the naming problem to ensure you're writing to the proper (and existing) location, the logic works just fine...I happened to have a little cell array hanging around in memory from a prior Answer so I ran a quick test at the command line...

 wd=cd;                                     % get a valid directory base location
fn=fullfile(wd,'Data','Testing.xlsx');      % create a qualified filename
for i=1:3                                   % illustrate your desired operation
writetable(cell2table(a),fn,'Sheet',"Trial "+i)
end

When that finished, then

>> sheetnames(fn)
ans = 
  3×1 string array
    "Trial 1"
    "Trial 2"
    "Trial 3"
>> 

shows that writetable created the sheets with the desired names as well as the result of the addition of the loop index value to the string by the overloaded "+" operator with string class--a very hand extension for such purposes.

[alexandra ligeti]

Thank you so much! I have managed to get the file naming issue sorted, the program now navigates between the different folders correctly. The renaming of the sheets works now too!

Thanks again for your help.

[dpb]

I knew you could... :)

BTW, I've not tried the specific exercise you tried so not positive and didn't take the time to go debug, but I expect what happens in the rename exercise is that the Items collection gets shuffled when the new name is written and your code doesn't update to see which sheet is which in the collection order.