Copy between Excel applications

[vpager]

I have a VBA routine that opens a new workbook in a new application using the following lines of code:
[VBA]
Set WoApp = CreateObject("Excel.Application")
Set WoBook = WoApp.Workbooks.Add
Set WoSheet = WoBook.Worksheets(1)
[/VBA]

I need to copy a worksheet from the source application into the new workbook. If MyBook is in the same Excel application as the source then this code will do it:
[VBA]Worksheets("mysheet").Copy After:=MyBook.Worksheets(1)[/VBA]

But this does not work for WoBook as it is in another Excel application.

WoBook is recognised by the source. The following code does work:
[VBA]WoSheet.Cells(1, 1) = "hello"[/VBA]

Any suggestions how to do the copying?

[R1C1]

I don't understand why you would want to open another instance of Excel. The running copy can do everything another instance of Excel can. It might cause problems since the new instance of Excel will try to access your Personal.xls file, which is in use by the running copy. I suggest you stay away from calling multiple instances of Excel.

Alan

[vpager]

Opening multiple instances of Excel is not an uncommon practice and is not a problem for Excel, MS Office or Windows. Open Excel through the Start menu and then do it again the same way a couple more times and you will have three separate instances all running concurrently.
For organisational purposes, this can be extremely useful when working with multiple workbooks. The 'Window' menu option only lists the workbooks open in the individual application session.

The code I've used to create the additional application session is straight out of the MS VBA Help. Just type 'createobject' into a VBA module and press F1.

[vpager]

My main source workbook is an extensive VBA application program which builds worksheets in a separate workbook.
Creating the output workbook within the same application session of Excel requires the source VBA program to terminate before the resulting output can be accessed by the user.
Moving the output worksheets to a workbook in a separate Excel application session allows access to this output workbook while the source VBA app remains active in the source application session.


Any suggestions how to use code to copy a worksheet between workbooks in different application sessions?

[Bob Phillips]

I could get Copy to work across two instances, but I could do a direct drop of a range into the other instance

[vba]

Dim XL As Excel.Application
Dim WB As Excel.Workbook
Dim WS As Excel.Worksheet

Set XL = New Excel.Application
Set WB = XL.Workbooks.Add
Set WS = WB.Sheets("Sheet1")
XL.Visible = True
WS.Range("A1").Value = Range("A1").Value

XL.Quit
[/vba]

[vpager]

Hi xld
The problem I have is that the source worksheets include picture shapes and cell contents all aligned and formatted.
The only way to retain all the information exactly the same is to copy the whole worksheet into the new workbook.

I am surprised I cannot find any way to do this.

[rory]

I don't know what the technical reason is, but you can't do it manually either. Nearest thing I can think of would be to copy the sheet to a new workbook, save and close it, then open in the other session and copy it to your workbook.

[vpager]

@rory
Agreed that when you use the manual copy, you are only presented with the option to copy to workbooks that are open in the same instance (or new book). This in itself supports the limitations of the Worksheet.Copy capability.

Whilst the saving option is valid, it defeats the object of what I'm trying to achieve.
These outputs are not always saved by the user. So I am trying to give them access to the output while the source application continues to run.
The option of saving the output to disk and opening in the other instance will create a number of saved files on disk which then have to be considered.
But, as you say, it may be the only option available.

Still disappointed I can't find a sloution to this.
Thanks anyway.