I have an excel Sheet that I need to send as an email attachment on a regular basis. I want to be able to send it with just the values and numbe values and number formatsr formats. I am trying to write Applescript to select the desired range, copy, open a new workbook, paste special ( values and number formats), send to (mail recipient), close without saving New Workbook.
Here is my code.
tell application "Microsoft Excel"
tell worksheet "active" of active workbook
select range "A1:N15"
end tell
tell application "System Events"
keystroke "c" using command down
end tell
tell application "System Events"
keystroke "n" using command down
end tell
tell application "system Events"
click menu item "paste special" of menu "edit"
end tell
end tell
When I run it copies what I want and opens a new workbook and then nothing. If I manually click th paste special it does just what I want.
Hopefully this works for you, maybe you’ll have to change the numbers, for the menu items, I have used numbers, in order to not use the localized names, as you can see, you need to specify the menu bar as well as the menu, and menu item. I have also changed the call hierarchy in your script, so Excel can be adressed correctly from System Events.
tell application "Microsoft Excel"
activate
tell worksheet "active" of active workbook
select range "A1:N15"
end tell
end tell
tell application "System Events"
tell application process "Microsoft Excel"
keystroke "c" using command down
keystroke "n" using command down
tell menu 4 of menu bar 1
tell menu item 9 to click
end tell
tell button "OK" of window 1 to click
end tell
end tell
Excel has a huge AppleScript dictionary, GUI scripting is not needed
tell application "Microsoft Excel"
tell worksheet "active" of active workbook
copy range range "A1:N15"
end tell
set newWorkbook to make new workbook
tell active sheet of newWorkbook
paste special range "A1:N15"
end tell
-- do Mail task
close window 1 saving no
end tell
You are of course right, with regards to Excels huge object model, which is why I liked the OP’s approach by using UI Scripting, in stead of reading up on the object model. Sometimes I feel it counter productive to learn a lot just to get “something working” too.
Because of that, I felt it right to correct the script, because in many cases, when you have UI Element inspector on your disk, you will actually be able to create a solution in Excel faster with it, than reading up, and experimenting with the dictionary.
I agree with you in that gui is not a sustainable solution, but I feel that under certain circumstances; (general) you know GUI scripting, and you are writing something that is just doing a job for you once, or you are trying out an idea. Then you may not interested in investing time into something that is out of your boundary, with respect to learning an object model the size of Excels, for something you want to do just once, or just an initial experiment.
Then sometimes GUI scripting is the cheapest alternative with respect to programming time, delivering a quality to the solution that is feasible in the moment, and for the life span of the product. Then I see GUI scripting as the component delivering most “bang for the buck”.
I ran my solution a few times (Excel 2011) and it worked fine.
Of course without the code to handle the Mail part the close line at the end closes the new workbook immediately,
When I wrote the snippet, I tested that I didn’t have to select anything anything. And yes, you are corect in your assertions regarding the “Edit” meny, and the “Paste Special” command.
It worked for me, anyhow (Excel 2008) I wonder why it doesn’t for you. Can you see any error messages when you run the script from AppleScript Editor?
yes, "error “System Events got an error: AppleScript Editor is not allowed assistive access.” number -1719 from menu bar 1 of application process “Microsoft Excel” and highlighted click.
This does not show up when I run the script in excel.
Thanks for your help, this stuff always makes me feel dumb.
No reason for feeling dumb, just be patient, and try to run this tinkered script from Excel, maybe an error message pops up. The thing is is that I have neithe allowed Excel, nor System Events to control my computer via Accessibility. fro the security pane in System preferecences. You can allow Applescript editor this, in order to be able to run scripts from AppleScript editor that uses such settings.
tell application "Microsoft Excel"
activate
tell worksheet "active" of active workbook
select range "A1:N15"
end tell
end tell
try
tell application "System Events"
tell application process "Microsoft Excel"
keystroke "c" using command down
keystroke "n" using command down
tell menu 4 of menu bar 1
tell menu item 9 to click
end tell
tell button "OK" of window 1 to click
end tell
end tell
on error e
tell application "Microsoft Excel"
activate
display alert e
end tell
end try
I’ll come back to you with a propery written script.
Edit
I have tinkered Stefans script a little bit, I have omitted the deletion of the new workbook, so you can see that you end up with a new workbook. Hopefully this works as it should.
tell application "Microsoft Excel"
activate
if (count of workbooks) > 0 then
try
tell worksheet "active" of active workbook
copy range range "A1:N15"
end tell
set newWorkbook to make new workbook
tell active sheet of newWorkbook
paste special range "A1:N15"
end tell
on error e number n
tell "Microsoft Excel" to display alert "An error occured: " & e & " : number " & n
end try
end if
-- do Mail task
-- close window 1 saving no
-- We are done with the workbook we attached to the mail, and can delete it.
end tell
Can you see any error messages when you run the script from AppleScript Editor?