Ghost Excel Instance when starting Excel using VBA from within Access 2007

Yes, I would have answered sooner if I hadn't been swamped trying to catch up from being gone from work for almost a week (took vacation time to move from an apartment to a house).

You could have shortened up the range part though. You didn't need to use the objXL.Sheets reference since you have a sheet object already there (which is based off of the objXL reference). So you could have used:

Code:
xlSht.Range("K2:O9").Cut Destination:=[B][COLOR=red]xlSht.[/COLOR][/B]Range("A2:E9")
 
Yes, I would have answered sooner if I hadn't been swamped trying to catch up from being gone from work for almost a week (took vacation time to move from an apartment to a house).

You could have shortened up the range part though. You didn't need to use the objXL.Sheets reference since you have a sheet object already there (which is based off of the objXL reference). So you could have used:

Code:
xlSht.Range("K2:O9").Cut Destination:=[B][COLOR=red]xlSht.[/COLOR][/B]Range("A2:E9")

I think I had tried that along the way and it was not happy which is why I ended up with the entire objXL.sheets reference. I'll try it when I am cleaning up the code.
 
Mr. B has just mentioned the most likely culprit. It would seem that the xlSht object creates an orphaned Excel instance. I would also include set those objects to Nothing:
Code:
Private Sub CMDtest_Click()

    Dim objXL As Object
    Dim xlWB As Object
    Dim xlSht As Object
    
    Set objXL = CreateObject("Excel.Application")
    Set xlWB = objXL.Workbooks.Open("C:\Apps\rptCSMPengineering2. xls")
    '*********
    Set xlSht = objXL.Sheets("TSSRSheet1")
    xlSht.Range("K2:O9").Cut Destination:=Range("A2:E9")
    
    '**************
    MsgBox "done"
    
    xlWB.Close True
    objXL.Quit
    
[COLOR=Blue]    Set xlSht = Nothing
    Set xlWB = Nothing
    Set xlobj = Nothing[/COLOR]

End Sub
Some would argue this is not a necessity ;)

Worked for my issue. Thanks.
 
This brings back memories of the same people helping me with and Excel orphan instance.
I use the MR Excel site to get help for complex Excel formulas.
The SQL Server Central for the back-end support for Access Linked tables.
This site (bar none) is the best for Office Automation using VBA for MS Access developers.

I suggest a new category for MSOffice VBA Automation or something along that line.

Using Excel with VBA Office Automation would be very useful.
 
This brings back memories of the same people helping me with and Excel orphan instance.
I use the MR Excel site to get help for complex Excel formulas.
The SQL Server Central for the back-end support for Access Linked tables.
This site (bar none) is the best for Office Automation using VBA for MS Access developers.

I suggest a new category for MSOffice VBA Automation or something along that line.

Using Excel with VBA Office Automation would be very useful.

While I'm a lurker/newcomer, I completely agree. 100% of my job is doing automation (read: unattended, repetitive tasks), which is very different from regular VBA routines.
 

Users who are viewing this thread

Back
Top Bottom