Close Hidden Instance of Excel

[mmogul]

I use the following code to determine if there is a hidden instance of Excel running. I first determine if there is an active Excel instance. If so, I then determine if the instance is hidden.

If I identify that there is an active hidden instance of Excel (this function returns a value of 2), how can I close it?

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Const Err_App_Notrunning As Long = 429

On Error GoTo ErrorHandling:


If isExcelOpen Then
    
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err = Err_App_Notrunning Then
      Set xlApp = New Excel.Application
    End If
    
    If xlApp.Visible = True Then
      ExcelVerifiedClosed = 1
    Else
      ExcelVerifiedClosed = 2
    End If
    

    
Else
    ExcelVerifiedClosed = 0
    
End If


ExitProc:
  Exit Function

ErrorHandling:
  Select Case Err.Number
    Case Else
        Call ErrorHandler("ExcelVerifiedClosed", Err.Number, Err.Description)
        GoTo ExitProc
  End Select

Here is the code to test that Excel is running:

Public Function isExcelOpen() As Boolean
Dim xlApp As Object

On Error GoTo ErrorHandling:

  'Initialize Return and variables
  isExcelOpen = False
  
  On Error Resume Next
  
    Set xlApp = GetObject(, "Excel.Application")
  
    If Err.Number = 0 Then 'excel open
        isExcelOpen = True
        Err.Clear
    Else
        xlApp.Quit
        Set xlApp = Nothing
    End If

ExitProc:
  Exit Function

ErrorHandling:
  Select Case Err.Number
    Case Else
        Call ErrorHandler(msModuleName & ".isExcelOpen", Err.Number, Err.Description)
        GoTo ExitProc
  End Select
End Function

 

[Andrzejek]

If xlApp.Visible = True Then
    ExcelVerifiedClosed = 1
Else
    ExcelVerifiedClosed = 2[red]
    xlApp.Quit
    Set xlApp = Nothing[/red]
End If

Excel may ask you: "Do you want to save it?" before closing

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[mmogul]

Thanks Andy - Apologies for the delayed response. I had tried something just like that but I encounter the following problems:

If I use this code:

If xlApp.Visible = True Then
    ExcelVerifiedClosed = 1
Else
    ExcelVerifiedClosed = 2
    xlApp.Quit
    Set xlApp = Nothing
End If

I get the error:
"Object variable or with block variable not set."

Since this appeared to me that the application was trying to close Excel but did not have a reference to it, I changed the code to:

If xlApp.Visible = True Then
    ExcelVerifiedClosed = 1
Else
    ExcelVerifiedClosed = 2
    [COLOR=#3465A4]Set xlApp = GetObject(, "Excel.Application")[/color]    
    xlApp.Quit
    Set xlApp = Nothing
End If

This runs without errors but does not close the instance of Excel that is open.

Notably, if I select from the VBA menu "Run / Reset" after I have run the code above, the instance of Excel that shows in the Task Manager closes.

I'd appreciate any insights to this issue.

 

[Andrzejek]

I don't know if that is related, but you mix early and late binding in your code and assign Excel Application to an Excel object:

Dim [blue]xlApp[/blue] As Excel.Application  [green]'Early binding[/green]
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Const Err_App_Notrunning As Long = 429

On Error GoTo ErrorHandling:


If isExcelOpen Then
    
    On Error Resume Next[blue]
    Set xlApp = GetObject(, "Excel.Application")[/blue] [green]'Late binding [/green]
    If Err = Err_App_Notrunning Then
      Set xlApp = New Excel.Application
    End If
    
    If xlApp.Visible = True Then
      ExcelVerifiedClosed = 1
    Else
      ExcelVerifiedClosed = 2
    End If

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[mmogul]

Thanks Andy. I will look into that.

 

[strongm]

>Set xlApp = GetObject(, "Excel.Application") 'Late binding

Just to be clear, in the referenced instance (the OPs first block of code) it is not late binding, and so the OP is not mixing early and late binding. It is only late binding if xlApp is declared as Object (as in the second block of code). One cheap (but not always 100% reliable) way of finding out in the IDE if you have got early or late binding is to see whether you get intellisense for the methods and properties for the object; if you get them then you are early binding.

 

[mmogul]

Strongm - Thanks for your response (sorry for the my delayed response -- but it is summer). Anyway - given your comment that I am not mixing early and late binding, do you have any thoughts why my code is not closing the hidden instance of excel?

Thanks.

Mark

 

[dilettante]

Your code deas some weird stuff, like calling [tt]Err.Clear[/tt] in the no error case. It also does [tt]On Error Resume Next[/tt] right after setting an error handler.

No telling what else your code looks like, these are scary enough oodities.


But Automating Office applications can be full of subtle gotchas unless you are hypervigilant. You have to expect a lot of snags like this. None of it was designed for unattended use in code anyway.

See INFO: Error or Unexpected Behavior with Office Automation When You Use Early Binding in Visual Basic which discusses issues far beyond the obvious ones. For example how inadvertant Global references can be held by the client program, preventing the Office app from terminating.

Application Does Not Shut Down: Unqualified calls in VB set a hidden variable reference to the Office Global object. Therefore, an Office application may fail to shut down, even if you call the Quit method, because it still has outstanding references. This behavior typically occurs on the first instance that the code uses. However, complex programs may cause it to occur with multiple instances.