Copy .xls sheet into .xlsm sheet

Question

I have .xls file with several sheets. I have .xlsm file with several sheets. I want to copy xls.sheet(x) into xlsm.sheet(y). I tried the following code:

try
    XLTo := CreateOleObject('Excel.Application');
    XLFrom := CreateOleObject('Excel.Application');
except
    ShowMessage('Excel might not be installed');
    exit;
end;

XLFrom.Workbooks.Open(filename1,1);
XLTo.Workbooks.Open(filename2,1);
SheetFrom := XLFrom.WorkSheets['Sheet Caption'];
SheetTo := XLScreener.WorkSheets['Sheet Caption'];
SheetFrom.Select;
SheetTo.Select;

Until here, everything looks OK, now I need to copy SheetFrom into SheetTo but I'm confused on how to do it. I tried:

SheetScreener.Copy(SheetDF);

But I got an error "copy method of worksheet class failed". SheetTo (xlsm) is a regular sheet with data that I want to override with the data of SheetFrom.

What am I missing?

I read the internet and saw several methods but I don't know if I fail because it's .xlsm or because I did something wrong. Also, I read that it had to be with the same Excel app but I'm copying from two different files so I'm not sure how to do it.

Do you want to create a new sheet in the target, or overwrite the contents of an existing sheet? — David Heffernan, Sep 05 '15 at 07:12
Don't you need to delete it first, and make a new one? Or will that break refs? If so then perhaps you need to copy a range rather than a sheet. Copying a sheet creates a new sheet. — David Heffernan, Sep 05 '15 at 07:46
All refs are invalid if I delete old sheet and copy the new one under the same name, — Amos, Sep 05 '15 at 09:04
You got that right and I made it, please add your comment as an answer so I can accept it — Amos, Sep 05 '15 at 09:14

score 1 · Accepted Answer · answered Sep 05 '15 at 12:40

When you copy a sheet, a new sheet is created in the destination workbook. I think you actually want to overwrite an existing sheet's contents. In which case do the following:

Specify the source as a Range object in the source workbook.
Specify the destination as a Range object in the destination workbook. Just specifying the top left cell suffices.
Use Source.Copy(Dest) to perform the copy.

nogenius · Answer 2 · 2015-09-05T22:49:18.347

0

To copy the sheet and insert it as last sheet try

SheetFrom.Copy(XLTo.Sheets(XLTo.Sheets.Count))

Then you can delete the original SheetTo - if this works for you

EDIT: Corrected syntax for delphi

edited Sep 05 '15 at 22:49

answered Sep 04 '15 at 21:33

nogenius

574
1
6
18

It's delphi so I changed the code to this: SheetFrom.Copy(XLTo.Sheets(XLTo.Sheets.Count)); but I got "member not found" – Amos Sep 04 '15 at 21:38
Maybe you should create only one instance of the `Excel.Application` object. – TLama Sep 04 '15 at 22:01
I managed to open 2 sheets using the same excel app, now I get an error when trying to select a sheet – Amos Sep 04 '15 at 22:13
I'm no good in Excel automation, but do you actually need to select the worksheet object ? You have references to both worksheets, so why not just call that `Copy` method ? [it's a wild guess] – TLama Sep 04 '15 at 22:22

Copy .xls sheet into .xlsm sheet

2 Answers2