Select and copy a cell in Excel

Scripting Forums AppleScript | Mac OS X
,
#1

A while ago, I created a script that would select and copy certain cells in an Excel spreadsheet, then paste it to a text document. This worked well, until I upgraded my OS and version of Excel.

With Mac OS X 10.3.9, Excel v.X (10.1.6) here is what I used:

tell application “Microsoft Excel”
Activate
Select Range “R2C2”
CopyObject Selection
end tell

Now, that script won’t even compile. I get an error “Syntax Error: Expected end of line, etc. but found property”
I now have Mac OS X 10.4.3 and Excel 2004 (11.2)
I tried changing the script to this:

tell application “Microsoft Excel”
Activate
Select Range “R2C2”
Copy Object
end tell

and I got this error “Microsoft Excel got an error: range “R2C2” doesn’t understand the select message.” Turns out that there is no long a “Select Range” command in MS Excel. So, I changed it again to this:

tell application “Microsoft Excel”
activate
goto “R2C2”
copy object
end tell

I then got this error: “Microsoft Excel got an error: Can’t continue copy object.”

Someone posted a pdf for Applescript for Excel. Reading through it, I found that the “Copy Object” command, which is in the dictionary, is not implemented. Great.

Anybody have an idea what I need to do in order to copy the contents of an Excel cell? This is driving me nuts.

TIA

Kevin

#2

With the release of Office 2004 Microsoft changed the entire AppleScript dictionary for their apps. The good news is that the apps are much more scriptable. so the solution to your problem is as follows:


tell application "Microsoft Excel"
	-- Set up some data so it can be copied
	
	set the formula of cell "b2" to 42
	
	-- now replace the following old code
	
	(*
	Activate
     Select Range "R2C2"
     CopyObject Selection
	*)
	
	
	activate
	copy range range "B2" of the active sheet
	
	-- Now put the value copied from the first cell into
	-- another cell.
	
	paste special range "B4"
end tell

I hope this helps

#3

Jim,

Yes, that is what I was looking for. Thanks. I noticed that they changed the naming structure of the cell from something like R2C2 to B2. Do you know why “range” has to be in that line of code twice?

Actually what I am doing with this script is copying data from an Excel spreadsheet, pasting it to a text document, then importing that text document into a FMP database.

Thanks again,

Kevin

#4

I’m having a similar issue. I simply need to select a cell to look at it, then move on to the next cell by calculation. This is easier to do with the older R1C1 notation, which I understand is supposed to be supported.

Can someone give me a clue how to update my code?

The old code:

			set currentRow to 1
			set currentColumn to 1
			repeat until formula of active cell is ""
				set currentRow to (currentRow + 1)
				set currentCell to ("R" & currentRow & "C" & currentColumn) as text
				select range currentCell
			end repeat

I’ve tried “select range range currentCell” and placing the “formula r1c1” designator in a few places, but all I get are variations of the error Microsoft Excel got an error: The object you are trying to access does not exist. Problem is, it existed just fine in the older versions of Excel. :stuck_out_tongue: I’m guessing a syntax issue, been slogging through the large script this snippet lives in, fixing them one-by-one.

#5

Renewed tinkering and even more pawing through the PDF documentation for Excel 2004 got it working…

			set currentRow to 1
			set currentColumn to 1
			repeat until formula of active cell is ""
				set currentRow to (currentRow + 1)
				activate object cell currentRow of column currentColumn
			end repeat

Looks like I get to re-write all my selection statements…bleh