Detecting an active instance of Excel from Word VBA

Sorcerer13 · #1 05-27-2014, 04:23 AM

Hi guys....
I am using the following code to check if Excel is currently active on a User's PC prior to opening another Excel Workbook.

Code:

 
'In a "Globals" module"

 Public gxlApp                       As Excel.Application
 Public gbooExcelIsRunning           As Boolean

'In the "detection" module

  On Error Resume Next
  Set gxlApp = GetObject(, "Excel.Application")
  If Err.Number <> 0 Then
    gbooExcelIsRunning = False
    Set gxlApp = New Excel.Application
   Else
    gbooExcelIsRunning = True
  End If
  On Error GoTo 0

However, this code (based on a Microsoft example) ALWAYS says that Excel is running, even when it definitely wasn't when the code was invoked.

It appears that my code actually invokes Excel (via the GetObject process??).
Can any kind soul correct my code to DETECT Excel, rather than INVOKE Excel?
I know the end result is to create an instance of Excel (which this does), but I'm trying to ensure that, later on in the code, if Excel was running to just close the new Workbook, but if Excel wasn't running, I'd like to close the Workbook and shut Excel down.

macropod · #2 05-27-2014, 05:56 AM

Try:

Code:

Dim gxlApp As Excel.Application
Dim gbooExcelIsRunning As Boolean
' Test whether Excel is already running.
On Error Resume Next
gbooExcelIsRunning = False ' Flag to record if we start Excel, so we can close it later.
Set gxlApp = GetObject(, "Excel.Application")
'Start Excel if it isn't running
If gxlApp Is Nothing Then
  Set gxlApp = CreateObject("Excel.Application")
  If gxlApp Is Nothing Then
    MsgBox "Can't start Excel.", vbExclamation
    Exit Sub
  End If
  ' Record that we've started Excel.
  gbooExcelIsRunning = True
End If
On Error GoTo 0

Sorcerer13 · #3 05-28-2014, 02:20 AM

Paul....

Thanks for the reply, but the same thing happens with YOUR code (slightly modified, see below: duplicate "As" removed, and lines to render an existing Excel instance visible).

If you put this code in a word module, set the references, then step through via PF8, even if every instance of Excel has been shut down, it appears that the GetObject actually starts Excel!

Even if an existing Excel document is open when the Word VBA runs, the VBA opens another Excel document!

I don't think what I'm trying to do is Rocket Science, but it sure seems difficult!

Any ideas???

Code:

Public Sub TestExcelFromWord()
Dim gxlApp                  As Excel.Application
' Needs reference to Microsoft Excel 14.0 Object Library
Dim gbooExcelIsRunning      As Boolean
' Test whether Excel is already running.
  On Error Resume Next
  gbooExcelIsRunning = False ' Flag to record if we start Excel, so we can close it later.
  Set gxlApp = GetObject(, "Excel.Application")
'Start Excel if it isn't running
  If gxlApp Is Nothing Then
    Set gxlApp = CreateObject("Excel.Application")
    If gxlApp Is Nothing Then
      MsgBox "Can't start Excel.", vbExclamation
      Exit Sub
    End If
  ' Record that we've started Excel.
    gbooExcelIsRunning = True
   Else
    gxlApp.Visible = True
  End If
  On Error GoTo 0
End Sub

macropod · #4 05-28-2014, 02:29 AM

Not when I run it ... You can test for your self by inserting:
MsgBox "!"
after
If gxlApp Is Nothing Then
On my system, if Excel isn't running, the message box displays; if Excel is running, no message box.

Sorcerer13 · #5 05-28-2014, 02:44 AM

Paul....

Still a problem on my PC!

I'm running Office 2010 on Windows 7 Professional (SP1, 32 bit).

Although I'm connected to a Server, the Office 2010 is "local", and runs from the "C" drive.

The application I'm developing lives on a Server drive, but the "testbed" my code comes from is definitely local (I haven't even saved it yet!!!).

Any ideas on what may be causing this abnormality?

I'm just off to save the Word, then E-mail it to a work colleague, then try it on HIS PC (same spec as mine) to see if it's a general problem at work, or if it's peculiar to my PC....

Steve

Sorcerer13 · #6 05-28-2014, 02:46 AM

Just update my Profile to reflect correct OS.

Sorcerer13 · #7 05-28-2014, 03:07 AM

I've just tried the same VBA on a colleague's PC, and it performs as expected, except that if you make Excel visible, whether it's a new or existing instance, at the end of the VBA any created instance of Excel is shut down.

This is probably logical - "I birthed it, I'll kill it!": just a little unexpected, as during the development stage of this sort of technique (opening a document with another application), in my experience errors in the "birthing" code usually leave other applications open, however "damaged" they may be.

I don't want to re-install Office 2010 (or the OS!) to "cure" this abnormality, so, any ideas on "Settings" which may cause this behaviour, or ways of comparing settings between two instances of Office 2010 (unlikely, I know)?

Any suggestions will be gratefully received.....

macropod · #8 05-28-2014, 05:04 AM

Are you sure you're testing the state of gbooExcelIsRunning before quitting? If it's True you shouldn't be trying to Quit. That's the whole point of capturing it...

Sorcerer13 · #9 05-28-2014, 06:25 AM

Paul....

