How to save tables to different sheets in one xlsx file?

Hello everyone,

i have a for loop reading several files and doing some calculations for each of them. What i want to do is to save the results from every file in a seperate sheet on one excel file.

%% reading the files 
files = dir('*.txt');
N=length(files);


  for  i=1:N
    filename=files(i).name;
    fid=fopen(filename);
    data = textscan (fid, '%s %s %s %s %s %s %s %*s %*s %s %*s', 'headerLines', 2); 
    
 %%saving 
 R1=table (Time, beta, Sh, K, 'VariableNames' , {'Time', 'beta', 'Sh', 'K'});
 F_xlsx=sprintf('results.xlsx');
 writetable(R1,F_xlsx,'Sheet',[files(i).name]);

this is how i tried, but the it is filling the sheet only with the first file.

[Andy]

from the Matlab documentation "wrtitetable(T) writes table T to a comma delimited text file." As far as I am aware, this format doesn't support multiple sheets. I have used xlswrite when writing to Excel as you can define the sheet to be written to.

Answer 1

[Nicolas B.]

Have you done a step-by-step execution of your code to be sure that there are multiple files read?

Looking at it like that, I would just remore the square brakets to the sheet name:

writetable(R1,F_xlsx,'Sheet',files(i).name);

Personnally, I'm using writematrix() the same way on different tables without any problem.

[Niya Bedzheva]

hey, thanks for your answer, i removed the brackets and it worked.

Yeah, there are several files read for sure, because before that i saved them in seperated txt files for furher use. i just wanted to have them all in one excel file, so i can show results easily. What bothers me now is that i have the these 3 empty sheets by default in excel: 'Sheet1' 'Sheet2' and 'Sheet3' and after that starts the consequence of my files.

[Nicolas B.]

For that, I recommand you the RemoveSheet123 tool.

[Niya Bedzheva]

everything is perfect now. Thank you very much for the help! :)

[FM]

I should have known better than to try exporting the CSV and writing VBA to import multiple files into different sheets within a common workbook. What a nightmare. This is a much more elegant solution, by orders of magnitude.