Excel already open test

  • Thread starter Thread starter Nigel
  • Start date Start date
N

Nigel

Hi All
I have an application that takes control of Excel. That works ok. However
if the user already has Excel running, with or without an open workbook, my
application which controls the menu and command bars also affect the current
workbooks as well. to avoid this I need to detect if this situation exists
as my application loads.

What I would like to do is as follows....

1. If an instance of Excel is running, then open another instance - do not
use the current one. This is my preference as my application opens in its
own environment and does not affect the current open workbooks.

2. If the above is not possible, provide a controlled shutdown of the
existing open workbook(s), asking the user to either save or close them. If
their choice, on any open workbook, is 'No' then do not open my application
and tell the user as much.

Is this possible?
 
Here is some code that opens a specified workbook in a new instance of Excel.
Put this code in a module in an empty workbook. This workbook will become the
access point to open the intended workbook. The user will enver even see this
workbook. You will end up with 2 instances of Excel running. You will still
potentially end up with a problem if the user wants to open up new
spreadsheets. Which instance of Excel is active when they open the
spreadsheet is very important. I abandoned doing this as it ended up being
too problematic for the users but give it a try. If you can make it work then
more power to you.

Option Explicit
Private Const m_strPath As String = "C:\Documents and
Settings\jamest\Desktop\Budget 2004\"
Private Const m_strFileName As String = "TestTemplate.xls"


Sub Auto_Open()
Dim appXL As Excel.Application
Dim wbk As Workbook

Set appXL = CreateObject("Excel.Application.9")
appXL.Visible = True
Set wbk = appXL.Workbooks.Open(m_strPath & m_strFileName, , True)
wbk.RunAutoMacros xlAutoOpen
ThisWorkbook.Close SaveChanges:=False
End Sub

HTH
 
I use the code on the following link to check if Excel is currently running
or not:

http://www.mvps.org/access/modules/mdl0006.htm

The link above shows automation of Excel, and includes a link to the
fISAppRunning module. I also found a need of the fIsAppRunning module to
keep my VBA routine in a loop until after an application closes so a second
sheet would update BEFORE being imported into Access. It's nifty. Hope
this helps.

Scott
 
Thanks Scott, I can see many uses for the function in automating Office
applications, but my problem is that I need to detect the Excel instance
before I load my application (also Excel).
 
Thanks Jim, I like the approach it always creates a new instance of xL
before loading the main application, in which my application does not allow
new workbooks/sheets to be created or opened so it is OK from that
perspective.

I can alos see where the problem arises in the user area, with 2 not 1
workbooks, quite tricky in a remote situation. I will play around with it
and see if I come up with anything.

Thanks again
 
Back
Top