The point I was making is that if you step through the code as previously shown, regardless of the current state of Excel, the code ALWAYS invokes ANOTHER instance of Excel with no Workbook open. Also, the flag "gbooExcelIsRunning" is NEVER set, so using this code I'll always terminate Excel, regardless of its original state.

If, after the

Code:

gxlApp.Visible = True

line I look at the "new" instance of Excel, under the File tab, the only available options are "Open/Recent/New/Help/Options/Exit", and the header in the Excel Window is just "Microsoft Excel", not "Name.Type - Microsoft Excel".

I strongly suspect it's all caused by a setting on my PC, but I'm bewildered (as usual) as to what it could be.

macropod · #10 05-28-2014, 04:43 PM

This works for me:

Code:

Sub Test()
Dim gxlApp As Excel.Application
Dim gbooExcelIsRunning As Boolean
' Test whether Excel is already running.
On Error Resume Next
gbooExcelIsRunning = False ' Flag to record if we start Excel, so we can close it later.
Set gxlApp = GetObject(, "Excel.Application")
'Start Excel if it isn't running
If gxlApp Is Nothing Then
  Set gxlApp = CreateObject("Excel.Application")
  If gxlApp Is Nothing Then
    MsgBox "Can't start Excel.", vbExclamation
    Exit Sub
  End If
  ' Record that we've started Excel.
  gbooExcelIsRunning = True
End If
On Error GoTo 0
' Do stuff
If gbooExcelIsRunning = True Then gxlApp.Quit
End Sub

Even an empty instance of Excel is only terminated if the macro started it.

Sorcerer13 · #11 05-29-2014, 05:23 AM

I still think the issue is my copy of MS Office, which I believe has become corrupt in some way.
At the next convenient opportunity I will relaod it, then report back....

macropod · #12 05-29-2014, 03:22 PM

You could try repairing the Office installation (via Programs & Features > Microsoft Office > Change in the Windows Control Panel). Reinstalling rarely cures anything a repair won't and a repair is much faster. Also, because some configuration settings are retained in the registry and templates when Office is uninstalled, a clean reinstall is somewhat harder to achieve than most people realize.

Sorcerer13 · #13 05-30-2014, 02:03 AM

Paul....

"a clean reinstall is somewhat harder to achieve than most people realize."

Not 'arf!

I have access to my own copy of Office 2010, and the Office version.

Using Control Panel I uninstalled and re-installed both versions (separately), to see if either one was corrupt.

Both exhibited the same reported problems.

I tried your “Repair” suggestion (I’d never noticed the “Change” button before).

Same result, so I suspect either the OS or a Registry setting.

Also, something else that may be relevant is that (on my PC only) in the VBA Project Explorer, everything is grouped under “Project (projectname)”.
Unlike other instances of Word, where there are subfolders in the Explorer (Userforms, Modules, This Document, etc.), in this version everything is in one folder!!!

Rather than going for a complete OS reinstallation (which may be just as tricky as an Office reinstall / repair), can you suggest any sites which give assistance in executing a COMPLETE removal of Word (i.e. including Registry settings)?

Any help or suggestions will be gratefully received….

Steve

macropod · #14 05-30-2014, 02:25 AM

You could have saved yourself some time by doing the repair first. Messing around with different flavours of Office won't achieve anything, since they use the same core components, registry settings and templates.

To completely remove Office, you should first use the std uninstall process, then run a utility like CCleaner or PC Cleaner to remove the vestiges the MS usually leaves behind. See also: http://office.microsoft.com/en-us/su...104027750.aspx

I don't understand what you mean re the VBE. Everything always goes into a Project. If you don't have any documents open and your Normal template doesn't have any macros, etc, I wouldn't expect to see anything other than the 'Normal' project. Even with a document open, you wouldn't necessarily expect to see anything other than that document's name and 'Normal'. It's only when you start adding macros that a given project's tree will expand. Since macros can be added to the 'ThisDocument' module of either the Normal template or a document, even having a macro doesn't guarantee having any entries under 'Modules', 'Class Modules' or 'Userform'.

Sorcerer13 · #15 05-30-2014, 03:49 AM

Paul....

Thanks for the advice on preparing a "clean" install - I'll look into it.

With regard to the VBE, I know what it normally looks like, and where Forms and Modules can be added.
I've added two screenshot attachments to show what I mean: existing Forms, Modules, etc ARE NOT IN THE NORMAL SUBGROUPS - They're ALL in the Project, which is behaviour I've never seen before.
I tend to prefix Forms with "frm", Modules with "mod" etc. The two pictures show the top and bottom of the VBE list, with This Workbook at the bottom.

Incidentally, if I open the Workbook on a different PC the VBE shows the Forms, Modules etc. CORRECTLY GROUPED.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
problem copying active excel table onto word, office 2013	billb	Word	2	01-08-2014 05:12 PM
Word vs bookmarks, another instance <g>	eNGiNe	Word	6	12-05-2012 01:05 AM
www....com does not stay active in excel 2010	waltdisneypixar	Excel	6	06-11-2012 12:55 AM
How to create multiple pages, 1 instance of word window	cs_starter	Mail Merge	1	09-08-2011 12:52 AM
VB Code in Excel Active worksheet	shakilhyd	Excel	2	05-17-2010 07:50 AM