Merging multiple Excel files

[Gintaras]

Here may be useful to select the files in Windows Explorer, copy, and then get paths from the clipboard.

Function GetClipboardFiles:

Code:

Copy      Help

;/
function# ARRAY(str)&a

;Gets full paths of files copied to the clipboard.
;Returns number of files.

;EXAMPLE
;ARRAY(str) a
;GetClipboardFiles a
;int i
;for i 0 a.len
,;out a[i]


def CF_HDROP 15

str s
int i n

s.getclip(CF_HDROP)
if(!s.len) ret
n=DragQueryFile(s -1 0 0) ;;how many
a.create(n)
for i 0 n
,a[i].all(300)
,a[i].fix(DragQueryFile(s i a[i] 300))
ret n

The Excel macro then would be:

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 xls files copied to the clipboard
ARRAY(str) arr
int i
for i 0 GetClipboardFiles(arr)
,ExcelStripAndAdd arr[i] r_dest row_counter

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