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.
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.
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
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
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