Combine / merge Excel files

FLX TMS FlexCel for VCL & FMX
1

Hey there,


I'm wondering if there is a smart way to combine / merge two or more Excel files into one?
I think about opening files in two Flexcel components and copying from one component to the other.

Is there a smarter way to do it?


2

Hi,

To combine 2 file you would indeed open both files, then call InsertAndCopySheets http://www.tmssoftware.biz/flexcel/doc/vcl/api/FlexCel.Core/TExcelFile/InsertAndCopySheets.html#texcelfileinsertandcopysheetsint32array-integer-texcelfile  
As mentioned in that link, one problem with Excel files is that a sheet can have a reference to other sheet. So if you have file1 with sheet1 and sheet2, and sheet1 has a formula: =sheet2!a1, if you copy sheet1 first, then sheet2 hasn't been copied yet and the formula will break. You can't fix this by changing the order: If you copy sheet2 first, it might have a reference to sheet1 which will break.

So you need to copy both sheets at the same time so references between them are kept. For that, FlexCel has an overload of InsertAndCopySheets that takes an array of sheets to copy, and copies them all at the same time.

You can also take a look at the example http://www.tmssoftware.biz/flexcel/doc/vcl/samples/delphi/api/consolidating-files/index.html
But remember to copy all sheets at the same time  
3

Thanks for the quick reply!
In my case there are 48 files (each month) with just one sheet in them, so I will have no problem with the references, but I hope to keep that in mind for other cases.


The consolidating example is exactly what I was about to write, since I want to copy all data into one sheet in the destination file.

Thanks! You saved me quite a bit of time.