Merging multiple Excel files

[ken gray]

is there a way to get the raw cell data from a spreadsheet with something like a "getfile" command.

maybe pipe-delimit the values or something like that?

what i need to do in the end is open 200+ files and strip out the headers and footers to get just the data and put it in one file.

thanks.

[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

[ken gray]

Yeeesh!!!
and i thought that this was something i could come up with if you pointed me to the right function hock:

two words....uuuuuuuuuughhhh.....ok well that's just one word but WOW....no freakin way!

THANKS!

[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

[ken gray]

Awesome...thanks

i found how to exclude the top rows but the bottom rows arent getting exclued. the "header" is 5 rows and the footer is 13.
can i exclued them both with differ lengths?
i tried to do a total rows-13 type thing but it didnt work.

[Gintaras]

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

int remove_header_rows_count=5
int remove_footer_rows_count=13

for i 1+remove_header_rows_count used_range.Rows.Count+1-remove_footer_rows_count
,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

[ken gray]

WOOT!!!!
works great...now they think i'm a genius...and that can be a burden, as you already know :wink:

i'm thinking an O'Reilly book...