Virtuous cycle

Bartlomiej Owczarek weblog

Return to 'Virtuous cycle' home page

Automating MS Office for Mac: editing and pasting

I’m getting used to doing serious work on MS Office for Mac, and while it’s still not close to Windows experience, I believe the work is reasonably productive.

But there were few things which I was really missing:

  • F2 shortcut to edit cell / text field in Excel and Powerpoint
  • Keyboard shortcut to paste as unformatted text in Excel and Powerpoint

Solutions that I used don’t seem very elegant (basically making Applescript emulate clicks and key strokes), but at least it works for me and makes me more productive, so I share it below.

F2 to edit cell in Excel

On Mac Excel you are supposed to use Ctrl-U to go into cell edit mode, which for me is much worse than just pressing F2.

It didn’t seem possible to remap the shortcut, so I used app called Butler to do it. If I remember correctly the order of steps in Butler would be the following:

  • Add smart item -> keystrokes
  • In Keys tab, press Ctrl-U
  • In Triggers tab, in Hot Key field press F2
  • In same triggers tab, add “Microsoft Excel” to “only” exceptions, so the shortcut is limited to Excel

Paste special (as values) in Excel

The goal is to have one shortcut (like Ctrl-v) to paste copied cells as values.

The only way I found so far is to use Applescript and assign it to keyboard shortcut in Butler.

Important: for this and following scripts to work, you need to activate System Preferences > Universal Access > Seeing > “Enable access for assistive devices” checkbox.

The script:

tell application "Microsoft Excel"
	activate
end tell
 
tell application "System Events"
	tell process "Excel"
		click menu item "Paste Special..." of menu 1 of menu bar item "Edit" of menu bar 1
 
		click radio button 3 of radio group 1 of window "Paste Special"
 
		click the button "OK" of window "Paste Special"
	end tell
end tell

In Butler you add smart item Applescript > Applescript, paste code in Source Code tab, and assign a shortcut in Options tab, similar to previous case.

F2 to edit text in Powerpoint

The goal is for F2 to enter text edit mode of active shape, with all current text selected.

I used the following Applescript:

tell application "Microsoft PowerPoint"
	tell active presentation
		if (count shapes of shape range of selection of document window 1) is not 1 then
			set scenario to "no selection"
		else if (placeholder type of shape 1 of shape range of selection of document window 1) is placeholder type object placeholder then
			set scenario to "Placeholder Object"
		else
			set scenario to "Text Object"
		end if
	end tell
end tell
 
if scenario is not "no selection" then
	tell application "Microsoft PowerPoint"
		activate
	end tell
 
	tell application "System Events"
		tell process "PowerPoint"
			click menu item scenario of menu 1 of menu bar item "Edit" of menu bar 1
		end tell
	end tell
end if

Assigned it in Butler to F2, similar to previous case.

Paste special as unformatted text in Powerpoint

Applescript again:

tell application "Microsoft PowerPoint"
	activate
end tell
 
tell application "System Events"
	tell process "PowerPoint"
		click menu item "Paste Special..." of menu 1 of menu bar item "Edit" of menu bar 1
 
		keystroke tab
		keystroke tab
		keystroke tab
 
		repeat with a_row in rows of outline of scroll area of window "Paste Special"
			if value of static text of a_row is not {{{"Unformatted Text"}}} then
				--click static text of a_row
				keystroke (ASCII character 31)
			end if
		end repeat
 
		click the button "OK" of window "Paste Special"
	end tell
end tell

Assign to Ctrl-V in Butler as previously.

Yes, it seems pretty long way round, but that’s my best shot for now. I will update here if I find any easier way.


Comments


You can follow any responses to this entry through the RSS 2.0 feed.

If you would like to leave a general comment about this site, you might want to use a guestbook.


  1. November 21st, 2009 | 2:08 pm

    FYI, you can also assign scripts a keyboard shortcut without using Butler. Just make the name of the script end in a backslash and shortcut abbreviation. For example:

    PasteValues\mosV.scpt = command-alt-shift-V
    PasteValues\moV.scpt = command-alt-V
    PasteValues\mV.scpt = command-V

    And the one I use is \msV (command-shift-V).

    I found out about this method on the Mackerron Blog:
    http://blog.mackerron.com/2009/05/paste-unformatted-keyboard-shortcut-office-2008-mac/comment-page-1/#comment-2368

  2. November 29th, 2009 | 10:48 pm

    Didn’t know about it, thanks:)