The thing to worry about here is the need to update links (not guaranteed but
possible) and performance (It will take a moment to save and open). Otherwise
it is a possible solution.
--
HTH...

Jim Thomlinson


"Jim Cone" wrote:

Alan (and Jim),

This might not meet your needs, but you can save
a copy of your workbook under another name and
then open it in a new instance of Excel...

'----------------------
Sub TransferWorkbook()
Dim strPath As String
Dim strName As String
Dim xlApp As Excel.Application

strPath = "C:\Documents and Settings\user\My Documents\Excel Files\"
strName = "File Name.xls"
Workbooks.Open strPath & strName
ActiveWorkbook.SaveCopyAs strPath & "File Name_new.xls"

Set xlApp = New Excel.Application
xlApp.Visible = True
xlApp.Workbooks.Open strPath & "File Name_new.xls"
Set xlApp = Nothing
End Sub
'----------------------------

Regards,
Jim Cone
San Francisco, USA



"Alan" wrote in message
...

Hi All,
I have a worksheet in once instance of excel (xlAppSource) that I need
to programmatically copy to another instance of excel (xlAppDest).
I can copy it to a new workbook within xlAppSource, but I really need
it in a different instance (since many application level settings are
tied down in source, but the user can play to their heart's content in
the destination instance).
Something like this:

I have:

xlAppSource.Workbooks("Main").Worksheet("Data")

I would like to copy that worksheet so that the copy is:

xlAppDest.Workbooks(1).Worksheet("Data")


The problem is that when I try to use the paste method I get an error,
and if I use the PasteSpecial method I just get a picture of the
worksheet (which is only partial anyway).
I could do it cell by cell looping through the entire usedrange, but
that takes ages (we are talking about 30 columns by 6000 rows ~
180,000 cells) and the users would not regard that as a god solution!
Is it possible to copy an entire worksheet across to another instance?
Thanks,