Excel Objects Clashing

[melver]

Hi,

First time poster and appreciate any help I can get.

I am using a Access adp and vba to import and export from sql to excel and visa versa.

Depending on the month some of the exports will take up to 20 minutes to finish. I instantiate the excel objects from access and leave them invisible in the background while they are populated.

The problem I have is when a user opens a different excel spreadhseet to work on, this new excel doc loads into the same object as the excel I am using for the export and causes all kinds of failures.

When I first create the excel object and open the template for export, can I somehow lock this object so that nothing interferes with it on any level except for obviously the access project that is controlling it.

I noticed when I run excel.exe it does not load into the same object space, it is only when opening actual saved excel objects that I get these clashes.

Thanks

 

[chergh]

Does your code use thing like activesheet, selection, activecell etc?

 

[melver]

When I realised what was happening I rewrote as much as I could of the vba to remove references to active workbooks sheets etc.

I substituted selection and active use for named worksheets to get to the correct cells for import and export.

I now mostly get subscript of range errors as the new object does not have sheets named like my templates.

 

[chergh]

I now mostly get subscript of range errors as the new object does not have sheets named like my templates.

What new object? The object you are creating should be the same it's just how you refer to worksheets and ranges that has changed.

 

[melver]

The new object is any excel spreadsheet I open up while my application is running. It could be a spreadsheet that keeps my timesheet info in or any other spreadsheet, as long as I open it from a saved file.

This new workbook adds itself the excel application object I created in my access vba application to allocate my template for export.

To summarise,

My access app runs an invisible template in the background populating from a sql database. Often taking several minutes to run. While I am waiting for this process to finish I go to another spreadsheet to complete my timesheet details for the week. As soon as I open this new spreadsheet the invisible template becomes visible, the new spreadsheet becomes the activesheet and the vba in access controlling the application falls over.

Hope I have clarified my problems.

 

[Access_guy49]

I think this site will give you what you want.. more or less.
http://www.vadriano.com/excel-vb/2007/04/12/how-to-open-excel-files-in-a-new-application-instance/

 

[melver]

That is exactly what I need....

Thanks very much!