INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Close Hidden Instance of Excel

Close Hidden Instance of Excel

(OP)
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?



CODE -->

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:

CODE

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 


RE: Close Hidden Instance of Excel

CODE

If xlApp.Visible = True Then
    ExcelVerifiedClosed = 1
Else
    ExcelVerifiedClosed = 2
    xlApp.Quit
    Set xlApp = Nothing
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.

RE: Close Hidden Instance of Excel

(OP)
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:

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:

CODE

If xlApp.Visible = True Then
    ExcelVerifiedClosed = 1
Else
    ExcelVerifiedClosed = 2
    Set xlApp = GetObject(, "Excel.Application")    
    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.

RE: Close Hidden Instance of Excel

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:

CODE

Dim xlApp As Excel.Application  'Early binding
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") 'Late binding 
    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.

RE: Close Hidden Instance of Excel

(OP)
Thanks Andy. I will look into that.

RE: Close Hidden Instance of Excel

>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.

RE: Close Hidden Instance of Excel

(OP)
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

RE: Close Hidden Instance of Excel

Your code deas some weird stuff, like calling Err.Clear in the no error case. It also does On Error Resume Next 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.

Quote:

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close