Determining whether Excel is running

  • Thread starter Thread starter Bill Murphy
  • Start date Start date
B

Bill Murphy

I'm using the following code (from the help file VBAXL10.chm) within Access
to determine whether Excel was running when I first opened the workbook in
VBA:

ExcelRunning = IsExcelRunning()

If Not ExcelRunning Then
Set objExcel = CreateObject("Excel.Application")
Else
Set objExcel = GetObject(, "Excel.Application")
End If

If Excel was running I leave it open, otherwise I quit Excel:

Function IsExcelRunning() As Boolean

' To use a pre-existing instance of Microsoft Excel with Automation, use
the GetObject function
' and specify "Excel.Application" as the Class type. If an instance of
Microsoft Excel already exists,
' the GetObject function will return a reference to the instance. If an
instance of Microsoft Excel
' does not already exist, your code will cause a trappable run-time
error, and you can use the
' CreateObject function to create one.

' You can use the following function to determine if an instance of
Microsoft Excel is running.

Dim xlApp As Excel.Application
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
IsExcelRunning = (Err.Number = 0)
Set xlApp = Nothing
Err.Clear

End Function

This function sometimes returns true, even if Excel was not running when it
was called. Is there a way to fix this code, or is there a better way to
make this determination?

Bill
 
Doug,

This function returns true when I run it from the immediate window for both
Word and Excel, although I can see from my task bar and from Task Manager
that neither of them is running. Could there be something else in my
Windows 2000 configuration that is confusing the issue for this function and
for the IsExcelRunning function below?

Bill
 
Sorry, I don't know. That function has always worked well for me, but then I
don't use Win2000.
 
Bill,

I just re-read your original post, and saw you were using early binding,
where my suggestion used late binding. Notwithstanding, I've never had the
function return True when Excel isn't running, and I don't see how it could.
Under what circumstances does it do so?

Also, instead of using:
Set objExcel = CreateObject("Excel.Application")
I'd be inclined to use:
Set objExcel = New Excel.Application

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Bill Murphy said:
Doug,

This function returns true when I run it from the immediate window for
both
Word and Excel, although I can see from my task bar and from Task Manager
that neither of them is running. Could there be something else in my
Windows 2000 configuration that is confusing the issue for this function
and
for the IsExcelRunning function below?

Bill
Click to expand...

In your Task Manager did you also check out the processes tab? If you are
running code like you posted, you could easily start a hidden instance of
Excel but not notice it in the Task Manager. Try setting the .visible
property of the application object to true like this:

Set xlApp = GetObject(, "Excel.Application")
If Err.Number=0 Then
IsExcelRunning=True
xlApp.Visible=True
End If
Set xlApp = Nothing

In general though, you should use some form of error handling to ensure your
code does not inadvertently leave a hidden instance running which you
created and have left abandoned.
 
Eric,

I believe you have answered my question. There was an Excel.exe process
running, even though no task was visible in Task Manager or on my task bar.
This could have resulted from an abnormal end to my program, which is still
in the testing mode. Is there a way in VBA to detect this hidden process
and kill it?

Bill
 
Bill,

<<Is there a way in VBA to detect this hidden process and kill it?>>
Why would you want to? If there's an instance running, you're going to use
it anyway. The trick is to detect whether it was running before you got
there. If so, can it be legitimately shutdown when you've finished with it,
or is the user (or another process) using it (in which case, you can't kill
it off)?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Graham R Seach said:
Bill,

<<Is there a way in VBA to detect this hidden process and kill it?>>
Why would you want to? If there's an instance running, you're going to use
it anyway. The trick is to detect whether it was running before you got
there. If so, can it be legitimately shutdown when you've finished with
it, or is the user (or another process) using it (in which case, you can't
kill it off)?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
Click to expand...




Here is an example of some error handling which calls xlApp.Quit if an error
occurs (e.g. trying to open a non-existant workbook) but only if the
instance of Excel was one that the code created - ie Excel was not already
running:

Sub OpenWorkbook()

On Error GoTo Err_Handler

Dim xlApp As Object
Dim xlBook As Object
Dim blnError As Boolean
Dim blnNewApp As Boolean

' Switch off normal error handling as we
' attempt to use ruuning instance of Excel
' If Excel is not running, we try to start an instance
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Err.Clear
Set xlApp = CreateObject("Excel.Application")
If Err.Number <> 0 Then
blnError = True
MsgBox "Error starting Excel", vbCritical, "Error"
GoTo Exit_Handler
Exit Sub
Else
blnNewApp = True
End If
End If

' Switch normal error handling back on
On Error GoTo Err_Handler

Set xlBook = xlApp.Workbooks.Open("C:\Test.xls")

xlApp.Visible = True


Exit_Handler:

On Error Resume Next

If Not xlBook Is Nothing Then
Set xlBook = Nothing
End If

If Not xlApp Is Nothing Then

If blnError And blnNewApp Then
xlApp.Quit
End If

Set xlApp = Nothing

End If

Exit Sub

Err_Handler:
blnError = True
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub
 
Graham and Eric,

Thanks for your tutoring me on this. It has helped immensely, and in the
process I have gotten much more familiar with manipulating Excel from within
Access. I can now produce some better looking and better formatted
spreadsheets for my clients.

Bill
 
I get this function to work correctly when there is not a hidden (code initiated) instance of Excel open. When there is an instance that has been opened through code and there is not a workbook open yet the function returns false (as if it were not running). I don't see that as a huge setback because unless I have a workbook open I don't care what I need to do (in code) to start a clean instance to do my work.

I just thought that was interesting (that a workbook has to actually be open for an Excel Instance to be recognized).

Thanks
 
Back
Top