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)