Select a Cell in iWork Numbers to Paste a Photo Using System Events

How do you select a cell in Numbers using Applescript.

I wish to paste several hundred image files into cells in Numbers '09. I believe this cannot be done directly using commands from the Numbers Library but it it is possible using System Events keystroke-paste a photo from the Clipboard. I can get the images from the photo files to clipboard. I can paste them to Numbers but I cannot figure out how to select a given cell in Numbers to paste a photo.

In the script below I kludged a solution where I manually select a cell, thereafter the System Event command Keystroke Return will choose the next cell in the column

--before running select the top cell in the column for the photos.  This will not work unless a cell in manually selected

try
	set the clipboard to (read (choose file with prompt "Select an image file:" without invisibles) as TIFF picture)
end try

tell application "Numbers"
	activate
	tell document 1 to tell sheet 1
		tell table "Photos"
			repeat with i from 1 to 5
				set value of cell i of column 1 to "test3"
				tell application "System Events"
					keystroke "v" using {command down}
					keystroke return
				end tell
			end repeat
		end tell
	end tell
end tell

This one will do the trick:

--before running select the top cell in the column for the photos.  This will not work unless a cell in manually selected

try
	set the clipboard to (read (choose file with prompt "Select an image file:" without invisibles) as TIFF picture)
end try

tell application "Numbers"
	activate
	tell document 1 to tell sheet 1
		tell table "Photos"
			repeat with i from 1 to 5
				set value of cell i of column 1 to "test3"
				set nameOfCell to name of cell i of column 1 -- ADDED
				set selection range to range nameOfCell -- ADDED
				tell application "System Events"
					keystroke "v" using {command down}
					keystroke return
				end tell
			end repeat
		end tell
	end tell
end tell

Yvan KOENIG (VALLAURIS, France) vendredi 27 novembre 2009 12:05:12

Fantastic. Thanks much. Also thanks for you many posts and your script samples on mobileme. I think there is a bit of yours herein. Below is the completed script. Not elegant but it works ok.

(* 
This is a basic script that copies selected filenames and images/photos as cell backgrounds into two columns within iWorks Numbers '09.  Be careful as it does not limit the file type chosen. It does not check to see if a table named "photos" already exists. *)

--====== Choose Files ======
set theFiles to (choose file with prompt "Select the image files:" with multiple selections allowed)
set theCount to count theFiles

--==== Make the Table =====
tell application "Numbers"
	activate
	
	if not (exists document 1) then
		display dialog "There is no document open." buttons {"Cancel"} default button 1
	end if
	
	-- make  the table
	tell document 1
		tell sheet 1
			set this_table to make new table with properties {name:"Photos", column count:5, row count:(theCount + 1)}
			tell this_table
				-- set any global cell properties
				set the height of every row to 50
				set the width of every column to 75
				set the vertical alignment of every row to center
				set the alignment of every row to center
			end tell
		end tell
	end tell
end tell

--===== Main Routine - select the cell, copy file image to clipboard, activate system events to paste the clipboard into the selected cell =====

tell application "Numbers"
	activate
	
	repeat with i from 1 to theCount
		-- gets the file information into variables and clipboard
		set thisFile to item i of theFiles
		tell application "Finder" to set file_name to (name of thisFile)
		set the clipboard to (read (thisFile) as TIFF picture)
		-- pastes information into Numbers
		tell this_table
			set value of cell (i + 1) of column 1 to i
			set value of cell (i + 1) of column 2 to file_name
			set nameOfCell to name of cell (i + 1) of column 3
			set selection range to range nameOfCell
			tell application "System Events"
				keystroke "v" using {command down}
			end tell
		end tell
	end repeat
end tell



--===== Makes sure UI Elements is enabled to use System Events

on activateGUIscripting()
	tell application "System Events"
		if not (UI elements enabled) then set (UI elements enabled) to true (* to be sure than GUI scripting will be active *)
	end tell
end activateGUIscripting

--=====

Thanks for the feedback.

The scripts which I uploaded on my iDisk are all mine (except perhaps one or two).

Yvan KOENIG (VALLAURIS, France) vendredi 27 novembre 2009 17:30:21