Merging multiple Excel files

[Gintaras]

This macro gets all xls files on the desktop, merges all except first and last row, and opens the output file in Excel. You need to edit it: set range of columns, and maybe something else.

Macro ExcelMergeFiles:

Code:

Copy      Help

typelib Excel {00020813-0000-0000-C000-000000000046} 1.2 0 1

Excel.Application a._create ;;create new Excel instance
Excel.Worksheet ws=a.Workbooks.Add.Worksheets.Item(1) ;;create new workbook and get first worksheet
Excel.Range r_dest=ws.Range("A:C") ;;select first 3 columns. You need to change this.
int row_counter=1

;add all xls files that are on the desktop, in random order
Dir d; str sPath
foreach(d "$Desktop$\*.xls" FE_Dir)
,sPath=d.FileName(1)
,;out sPath
,ExcelStripAndAdd sPath r_dest row_counter

;;or, use list of files
;str sPath
;lpstr files=
;;file1
;;file2
;;...
;foreach sPath files
,;ExcelStripAndAdd sPath r_dest row_counter

;show Excel. You will need to save (in some other folder).
a.Visible=TRUE

Function ExcelStripAndAdd:

Code:

Copy      Help

;/ExcelMergeFiles
function $source_file Excel.Range&r_dest int&row_counter

Excel.Workbook wb._getfile(source_file) ;;open file in background
Excel.Worksheet ws=wb.Worksheets.Item(1) ;;get first worksheet
Excel.Range used_range=ws.UsedRange ;;get used range
Excel.Range row_src row_dest
int i

for i 2 used_range.Rows.Count ;;all rows except top row and bottom row
,row_src=used_range.Rows.Item(i) ;;get source row
,row_dest=r_dest.Rows.Item(row_counter) ;;get destination row (initially empty)
,row_dest.Value=row_src.Value ;;copy
,row_counter+1