Excel Instance Stays Open

[mmogul]

I am adding a worksheet to an existing workbook from Access. But when I run this procedure and close Excel through the vba code, an instance of Excel is still running if I look at the task manager processes. I realize that this probably has something to do with how automation based variables are declared but I cannot seem to determine the issue. Perhaps you can help. Here is the code - I have simplified it for the purpose of stating this issue.

Private Sub test_Click()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook

Dim sDestinationFile As String
sDestinationFile = "C:\Reports\Allocation\Q3_2013\PandL_V5_GSOU_Q3_2013.xlsx"

                      Call OpenCloseExcel(xlApp, True)
                              Set xlWB = xlApp.Workbooks.Open(sDestinationFile)
                                Call AddAsLastWorksheet(xlWB, "Rules")
                       xlWB.Save
                      xlWB.Close
                      Call OpenCloseExcel(xlApp, False)

End Sub

Public Sub OpenCloseExcel(xlApp As Excel.Application, bOpen As Boolean)
 '** When passed a value of True into bOpen, then this sub opens an instance of Excel
'** When passed a value of False into bOpen, then this closes an instance of Excel

Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
 Dim bExcelWasOpen As Boolean
        
If bOpen Then
        On Error Resume Next
        Set xlApp = GetObject(, "Excel.Application")
        If Err.Number <> 0 Then
            'Could not get instance, so create a new one
             Err.Clear
            Set xlApp = CreateObject("Excel.Application")
            bExcelWasOpen = False
            
        Else ' could get open instance
            bExcelWasOpen = True
             
        End If
          '** If this is in testmode, then keep excel visible
        
        If GetTblzAdmin("testmode") Then
            xlApp.Visible = True
        Else
             xlApp.Visible = False
        End If
Else
    xlApp.Quit
    Set xlApp = Nothing

End If

End Sub

Public Function AddAsLastWorksheet(xlWB As Workbook, sWorksheetName As String)
 '-----------------------------------------------------------------
'Purpose: Adds a worksheet at the end of the workbook with the passed in worksheet name
'

On Error GoTo ErrorHandling:
With xlWB
    .Sheets.Add after:=Worksheets(Worksheets.Count)
    .Sheets(Sheets.Count).Name = sWorksheetName

End With
ExitProc:
  Exit Function

ErrorHandling:
        GoTo ExitProc
End Select

End Function

 

[PHV]

.Sheets.Add after:=[!].[/!]Worksheets([!].[/!]Worksheets.Count)
.Sheets([!].[/!]Sheets.Count).Name = sWorksheetName

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

 

[SkipVought]

hi,

Have you STEPPED thru the process line by line?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

You might try something like this in the open/close procedure, rather than the bOpen variable.

    If xlApp Is Nothing Then
        On Error Resume Next
        Set xlApp = GetObject(, "Excel.Application")
        If Err.Number <> 0 Then
            'Could not get instance, so create a new one
             Err.Clear
            Set xlApp = CreateObject("Excel.Application")
            bExcelWasOpen = False
            
        Else ' could get open instance
            bExcelWasOpen = True
             
        End If
          '** If this is in testmode, then keep excel visible
        
        If GetTblzAdmin("testmode") Then
            xlApp.Visible = True
        Else
             xlApp.Visible = False
        End If
    Else
        xlApp.Quit
        Set xlApp = Nothing
    End If

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[mmogul]

PHV - I knew it had something to do with how I used the automation variables -- but I just couldn't see what you saw. Thanks!!!