It sounds like you have two Excel workbooks. In one of those workbooks there is a worksheet. You want a copy of that sheet to be put in the second workbook.
Do you want a new sheet to be created or do you want the source sheet to be copied to an existing sheet in the second book?
This will copy the source sheet as a new sheet at the end of the worksheets of Workbook2.xls.
tell application "Microsoft Excel"
set sourceSheet to sheet "Sheet1" of workbook "Workbook1.xls"
set destinationBook to workbook "Workbook2.xls"
tell destinationBook
set lastSheetOfDBook to sheet (count of sheets)
end tell
copy worksheet sourceSheet after lastSheetOfDBook
end tell
i apologize for being vague. i’ve been wrestling with this problem i kind of forgot other people wouldn’t already know my pain.
i have two existing workbooks (let’s call them “START” and “FINISH”). i need to get a worksheet from START to FINISH.
the thread that was referenced takes an existing workbook’s worksheet and moves it to a new workbook. when i try adding any sort of reference to where i want it copied to to the end of the “copy worksheet active sheet” line it gets all mad at me.
also, i’m not going to know the names of the workbooks or worksheets beforehand.
when i take your script and swap out the filenames for variables, it gets all wonky on me:
set theSKUFile to choose file with prompt "SELECT THE SKU LIST:"
set theContentFiles to choose file with prompt "SELECT THE CONTENT FILES:" with multiple selections allowed
repeat with HereWeGo from 1 to count of items in theContentFiles
tell application "Microsoft Excel"
open theSKUFile
set sourceSheet to worksheet 1 of workbook theSKUFile
set destinationBook to workbook (item HereWeGo of theContentFiles)
open destinationBook
tell destinationBook
activate
set lastSheetOfDBook to sheet (count of sheets)
copy worksheet sourceSheet after lastSheetOfDBook
end tell
end tell
end repeat
this results in the worksheet in the workbook theSKUFile is referencing being duplicated in itself.
all the variables are getting the values they need, it just won’t cooperate.
The problem that I had with that script is that theSKUFile is a file not a workbook.
Try this
set theSKUFile to choose file with prompt "SELECT THE SKU LIST:"
set theContentFiles to choose file with prompt "SELECT THE CONTENT FILES:" with multiple selections allowed
tell application "Microsoft Excel"
open theSKUFile
set sourceSheet to worksheet 1 of workbook (name of active workbook)
repeat with HereWeGo in theContentFiles
open HereWeGo as alias
tell active workbook
set lastSheetOfDBook to sheet (count of sheets)
end tell
copy worksheet sourceSheet after lastSheetOfDBook
end repeat
end tell
i’m just such a noob that i’m not really seeing why your’s works. this is my first stab at scripting Excel, up to now i’ve worked exclusively with inDesign and the phrasing of commands is completely different.
is it because theSKUFile only contains a reference to one file and the HereWeGo variable is rolling through a list of files?
(This is where I’m not sure and would appreciate correction from the knowlegable if I’m wrong.)
theSKUFile is an alias, whose coersion to text returns a complete path e.g. “Macintosh HD:Users:…:Workbook1.xls”
The Excel object model handles workbooks by their name , e.g. the pure text value “Workbook1.xls”
So, the statement “workbook theSKUFile” would fail.
It seems as if each item in theContentsFile (the various HereWeGo’s) is text (not an alias) and so the “as alias” has to be added to coerse the text to an alias so the open command works properly.
An alternate approach might be to extract the workbook’s name from the aliases/strings and use Excel’s Open Workbook command, instead of AppleScript’s Open, but I’m not experienced enough to be confident about changing a script that is working.
Furthermore, AppleScript variables are assigned ByRef rather than ByVal (using terms from VBA).
If you have a workbook with two sheets, running this script
tell application "Microsoft Excel"
activate object sheet "Sheet1"
set mySheet to active sheet
--set mySheet to get active sheet
--copy active sheet to mySheet
set myFirstName to name of mySheet
activate object sheet "Sheet2"
set mySecondname to name of mySheet
end tell
display dialog myFirstName & linefeed & mySecondName
will return
Sheet1
Sheet2
in all those variations of setting the variable mySheet. When Sheet2 becomes the active sheet, mySheet then refers to Sheet2.
I came up with, and used in your script, this syntax to fix the value of mySheet
tell application "Microsoft Excel"
activate object sheet "Sheet1"
set mySheet to sheet (name of active sheet)
set myFirstName to name of mySheet
activate object sheet "Sheet2"
set mySecondname to name of mySheet
end tell
display dialog myFirstName & linefeed & mySecondName
