Copy multiple sheets from an excel workbook into a new workbook

Scripting Forums AppleScript | Mac OS X
,
#1

So,
This is a little silly and I’m sure it’s a simple procedure, but I can’t seem to figure it out…

I know you can use Applescript to move a single worksheet into a new Excel workbook using the copy worksheet command, but how would you go about selecting multiple sheets from one workbook and copying it into another.

I can create my new workbook using the “copy worksheet” command without supplying a “after sheet 3” but getting a second worksheet into that same new workbook has proved more challenging that I thought!

I can’t seem to be able to specify a destination in the “copy worksheet” command that exists outside of the current workbook. Everything I’ve tried results in an error “The object you are trying to access does not exist”

here’s my code so far… admittedly, it’s not much

set invoiceList to {"Deposit Inv", "Final Inv", "Single Inv", "Misc Inv", "Tru 50 Inv"}
set myInvoice to choose from list invoiceList with prompt "Which invoice would you like to email as a PDF?"
set myInvoice to myInvoice as string
tell application "Microsoft Excel"
	activate
	save active workbook
	if exists sheet myInvoice in active workbook then
		copy worksheet sheet myInvoice
                set active workbook to newBook
	end if
	activate previous active window
	if exists sheet "Reconciliation Sheet" in active workbook then
		duplicate worksheet sheet "Reconciliation Sheet" to workbook newBook
	end if
	return
	activate previous active window
	close without saving
end tell

any help is much appreciated!

Joel

#2

I changed things to match my test workbook, but this worked for me.
I also qualified things so that activating and selecting isn’t needed.
I also used try rather than explicit testing for a sheet’s existance.

set invoiceList to {"Invoice 1", "invoice 2"}
set myInvoice to choose from list invoiceList with prompt "Which invoice would you like to email as a PDF?"
set myInvoice to myInvoice as string

tell application "Microsoft Excel"
	activate
	set sourceWorkbook to workbook (name of active workbook)
	
	try
		copy worksheet sheet myInvoice of sourceWorkbook
		set newSheet to sheet (name of active sheet) of active workbook
	end try
	try
		copy worksheet sheet "Reconciliation Sheet" of sourceWorkbook after newSheet
	end try
	name of newSheet
end tell

The key to your problem was:
a) the original “set active workbook to newBook” had things in the wrong order.
b) the Reconciliation sheet’s copy command specifies the sheet after which it is to be copied.

I learned that the intuative

set sourceWorkbook to active workbook

results in the situation that when a new book becomes active, sourceWorkbook will then point to that other sheet, hence the construction

set sourceWorkbook to workbook (name of active workbook)

Similarly Set newSheet to active sheet would have problems.

#3

Hi Mike,
Your script works like a charm! I really appreciate the help! I suppose if you think about it, either execution of the command “set “theWorkbook” to active workbook,” makes sense; the way it actually works, or the way I thought it should work!
Oh well, one of those things I’ll be able to remember the next time it comes up.
Thanks again for your help!
Joel

#4

Glad to help.
But I disagree that the ByRef assignment of active workbook to a variable makes sense.

Which means nothing, AppleScript/Excel work the way that they do, even if it doesn’t make sense.

On the other hand, if improvements weren’t possible, then what would professional programmers do with themselves?
Go into politics?
:confused:
Hmm…further thought is needed, perhaps perfect software is desirable.
(What will they do when they find out that citizens are less dependable than users?)