Matlab/ActiveX Excel - Copying specific sheets into one workbook

Question

I am writing some software to average many data points from different measurements together. I've found excel to be more aptly suited to dealing with the occasional data point (users can easily see and delete it).

The script that ingests the measurement produces a .xlsx file and dumps the formatted data in a sheet with the serial number as a name, so it is easy to explicitly reference that sheet. The output looks like this:

https://i.stack.imgur.com/wHgKN.png (sorry, can't post images yet)

There are between 5-50 workbooks that have sheets in this format that will be copied. The target workbook has a blank sheet "start" and a blank sheet "finish" followed by a sheet "summary1" that averages through start-finish with =AVERAGE(start:finish!B53) and many similar commands.

I use the matlab code

Excel = actxserver('excel.application');
Excel.Visible = 1;
[ AVGWBFilename, AVGWBPath ] = uigetfile('path\*.xlsx','Locate Average Spreadsheet','MultiSelect','off');
AVGWBLoc = strcat(AVGWBPath,AVGWBFilename);
AVGWB = Excel.Workbooks.Open(AVGWBLoc); % get filename and path of average workbook, then open
LensDirectories = regexp(genpath(AVGWBPath),['[^;]*'],'match');

to have a user select the workbook to copy into. The LensDirectories cell array is populated by directories to the subfolders which contain the xlsx files I would like to copy from. Then they are looped through:

for i = 2 : length(LensDirectories);
    tempdir = dir(LensDirectories{i});
    tempfile = tempdir(7,1).name;
    temppath = fullfile(LensDirectories{i},tempfile);
    LensNum = str2num(sprintf('%s',LensDirectories{i}(end-5:end)));
        eval(sprintf('Workbook_%d = Excel.Workbook.Open(temppath);',LensNum));
        eval(sprintf('LensSheet_%.0f = Workbook_%d.Sheets("%d");',i,LensNum,LensNum));

But here is where I get stuck. the final nested sprintf produces a working string, but eval throws an error "The input character is not valid in MATLAB statements or expressions." - I think this is due to the quotation marks in the string?

Once the workbooks are open I should be able to make a variable to reference the sheet, but I am also stuck on the code to successfully copy between workbooks. The target workbook and source workbook will be open for the operation - I think the code goes something like 'Workbook_222045("222045").Copy Before:=AVGWB("finish")' or Workbook_222045("222045").Copy(AVGWB("finish") but I do not know visualbasic and this is the first time I have had to use ActiveX controls with matlab, so I am not sure how to proceed.

I have also found this snippet which seems better, but the indexing depends on the sheet number, which will change for finish as the Average xlsx workbook is populated.

WS = Excel.ActiveWorkbook.Sheets;
WS.Item(1).Copy([],WS.Item(1)); %to copy after first sheet.
WS.Item(1).Copy([],WS.Item(WS.count)); % to keep duplicating, new sheet will be after all existing sheets. 

Any help is greatly appreciated.

All the best,

Brandon

Answer 1

Update: debugged and found solution, the new working for loop is as follows.

for i = 2 : length(LensDirectories);
    tempdir = dir(LensDirectories{i});
    tempfile = tempdir(7,1).name;
    temppath = fullfile(LensDirectories{i},tempfile);
    LensNum = str2num(sprintf('%s',LensDirectories{i}(end-5:end)));
    eval(sprintf('Workbook_%d = Excel.Workbook.Open(temppath);',LensNum));
    eval(sprintf('Workbook_%d.ActiveSheet.Copy(AVGWB_finish);',LensNum));

There are restrictions to it, namely that the active sheet when the averaging workbook is saved must be the finish sheet, but it is functional for now.