Checking for Open Instance(s) of Excel

[kwvh]

I use VBA from Access to open instances of Excel and do some formatting, then save and close the spreadsheet, and continue other things. There are times when the user may have an Excel spreadsheet or spreadsheets open before opening and executing the Access program which periodically result in errors in the VBA code. My question is "Is there a method to test for any open instances of Excel, and if there are any open, prompt the user to close them before continuing?"

Thanks for any ideas.

Respectfully,

Ken

[HansV]

You can use code like this:

Code: Select all

Dim xlApp As Object
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
On Error GoTo 0
If xlApp Is Nothing Then
  ' Excel is not running
Else
  MsgBox "Please close all instances of Excel before continuing!", vbExclamation
  Set xlApp = Nothing
End If

If you have set a reference to the Microsoft Excel n.0 Object Library, you can change

Dim xlApp As Object

to

Dim xlApp As Excel.Application

[kwvh]

Hans,

THANKS! Worked perfectly.
What does On Error GoTo 0 mean/do as opposed to going to module error handler? Below is what I used, and it works marvelously.

Code: Select all

Dim xlApp As Excel.Application
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
On Error GoTo 0
'On Error GoTo Err_cmd_ImportAndAllocate_Click
If xlApp Is Nothing Then
  ' Excel is not running
Else
Call MsgBox("Please close all instances of Excel before continuing!", vbExclamation, "Excel Files Open!")
  Set xlApp = Nothing
  Exit Sub
End If

[HansV]

I just used

On Error GoTo 0

because I posted a snippet of code without a real error handler. This means that any error will display a message box with End and Debug buttons. In your actual code, you should use

On Error Goto Err_cmd_ImportAndAllocate_Click

[kwvh]

Thanks! Without fail, I always learn a lot from your posts and replies.

[Jan Karel Pieterse]

As an alternative, you could just fire up a new instance of Excel, just for your application:

Dim oXL As Excel.Application
Set oXL = New Excel.Application
oXL.Visible = True
...

[kwvh]

Jan,

Thanks for the info. I do start up a new instance each time. However, I developed it in Access 2003, and the users are using 2007 and a few 2003. The issue occurs most often in 2007, but does occasionally pop up in 2003. I haven't identified a specific pattern to date, but to avoid issues, I ask that they close all Excel files before running. Sometimes they forget, and may even have the Excel file open that I am trying to create and manipulate.

So by testing for Excel instance and prompting user, my hope is the issue will go away altogether. LOL

Thanks again for your reply!

Ken