Excel 2008 and Applescript

I have been searching and trying to learn to how do what it is that I want to do. I am brand new to scripting. I think my need is simple:

I have two excel files: one has the data I want (the source) the other is where I want to copy selected cells (the target). One would think that the microsoft excel dictionary would support something as basic as copy/paste but, as far as I can tell it doesn’t. Unless I’m missing something. I can find the “copy object” statement but cannot find it’s paste equivalent.

Help? Or is this an unreasonable thing to expect?

Thanks

Here are a variety of ways to copy/paste between worksheets. The script below shows four.
Have you downloaded the Apple Script Reference from http://www.microsoft.com/mac/developers/default.mspx?CTT=PageView&clr=99-21-0&target=4acff5ca-5863-4bb6-9a3b-09d2bc0d1dc71033&srcid=e1dbbe49-e45b-4606-bc00-dc5d3bd2d4601033&ep=7

tell application "Microsoft Excel"
	set sourceRange to get range "A1:A10" of sheet 1 of workbook "Workbook1.xls"
	set destinationRange to get range "B1:B10" of sheet 1 of workbook "Workbook2.xls"
	
	-- fastest 
	set value of destinationRange to value of sourceRange
	
	--Straight copy paste
	copy range sourceRange destination destinationRange
	
	copy range sourceRange
	--copy paste/special values
	paste special destinationRange what paste value
	
	-- copy to selection on destination sheet
	paste worksheet (worksheet object of destinationRange)
	
end tell

Hmmm…I’m trying it and nothing is happening. I’m not getting any error messages (finally) but not getting results either. But this gives me a really good place to continue working from.

thanks so much.

It worked in my Excel 2004. You should change the file names (and the .xls tags) to match your situation.

I have changed the file names to match my specific purpose. For some reason, it is not picking up the correct cell from the source sheet. It’s picking up the equivalent the cell range from the target sheet and copying it to the specified cell in the target sheet. For clarity, I want cell the contents of C20 from the source file to be copied into cell C4 of target sheet. Instead I am getting the contents of cell C20 from target sheet copied into cell C4 of target sheet. So it must be the way I am referencing the files. I’ll keep plugging away.

tell application "Microsoft Excel"
	set sourceBook to workbook "Workbook1.xls"
	
	set destinationBook to workbook "Workbook2.xls"
	
	set sourceRange to get range "C20" of sheet 1 of sourceBook
	set destinationRange to get range "C2" of sheet 1 of destinationBook
	
	-- fastest 
	set value of destinationRange to value of sourceRange
	
	--Straight copy paste
	copy range sourceRange destination destinationRange
	
	
end tell

Getting “Applescript error: Can’t get sheet 1 of missing value.” and “range C20” of this instruction is highlighted set sourceRange to get range “C20” of sheet 1 of sourceBook

Maybe the problem is that it’s Excel 2008. From all the reading I have done in researching my plan, it seems that there are many differences between 2004 and 2008. What works for you just might not work for me. But it seems odd that something this intuitively simple is proving to be so challenging (at least for me).

just edited the above to correct what was highlighted

Are both the workbooks open?
Is the left most sheet of Workbook1 a normal sheet or a chart sheet?

yup - both are open. Not sure what you mean by a normal sheet or a chart sheet.

I give up. I have been defeated. Time to get out of my PJs and join the rest of the world. I hate computers!!

BTW - Thanks mikerickson for all your help - you got on on a forward track.

The error message says, that the reference to the workbook is missing.
Make sure that the names of your workbooks and the literal strings “Workbook1.xls” and “Workbook2.xls” are exactly the same

Hi
Try this

tell application "Microsoft Excel"
	copy range (range "A1" of sheet "Sheet1" of workbook "workbook1") destination range "A5" of sheet 1 of workbook "workbook2"
end tell

I’m using Office 2008
regards
bills