Checking for Open Instance(s) of Excel

kwvh
3StarLounger
Posts: 308
Joined: 24 Feb 2010, 13:41

Checking for Open Instance(s) of Excel

Post by 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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Checking for Open Instance(s) of Excel

Post by 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
Best wishes,
Hans

kwvh
3StarLounger
Posts: 308
Joined: 24 Feb 2010, 13:41

Re: Checking for Open Instance(s) of Excel

Post by 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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Checking for Open Instance(s) of Excel

Post by 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
Best wishes,
Hans

kwvh
3StarLounger
Posts: 308
Joined: 24 Feb 2010, 13:41

Re: Checking for Open Instance(s) of Excel

Post by kwvh »

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

User avatar
Jan Karel Pieterse
Microsoft MVP
Posts: 656
Joined: 24 Jan 2010, 17:51
Status: Microsoft MVP
Location: Weert, The Netherlands

Re: Checking for Open Instance(s) of Excel

Post by 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
...
Regards,

Jan Karel Pieterse
Excel MVP jkp-ads.com

kwvh
3StarLounger
Posts: 308
Joined: 24 Feb 2010, 13:41

Re: Checking for Open Instance(s) of Excel

Post by 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