Consolidating multiple Excel workbooks into single workbook

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.

Answer 1

[Image Analyst]

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.

[Jeff]

Splendid! Thank you for the prompt response (and for alleviating my anxiety that this could be a straightforward solution I was missing). This was exactly what I was trying to accomplish. Very elegant solution.

[Meng Li Gan]

Hi, I can copy the cell of the worksheet to a new workbook now, but the graphic in the source workbook is missing in destination workbook. Is there any way to solve it? Thanks is advance

[Image Analyst]

I don't know how to copy other stuff like images, charts, etc. What you need to do is to

1. create/record a macro in Excel where you copy the graphic,
2. switch to the other worksheet,
3. paste it.
4. Then stop the macro recording and
5. edit it to see what VBA functions it used.
6. Then transfer those function calls to your MATLAB program.

This process works in general for whatever you want to do in Excel. Just record a macro and see what the macro did.

[Meng Li Gan]

Hi, thanks for your fast respond. I had tried to do something like this and the excel macro showed the code like:

    ActiveSheet.Shapes.Range(Array("Picture 1")).Select
    ActiveSheet.Shapes.Range(Array("Picture 1", "Subtitle1")).Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Pictures.Paste.Select

and I convert the code to

sourceWorksheets.Item(1).Shapes.SelectAll;
sourceWorksheets.Item(1).Shapes.Copy;
destinationWorkbook.Activate;   
activeSheet = excel.ActiveSheet;
activeSheet.Paste  

but i getting this error:

Undefined function 'Copy' for input arguments of type 'Interface.0002443A_0000_0000_C000_000000000046'.

Any suggestion for me to convert the code?? thanks in advance

[Image Analyst]

I don't know - haven't done it. This type of thing usually involves some experimentation to translate the calls exactly as needed. The macro gets you close but is not always a 1 to 1 verbatim translation. If you find out, post the answer back here.