Script is pasting values as images instead of as values

Hi all,

It’s been about a year since I’ve used Applescript but I recently returned to it to help automate some spreadsheets for work in bulk. Below is my code, and it’s doing everything right so far except for ONE thing. (not too shabby, I think, for someone who hasn’t seen this stuff in a while!)

What I want the script to do is this:

The problem here is step 4. The script copies the info from Spreadsheet 2, pastes the values on the final spreadsheet selected, then pastes an IMAGE of the values on the remaining spreadsheets selected, not the actual values.

Example: if I select 5 spreadsheets for formatting, 1 will have the desired values in column A, and 4 will have Images of the values pasted in column A instead (like a screenshot or something).

I suspect “paste special” is to blame but I’m not quite sure why or how and haven’t yet found an alternate syntax in the Excel Applescript dictionary that works in its place.

Let me know what you guys and if you have any suggestions! Thanks so much!


---------------------------------------------------------------------------------------------------
--Asking user for desired files, setting variables, and starting a counter for the later repeat fcn
set MotorMind_LessonTemplates to (choose file with prompt "Select the MotorMind Lesson Template you want to add tags to. Multiple selections ARE permitted:" with multiple selections allowed)

set Each_MotorMind_File to every item of MotorMind_LessonTemplates

set MotorMind_TagsFile to (choose file with prompt "Select the file containing the MotorMind tags" of type {"XLSX", "XLS"})

set RepeatCounter to number of items of MotorMind_LessonTemplates
---------------------------------------------------------------------------------------------------


---------------------------------------------------------------------------------------------------
--A/S retrieves the codes it needs and copies them to the clipboard
tell application "Microsoft Excel"
	activate
	open MotorMind_TagsFile
	set MotorMind_Tags to range "A:A" of MotorMind_TagsFile
	copy range MotorMind_Tags
end tell
---------------------------------------------------------------------------------------------------


---------------------------------------------------------------------------------------------------
--Formatting begins
tell application "Microsoft Excel"
	activate
	
	--A/S opens the files that need the codes and pastes them to column A
	open Each_MotorMind_File
	
	repeat until RepeatCounter = 0
		select range "A:A"
		paste special on worksheet active sheet
		
		--A/S deletes column c
		set column_c to range "C:C" of active
		delete range column_c
		
		--A/S saves and closes worksheet and moves to the next sheet
		save active workbook
		close active workbook
		set RepeatCounter to RepeatCounter - 1
	end repeat
	
end tell
---------------------------------------------------------------------------------------------------

display dialog "All done!" with icon note

UPDATE: So “paste special” was the culprit after all, and I worked around it with GUI coding, but naturally I hit another snag.

Despite testing the script with identical spreadsheets, Excel spits back an error asking if I’d like to paste the values into the new destination despite the fact that the source and destination are different sizes.

Can I use GUI scripting to get my A/S to click “OK” on that silly message asking me if I still want to copy my values? So far this code isn’t working:


	--What I'm getting at here is this: the tags sheet and the lesson sheets for some reason don't line up, and Excel spits back an error message asking if you're sure you want to paste where you've selected. We do. So I want the A/S to try to paste, and if it gets that error from Excel to click "OK" and move on
		try
			save active workbook
		on error
			tell application "System Events"
				click (button "OK" of window 1)
			end tell
		end try
		close active workbook

Thanks for the help every body!

UPDATE 2: I figured it all out!

If anyone’s interested, the final code is below.

Its solutions are not always elegant, and it does require more cooperation from the user than I normally like, but it gets the job done. Especially since most of the time the user in question will be me!


---------------------------------------------------------------------------------------------------
display dialog "WARNING: This script will overwrite the files you select for formatting. It will NOT save them as new files. Make a copy of the files you want to format and put those copies in a folder on your desktop before continuing." with icon caution
---------------------------------------------------------------------------------------------------


---------------------------------------------------------------------------------------------------
--A/S asks the user for desired files, setting variables, and starting a counter for the later repeat fcn
set MotorMind_LessonTemplates to (choose file with prompt "Select the MotorMind Lesson Template you want to add tags to. Multiple selections ARE permitted:" with multiple selections allowed)

set Each_MotorMind_File to every item of MotorMind_LessonTemplates

set MotorMind_TagsFile to (choose file with prompt "Select the file containing the MotorMind tags" of type {"XLSX", "XLS"})

display dialog "Click OK to start formatting. Once started, do not open ANY other windows - including word docs and webpages. Seriously. If you have uploaded at least 10 files for formatting, go get a coffee or crack open a book for the next 5-7 minutes." with icon note

set RepeatCounter to number of items of MotorMind_LessonTemplates
---------------------------------------------------------------------------------------------------


---------------------------------------------------------------------------------------------------
--A/S retrieves the codes it needs and copies them to the clipboard
tell application "Microsoft Excel"
	activate
	open MotorMind_TagsFile
	set MotorMind_Tags to range "A:A" of MotorMind_TagsFile
	copy range MotorMind_Tags
end tell
---------------------------------------------------------------------------------------------------


---------------------------------------------------------------------------------------------------
--Formatting begins
tell application "Microsoft Excel"
	activate
	
	--Applescript has a default timeout of 2 minutes between commands. On testing, the script timed out before all motormind files (when doing bulk formatting) opened and the next line of script could be executed. It only took about 5-6 mins to open all 75 files, but I'm giving this a 15 minute timeout just in case
	with timeout of 1000 seconds
		
		--A/S opens the files that needs the codes
		open Each_MotorMind_File
		
		--this repeat tells the script I need to run the below process until the counter reaches 0, where each number in the counter is a file selected by the user.
		repeat until RepeatCounter = 0
			
			--A/S pastes the codes to column A
			select range "A:A"
			tell application "System Events" to keystroke "v" using command down
			
			--A/S deletes column c
			set column_c to range "C:C" of active
			delete range column_c
			
			
			--A/S saves and closes worksheet and moves to the next sheet
			--The "try statement" is important here. Excel sometimes spits back an error message asking if you're sure you want to paste where you've selected. We do. So I want the A/S to try to paste, and if it gets that error from Excel to click "OK" on the error message, save the file, then move to the next one if one exists.
			try
				save active workbook
			on error
				tell application "System Events"
					set frontmost of process "Excel" to true
					tell process "Excel" to keystroke return
				end tell
			end try
			save active workbook
			close active workbook
			set RepeatCounter to RepeatCounter - 1
			
		end repeat
	end timeout
	
	--Here the script closes down the tags file without saving changes or prompting a "copy to clipboard" message
	set the clipboard to MotorMind_Tags
	close active workbook without saving
	
end tell
---------------------------------------------------------------------------------------------------


---------------------------------------------------------------------------------------------------
--this forces a pop up window to the front of the screen to tell the user formatting is done
tell me to activate
display dialog "All done!" with icon note
---------------------------------------------------------------------------------------------------


“Paste Special” accepts arguments to specify the pasted format, did you try using those arguments to specify that it paste values, rather than as image? Take a look at Excel’s Applescript Dictionary entry for “Paste Special.”

There’s also an entry for “Paste.” I’d think that using that would get you the same result as using the UI scripting keyboard command you’ve switched to, but be less error prone.