Write multiple excel files into one excel workbook sheet by sheet.

I have 40 excel files (.xlsx') with different names in a folder on desktop. I want to write them into one excel workbook named 'mydata.xlsx' containing 40 sheets with each sheet bear the names of each of the files. Kindly help me with the codes. Thanks.

Answer 1

[Walter Roberson]

outfile = 'mydata.xlsx';
dinfo = dir('*.xlsx');
filenames = setdiff({dinfo.name}, outfile);

for K = 1 : length(filenames)
   thisfile = filenames{K};
   [~, basename, ~] = fileparts(thisfile);
   T = readtable(thisfile);
   writetable(T, outfile, 'sheet', basename);
end

[Ojo Olusola]

The code runs and my mydata.xlsx with sheetname was created but the data from T were not written in any of the sheet. All the created sheets were blank. kindly help.

[Walter Roberson]

Ah yes. I have corrected the code.

[Ojo Olusola]

Thank you this code worked very well but the outfile mydata.xlsx is difficult to open in excel. l guess it is because we use readtable and writetable. Can we use xlsread and xlswrite instead? I have it brought out error message. Kindly help.

[Walter Roberson]

l guess it is because we use readtable and writetable.

That seems unlikely to be the reason, but you can try xlsread() / xlswrite() anyhow.

outfile = 'mydata.xlsx';
dinfo = dir('*.xlsx');
filenames = setdiff({dinfo.name}, outfile);

for K = 1 : length(filenames)
   thisfile = filenames{K};
   [~, basename, ~] = fileparts(thisfile);
   [~, ~, raw] = xlsread(thisfile);
   xlswrite(outfile, raw, basename);
end

[Ojo Olusola]

Thank you. This code now work efficiently.