Copy contents from one excel file to another using Applescript

Question

I am trying to copy contents from a source excel file after editing a few rows and paste that into a new workbook. Tried the following code, but it doesn't copy the contents or show any error. Please let me know what I am doing wrong, I am new to applescripts.

tell application "Microsoft Excel"

open workbook workbook file name sourcefile

replace (range "A:A" of worksheet "Sheet1") what "US" replacement "DA"

save active workbook

tell active sheet

    tell used range

        set rc to count of rows

    end tell

    set src to range ("A1:D" & rc)

end tell

set newbook to make new workbook

set update remote references of newbook to true

save workbook as newbook filename targetfilename

tell active sheet

    set dst to range ("A1:D" & rc)

end tell

activate

copy range src destination dst

end tell

Answer 1

Using "active sheet" is your problem. Your sheet is copying from the active sheet to the active sheet, which at the time you send the copy command, are the blank cells in A1:D5 of the new workbook it created.

Change the first 'Tell active sheet' section to:

tell workbook sourcefile to tell worksheet "Sheet1"
    tell used range
        set rc to count of rows
    end tell
    set src to range ("A1:D" & rc)
end tell

For sanity's sake, I would also change the second 'tell active sheet' to:

tell workbook targetfilename to tell worksheet 1
    set dst to range ("A1:D" & rc)
end tell

I made these changes and your script worked for me.