Copy worksheet from one instance of Excel to another

[bdmangum]

I know that typical copy methods will fail since I'm running two instances of Excel. The problem is that I have to run two seperate applications of Excel, however I need to copy a worksheet from one instance to the other.

One of the applications is running a database I built for some people. It has one sheet which stores the bulk of the data. A required functionality of this databse is to be able to save the one worksheet in a seperate workbook which does not contain macros.

Due to the nature of the database, it has to run in it's own instance of Excel. Creating the new workbook and saving it where desired is simple, but I can't figure out how to copy over the worksheet.

Does anyone know a good bypass to this problem? Or perhaps a method for exporting a single worksheet into a new workbook?

TIA,
BD

 

[SkipVought]

Hi,

"Due to the nature of the database, it has to run in it's own instance of Excel."

Care to elaborate on this requirement? I do not understand why.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bdmangum]

It's due to some of the code I'm using to run the database. The database functions from userforms with the entire Excel interface hidden from the user. I'm using some code to make it appear to the user as though the database is a stand-alone program. The code (and some other coding) used to accomplish that won't allow other workbooks to open in the same instance of Excel while a form is loaded. I thus had to create code where any time a user opens a new Excel workbook, it redirects it to another application instance.

The user calls the tool to create the new workbook from a user form, thus I can't put the new workbook in the active application.

 

[SkipVought]

'The code (and some other coding) used to accomplish that won't allow other workbooks to open in the same instance of Excel while a form is loaded. "

Check out the Modeless property for the Userform.Show method.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bdmangum]

Modeless won't work with the setup I'm running. The form is submitted to the computer be operated as a stand-alone window which allows the user to treat the form the same way they would treat any other window. It gives them the ability to minimize/resize along with several other settings I added in. In order to make all those settings work together, it can't be run as modeless.

Hence, my problem of having to create a new instance of Excel in which to place the new workbook. I created the new instance and new workbook, but the question now is how to copy the worksheet to this new workbook.

 

[SkipVought]

I have never done this, but try using the CreateObject to create a new Excel Application object and the GetObject method to get the other Excel Worbook. Check VBA Help for some good documentation and code.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]