Access VBA - Dangling Excel Process 1

[kjv1611]

I originally mentioned this over in this thread:
thread705-1483851

The issue is that when this procedure runs, it opens an instance of Excel, and then leaves the instance open when it closes. I narrowed down the possible problem(s) by commenting out sections of the code, and letting it run. When I commented out the TransferSpreadsheet line, the code ran fine, with no dangling Excel process.

Best I can tell, this statement:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, ws.Name, strWbName, True, ws.Name & "$"

Seems to be the remaining issue causing the dangling Excel process.

Is there some place in the TransferSpreadsheet statement that I can reference the appXl object to correct this?

Here is how the object and sub-items are Dimmed earlier in the procedure:

    Dim appXl As Object: Set appXl = CreateObject("Excel.Application")
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
    
    Set wb = appXl.Workbooks.Open(Forms!frmImport!txtXlFile)

[GREEN]'Then later:[/GREEN]
    For Each ws In wb.Worksheets
      [GREEN]'Do stuff[/GREEN]
    Next ws

Any thoughts/hints?

Also, this is currently how I am closing everything out:

ExitSub:[HIGHLIGHT]
    appXl.DisplayAlerts = False
    wb.Close
    appXl.DisplayAlerts = True
    appXl.Quit
    Set appXl = Nothing
    Set wb = Nothing
    Set ws = Nothing[/HIGHLIGHT]
    
    db.Close
    Set db = Nothing
    
    Exit Sub
    
ErrHandle:
    ErrTalk (Forms!frmImport!txtImportID)
    Resume ExitSub
End Sub

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

Usually the TransferSpreadsheet method don't like to deal with open workbooks.
I'd populate an array with the sheets name, close the workbok and then loop the array for the transfer.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

Hmm, that's an interesting way to look at fixing that issue. Sounds like something I would like to do. I'll give it a shot and post back.
Thanks!

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Alright! That worked. I had to do some extra finagling for some reason to get the number of worksheets to build the correctly sized array, but I finally got it knocked out.

That was definitely the trick! And I've been trying to figure this one out for a long time, now!

Thanks a ton!

--

"If to err is human, then I must be some kind of human!" -Me