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"
			try
				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
				beep
				display alert errNum message errmsg & return & "Could not continue" as warning buttons {"OK"} default button 1 giving up after 5
				return
			end try
		end tell
	end ignoring
end repeat

Thanks so much for any help you can give!
jared

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.

Try

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.