How do I copy an entire worksheet to another instance of excel?

[Alan]

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,

 

[Jim Thomlinson]

To the best of my knowledge you can't. That is why I never create multiple
instances of Excel. One instance is completely independant of the other and
the two can not interact.

 

[Alan]

To the best of my knowledge you can't. That is why I never create
multiple instances of Excel. One instance is completely independant
of the other and the two can not interact.

Thanks for your quick reply.

If you avoid doing it that way, is there another route I could take?

The reason I was hoping to copy to another instance is that there are
a lot of settings tied down in the original application instance (cell
drag and drop / copy and paste - enough to render the application
fairly useless as an excel application but very safe for the data).

Of course, the best solution would be to move to a different tool
(Access probably), but that would incur a lot of time / effort that we
cannot really justify at this point.

Thanks again!

Alan.

 

[Jim Thomlinson]

If it is just data you have then it is a database that makes the best
solution. Then you can better control the data and keep it away from the
unwashed masses. If that is just not feasable then put the data on a
protected sheet or maybe a very hidden sheet to stop the heathen from messing
up what they were not supposed to touch in the first place. Just a thought...

 

[Jim Cone]

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




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,

 

[Jim Thomlinson]

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.

 

[Peter T]

Hi Alan,

If it's only data you want to copy, maybe adapt this snippet

Sub test()
Dim ws As Worksheet
Dim wb As Workbook
Dim xlAppDest As New Excel.Application
Set ws = ActiveSheet
Set wb = xlAppDest.Workbooks.Add

ws.Range("A1:z1000").Value = 1
With ws.UsedRange
wb.Worksheets(1).Range(.Address()).Value = .Value
End With
xlAppDest.Visible = True

Stop 'have a look

' as this is only a test ...
wb.Close False
Set wb = Nothing
xlAppDest.Quit
Set xlAppDest = Nothing
End Sub

Regards,
Peter T

 

[Alan]

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,

Doh doh doh!

xlAppSource.Workbooks("Main").Worksheet("Data").UsedRange.Copy
xlAppDest.Workbooks(1).Worksheet("Data").Range("A1").Select
xlAppDest.Workbooks(1).Worksheet("Data").Paste

I was getting an error when I had this:

xlAppSource.Workbooks("Main").Worksheet("Data").UsedRange.Copy
xlAppDest.Workbooks(1).Worksheet("Data").Range("A1").Paste

and thought that it was because the destination was in a new instance.
Infact it was just my bad coding!

Thank you to all who contributed. I would still prefer not to have to
copy the cells, but it appears that that is not possible from what has
been posted.

Regards,

Alan.