Combining Excel Sheets into 1 Sheet

I’ve got a problem that I’m trying to work out but am having a really hard time nailing down.

I have a group of Excel Spreadsheets that I have to combine the data from many sheets into the first sheet (which I am renaming to Sheet1). The end goal is to import these spreadsheets into a Filemaker system. Since Filemaker can’t adapt to the varying amount of tabs and naming of those tabs I need to use Applescript (which I’m very new at).

My problem points come at line 22 where I’m trying to get the total sheet count. I can get a good result in the expression section of Script Debugger 4.5 but it bears no fruit in the actual script. If I hard code the lastSheet variable to a number (say 7) then it works and will loop. But I’m also unclear about how to copy and paste the cell ranges.

Here’s what I have so far for code:

set ex_folder to (choose folder)

tell application "Finder"
	set ex_files to every file of ex_folder as alias list
end tell

repeat with i in ex_files
	set file_path to i as text
	ignoring application responses
		-- ignore the "this is a web application" warning
		tell application "Microsoft Excel"
				open i
				tell first workbook
					set ws to (a reference to sheet 1)
					set name of ws to "Sheet1"
					--clear contents of Sheet1
					delete used range of ws
					set lastSheet to count of worksheets in workbook
					repeat with s from 3 to lastSheet
						--Get the range to copy and copy them
						set copyWS to (a reference to sheet s)
						set copyRange to used range of copyWS				
						--Set the range to be pasted into and paste
						set pasteWS to (a reference to sheet 1)
						set destLastRow to ((count of rows of used range of pasteWS) + 1)
						paste copyRange in destLastRow
					end repeat
					save workbook as filename file_path file format Excel98to2004 file format with overwrite
				end tell
				close first workbook
			on error errmsg number errNum
				display alert errNum message errmsg & return & "Could not continue" as warning buttons {"OK"} default button 1 giving up after 5
			end try
		end tell
	end ignoring
end repeat

Thanks so much for any help you can give!

Model: Mac mini
AppleScript: 2.2.3
Browser: Safari 536.26.17
Operating System: Mac OS X (10.8)

I don’t know the jargon but

set lastSheet to count of worksheets in workbook

sets lastSheet to the number of sheets in the workbook as a continuing thing. If sheets are added, lastSheet will increase.


set lastSheet to Get (count of worksheets)

For some reason that’s still not working The variable never actually sets and when I want to do something with it I get an error.