Running excel vba program for second time is causing errors..

[bkalimili]

excel program when run second time from MS Access - fails giving object failed or method global row failed etc different error each time while stepping through code...
I think the cause is because of instance of excel is still seen in task manager process section.

Not able to figure out - what is wrong letting the excel still hang and not completely close out.

here is the code.

 

[dkinley]

It might be because of the way you are instantiating Excel .. where even when you close the object it doesn't close and the only way to resolve it is by closing Access. The resolution was to alter the way Excel was called so the close method would work consistently.

I saw a post not too long ago talking about this very subject .. you can do a search probably when the last 3 months.

-dk

 

[SOS]

I do believe I found the problem.

The problem is this line (and any others like it):

For Each cell In Intersect(ActiveSheet.Rows(1), ActiveSheet.UsedRange)

You are using ActiveSheet which is not tied to the current instantiated Excel application object. Because of that Access instantiates another, hidden, instance which you cannot refer to nor can you delete or kill except to either close Access or go to the Task Manager.

Make sure that ALL code ties to the instantiated Excel object.

 

[John.Woody]

I had the same problem try removing

Set oapp = CreateObject("Excel.Application")

then modify to read

Set xlWkb = Excel.Application.Workbooks.Open("C:\temp\TReport.xls")

HTH

 

[SOS]

I had the same problem try removing

Set oapp = CreateObject("Excel.Application")

then modify to read

Set xlWkb = Excel.Application.Workbooks.Open("C:\temp\TReport.xls")

HTH

Nope, Sorry John it is because as I said, it is because the code that isn't tied to the instantiated object is being used.

 

[bhein]

I can confirm what SOS said. I had a similar problem and solved it by making sure that ALL Excel related code ties to the instantiated Excel object.