MATLAB: Consolidating multiple Excel workbooks into single workbook

activexexcel

Good afternoon,
After much searching and false starts I wanted to reach out regarding a seemingly straightforward operation. Please bear with me as I am a COM Object/ActiveX neophyte.
Running Matlab R2012a/Excel 2010.
Matlab generates four Excel files (A.xlsm, B.xlsx, C.xlsx, and D.xlsx) of which A.xlsm is a formatted, macro-enabled workbook. There are multiple worksheets in each file and I wish to script in Matlab (via ActiveX) a means of aggregating all of these workbooks into a unique workbook E.xls (maintaining the formatting present in the original 4 workbook's individual worksheets). No problems generating these four files via Matlab/ActiveX, but the problem arises when trying to combine these workbooks to a new, single workbook (with existing names and formatting).
The damage so far:
%%Begin
Savedir = uigetdir('','Where to save file?');
%%Opens the A.xlsm template
TemplateA = 'c:\tmp\A.xlsm'
tempFile = cell(2,1);
tempFile(1,1) = cellstr('A_temp_');
% Excel COM Object launch
Excel = actxserver ('Excel.Application');
set(Excel, 'Visible', 0);
original = invoke(Excel.Workbooks,'Open',TemplateA);
% Prompt re: location of A and addition of extension
prompt = {'Name your EXCEL File:'};
name = 'Stuff';
numlines = 1;
defaultanswer = {'.xlsm'};
options.Resize = 'on';
options.WindowStyle = 'normal';
options.Interpreter = 'tex';
tempFile(2,1) = cellstr(inputdlg(prompt,name,numlines,defaultanswer,options));
FileNameA = strcat(tempFile{1,1},tempFile{2,1});
FileA = fullfile(Savedir,FileNameA);
% Open Excel workbook
WorkbookA = Excel.Workbooks.Open(TemplateA);
% % Retrieve sheet names - currently unimplemented
% [~, sheetNamesA] = xlsfinfo(TemplateA);
Excel.ActiveWorkbook.SaveAs(FileA);
Excel.ActiveWorkbook.Close;
%%Opens the B.xlsx template
TemplateB = ... %repeats same process from above block to generate files B,C,D.xlsx [Note every file generates without any issues with code so far.]
%%Create aggregate file - [Note: here's where it gets dicey for me]
TemplateE = FileA; % Opens the first Excel file, ostensibly to capture all of the worksheets for copy
tempFile(1,1) = cellstr('E_temp_');
original = invoke(Excel.Workbooks,'Open',TemplateE);
FileNameE = strcat(tempFile{1,1},tempFile{2,1});
FileE = fullfile(Savedir,FileNameE);
% Open Excel workbooks and copy desired sheets
% This is where I fall flat
WorkbookA = Excel.Workbooks.Open(FileA);
sheetsWorkbookA = WorkbookA.Sheets;
invoke(sheetsWorkbookA,'Copy');
TemplateE = Excel.ActiveWorkbook;
activeSheetWorkbookB.Paste.sheetsWorkbookB;
% Add after the last sheet
invoke(activeSheetWorkbookUtility,'Paste',sheetsWorkbookUtility);
%Repeat for remaining files B, C, D
----> HELP/GUIDANCE <----
Excel.ActiveWorkbook.SaveAs(FileE);
Excel.ActiveWorkbook.Close;
Excel.Quit;
Excel.delete;
I appreciate the insight and expertise.
Thanks, J.

Best Answer

I didn't scrutinize your code but maybe you can mine. I have a generic function I wrote to transfer worksheets from one workbook to another. See the attached m-file.
Related Question