Search code examples
delphiexport-to-excelxlsimport-from-excelxlsm

Copy .xls sheet into .xlsm sheet

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.

Solution

  • 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.