You'll just have to loop over the sheets; use sheetnames to return the list of sheets in the given file and iterate over it--presuming the sheets are in the desired order in the workbook, they'll be returned in chronological order; if not you can always sort() it first.
Presuming the sheets are also in the same format, you can use the same import object for each, changing only the sheet name/number although naming the sheet explicitly in the readtable call overrides the import options value.
I'll note that you could also incorporate the datetime variable type and input format into the options object thus saving the explicit conversion code.
Since it's not known a priori (I presume) how many records are in each sheet, the simplest coding is to simply dynamically catentate the new table onto the existing one...
fn='BC1 2007-2020.xlsx'; % using a fully-qualified filename here would be good practice
tBC=[];
opt=detectImportOptions(fn);
% wanted/need options code/fixup here...
shts=sheetnames(fn);
for i=1:numel(shts)
tBC=[tBC;readtable(fn,opt,'Sheet',shts(i))];
end
...
Do the conversion to timetable from all only once at the end as well as the time conversion if choose to not use the import options route for import for it...
While dynamic allocation is not the most elegant solution, it is by far the simplest and as long as the sizes and numbers of the sheets to be imported isn't too large, the overhead will be not too painful -- particularly if only doing the reading once or a few times and mostly using the resulting data. Of course, once read, one can save the table as a .mat file and retrieve the whole thing very quickly; only when new data are added would it be necessary to reread the raw file again.
