[AccessD] Problem with Access VBA makes Excel Orphan

Edward,

 I always open Excel files this way...

Function Excel_OpenWorkBookHidden(Path As String, Optional UpdateLinks As Boolean = False, Optional Password As String = "") As Excel.Application
Dim xlObj As Excel.Application

    On Error GoTo Excel_OpenWorkBookHidden_err
        Set xlObj = CreateObject("Excel.Application")
        xlObj.Workbooks.Open Path, UpdateLinks, , , Password
        Set Excel_OpenWorkBookHidden = xlObj
Excel_OpenWorkBookHidden_exit:
    Exit Function
Excel_OpenWorkBookHidden_err:
    'Insert your error reporting routine here
    '
    Set Excel_OpenWorkBookHidden = Nothing
    Resume Excel_OpenWorkBookHidden_exit
End Function

Example usage:

Dim xLAp as Excel.Application

	Set xLAp =  Excel_OpenWorkBookHidden("X:\SomePath\SomeFile.xls")

And if you want the workbook visible...

Function Excel_OpenWorkBook(Path As String, Optional UpdateLinks As Boolean = False, Optional Password As String = "") As Excel.Application
Dim xlObj As Excel.Application
    On Error GoTo Excel_OpenWorkBook_err

    Set xlObj = Excel_OpenWorkBookHidden(Path, UpdateLinks, Password)

    If xlObj.Name > "" Then xlObj.Visible = True

    Set Excel_OpenWorkBook = xlObj
Excel_OpenWorkBook_exit:
    Exit Function
Excel_OpenWorkBook_err:
    'Insert your error reporting routine here
    '
    Set Excel_OpenWorkBook = Nothing
    Resume Excel_OpenWorkBook_exit
End Function


Then to close an Excel file opened with the above routines...

Typically I work with worksheet objects, not workbooks, so somewhere in my code after opening an Excel file there will be one or more 

Dim xlWs as Excel.WorkSheet

Set xlWs = xlQb.Sheets("Data")
...
And then to close everything down I use

Set xlWs = Nothing

Excel_CloseWorkBook xlWs

The code for Excel_CloseWorkBook is...

Sub Excel_CloseWorkBook(xlApp As Excel.Application, Optional bSaveChanges As Boolean = False)
Dim wb As Excel.Workbook
    On Error Resume Next
    If xlApp.Name > "" Then
    End If
    If Err.Number <> 0 Then Exit Sub
    On Error GoTo 0
    For Each wb In xlApp.Workbooks             'Close all open workbooks
        wb.Close bSaveChanges
    Next wb
    xlApp.UserControl = False
    Set xlApp = Nothing
End Sub

The line "xlApp.UserControl = False" seems to be crucial to the process (though I don't remember where I read about it) the property is supposed to be False for programmatically opened files, but it seems that setting it explicitly before closing seems to help.

I wonder if your code between the two lines

    Set ExcelwBook = ExcelApp.Workbooks.Add
    . . .
    ExcelApp.DisplayAlerts = False

Is manipulating any worksheet objects that are not then set to Nothing?

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Edward Zuris
Sent: Friday, November 13, 2009 2:58 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Problem with Access VBA makes Excel Orphan


 Using the below code from an Access 2000 VBA  application, once running these commands there  is still an Excel job running in task manager.
 
 The Excel orphan makes it impossible to run the  VBA application recessively.
 
 One clue is Excel will finally shut down if  the Access 2000 VBA application / database  is shutdown.
 
 Any way to tell Excel to just shut down ?
 
 Is there a work-a-round for this problem ?
 
 Thanks.
 
 Sincerely,
 Edward Zuris.

 -
 
 =======================================================
 ====== .Quit code not closing Excel Application ======
 
    Dim ExcelApp            As Excel.Application
    Dim ExcelwBook          As Excel.Workbook
    . . .
    Set ExcelApp = New Excel.Application
    Set ExcelwBook = ExcelApp.Workbooks.Add
    . . .
    ExcelApp.DisplayAlerts = False
    ExcelwBook.SaveAs zPath & zName
    ExcelApp.DisplayAlerts = True
 
    ExcelwBook.Save
    ExcelwBook.Close False
    ExcelApp.Quit
 
    Set ExcelwBook = Nothing
    Set ExcelApp = Nothing
 
 =======================================================
 =======================================================
 -
 
 Yet a simpler VBA applications doesn't have  this issue using the following.
 
 This also fails using the complex VBA program.
 
 Any ideas ?
 
 =======================================================
 = .Quit code Working closing simple Excel Application =
 
    ExcelwBook.Application.DisplayAlerts = False
    ExcelwBook.SaveAs zPath & zName
    ExcelwBook.Application.DisplayAlerts = True
 
    ExcelwBook.Application.Quit
 
    ExcelApp.Quit
    
    Set ExcelwBook = Nothing
    Set ExcelApp = Nothing
 
 
 =======================================================
 =======================================================
 
 
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com