I am new to AppleScript. Can someone tell me how to write a script to copy a worksheet in Excel and move the copied sheet to a new workbook?
Excel from Microsoft Office X (Excel 10.1.5) is Recordable, an often overlooked feature of AppleScript (maybe because so few apps are recordable?), and I got this:
tell application "Microsoft Excel"
activate
Create New Workbook
set FormulaR1C1 of ActiveCell to "TEST DATA"
Select Sheet "Sheet1"
set Name of Sheet "Sheet1" to "Test Sheet"
Select Sheet "Test Sheet"
CopyObject Sheet "Test Sheet"
end tell
Basically I started recording, then:
–Entered “TEST DATA” in to the first cell
–Renamed the first sheet to “Test Sheet”
–Right-clicked the sheet name and selected “new workbook” and “copy” options.
–Switched back to Script Debugger (Script Editor, or whatever your flavor) and stopped the recording
–Voilà !
Using Mac version of Excel, not recordable as far as I can tell. Script did indeed create new workbook, but it didn’t copy my existing sheet into the new workbook. If you are recording, what I’m trying to do is go to Edit menu and select ‘Move or Copy Sheet . . .’ , click ‘Create A Copy’ box, select ‘(new book)’ under the To book menu, and then click OK.
Thanks!
Record is a feature of Script Editor (or in my case Script Debugger), big green record button. Push that, switch to Excel, do your thing, then switch back to Script Editor and click Stop.
There may be some confusion if “Excell 2008” is different from Excel that is part of “Microsoft Office X” and is individually version (Get Info) 10.1.5. It’s possible their scripting has change if they are different versions.
The way I did it it the same as you did…you used the menus, I used contextual menus, same dialogs. More simplified:
tell application "Microsoft Excel"
Activate
CopyObject Sheet "Sheet1"
end tell
If you have an Excel spreadsheet open with the default sheet names (Sheet 1, Sheet 2, Sheet 3) and are using Sheet 1, then this script, when run from Script Editor, should simply switch to Excel, copy Sheet 1 to a new document, then return to Script Editor.
If it doesn’t, we have difference versions of Excel, different enough to cause compatability issues. If so, sorry. 
If you get an error, what is the error?
Using Excel 2008 for Mac Version 12.0.1
When I hit record, switch to Excel, do what I want to do and then come back to ScriptEditor and hit Stop, nothing has been recorded.
When I try compiling the script you wrote, I get an error message that says “Syntax Error: Expected end of line, etc. but found class name” and it highlights the word ‘Sheet’ (following ‘Object’) in the line ‘Copy Object Sheet “Sheet1”’
Bummer, different versions, yours is newer. Remind me to tell my IS/IT folks not to upgrade, I like the recordability of Excel 10.x… 
Sorry to lead you on a wild goosechase, but at least now I have the “Excel 2008” versioning straightened out.
I also am using 2004, but this different syntax copied Sheet1 of the ActiveWorkbook to a newly created workbook.
tell application "Microsoft Excel"
activate
copy worksheet sheet "sheet1"
end tell
The MicroSoft website has a download for the apple scripting model for both 2004 and 2008.
That worked! One last wrinkle: instead of referring directly to “sheet1” can I just have the script copy the active sheet, regardless of the sheet name?
Thanks!
tell application "Microsoft Excel"
activate
copy worksheet active sheet
end tell
Thanks Mike, that works. Now, one last thing and I will never bother you again. What I’m doing is creating a purchase order, and I want to save the newly-created file with the filename being an order number that is found in a specific cell of the sheet. Let’s say the order number is in cell A1. Can I say something like 'Save As filename range “A1” or something along those lines?
Thanks again!
This script makes the new workbook and then saves it with the name found in A1. (“.xls” is added to that value)
tell application "Microsoft Excel"
activate
-- make new workbook
copy worksheet active sheet
-- save that workbook as file named in A1
set newName to value of range "A1" as text
set AppleScript's text item delimiters to {":"}
set oldPath to full name of active workbook
set newPath to text items of oldPath
set last item of newPath to newName & ".xls"
try
save workbook as activeworkbook filename newPath
on error
-- error handling
close active workbook saving no
display dialog "Error in Save. Check value of A1."
end try
end tell
If you want to put the user into the loop and give them a chance to cancel, perhaps this would work.
tell application "Microsoft Excel"
activate
-- make new workbook
copy worksheet active sheet
-- save that workbook with user seleted name
set filePath to ""
set allDone to false
set newName to value of range "A1" as text
repeat until allDone
try
-- User chooses Aave As File (A1 default)
get filePath to get save as filename initial filename newName
save workbook as activeworkbook filename filePath
set allDone to true
on error
if filePath = "" then
-- Cancel Pressed
close active workbook saving no
set allDone to true
else
-- other Error
set filePath to ""
display dialog "Bad file name. Try again"
end if
end try
end repeat
end tell
I’m sure that the Cancel/error handling can be done cleaner. I just don’t know how.
If you want the new book to be closed after being saved, put
close active workbook saving noimmediatly before the “end tell” in the first script and immediatly before “on error” in the second script.
By the way, please feel free to “bother” me again.
I am also learning applescript and live exercises on forums like this are valuable to my learning.
The second script was just about there, but when I got the Save dialog box and clicked OK, the file was nowhere to be found. In other words, it should have been saved to the desktop but wasn’t. I’ve certainly never seen that before.
My bad. Swap one “s” for one “g” and add a space:
tell application "Microsoft Excel"
activate
-- make new workbook
copy worksheet active sheet
-- save that workbook with user seleted name
set filePath to ""
set allDone to false
set newName to value of range "A1" as text
repeat until allDone
try
-- User chooses Aave As File (A1 default)
set filePath to get save as filename initial filename newName
save workbook as active workbook filename filePath
set allDone to true
on error
if filePath = "" then
-- Cancel Pressed
close active workbook saving no
set allDone to true
else
-- other Error
set filePath to ""
display dialog "Bad file name. Try again"
end if
end try
end repeat
end tell
Mike - that worked! You are a genius!!