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

[boblarson]

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:

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

 

[Geek65]

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:

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.

 

[Poet]

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:

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.

 

[Rx_]

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.

 

[Poet]

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.