?
Solved

How to determine if there are more than one excel applications running

Posted on 2013-05-23
7
Medium Priority
?
281 Views
Last Modified: 2013-05-24
I have an Excel workbook that we use as a form. I have added a button that when clicked if this is the only form open Excel closed completely, but if Excel has another workbook opened it will only close the form.

    ActiveWorkbook.Saved = True
    If Workbooks.Count >= 2 Then Application.Quit '2 needed to include personal.wkb
    ThisWorkbook.Close


This works but only if the user opens another Excel file from the menu bar (OPEN FILE) within the form.  If Excel or another Excel file is opened by a shortcut, or other means, the code doesn't see it and cause the application to quit.

Is this possible?

Thank you.
0
Comment
Question by:thandel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 15

Accepted Solution

by:
Berkson Wein earned 2000 total points
ID: 39193124
If I'm understanding you right you want to exit the application if your "form" (workbook) is the only one open.  Otherwise, if there's more than one workbook open, just close the current workbook.

If that's the case, it looks like you code is backwards.

ActiveWorkbook.Saved =

' if there's only 1 workbook, then quit
If Workbooks.Count = 1 Then Application.Quit
ThisWorkbook.Close


Also, just a suggestion, but I'd use If then else here.  Even though it's the same in function, it's much easier to see what's going on if you were to look at this down the line.

ActiveWorkbook.Saved = True
If Workbooks.Count = 1 Then
     Application.Quit
else
    ThisWorkbook.Close
end if



Hope this helps.
0
 

Author Comment

by:thandel
ID: 39194130
Yes you understand my issue.  Your code is pretty much what I have now and when another workbook is opened from outside of the Excel "Form" the workbook count doesn't refelct this and thus is always closing  excel via the application quit.
0
 
LVL 15

Expert Comment

by:Berkson Wein
ID: 39194750
What I have is a bit different though in that it quits the application if there's only 1 workbook open.  If there's more than that, then it just closes the workbook.  Your version, I assume due to a logic oversight, has it the other way around, if there's 2 or more workbooks then it closes the application.

Did you try my code?  Candidly, I haven't, but I don't see a reason why it wouldn't work.  Excel doesn't care (or shouldn't) how another workbook is opened.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:thandel
ID: 39194947
I need two because I think the personal.xls is effecting it.  When I only open this form its reports that 2 are open.
0
 
LVL 15

Expert Comment

by:Berkson Wein
ID: 39194988
OK, fine, but you have:

If Workbooks.Count >= 2 Then Application.Quit

So if you have 2 or more open, then quit the application (which is what is happening right?)

I think what you mean is

If Workbooks.Count <= 2 Then Application.Quit
if there are 2 or FEWER, then quit, otherwise, just close the workbook.
0
 

Author Comment

by:thandel
ID: 39195052
Hmmm my current code is:

    ActiveWorkbook.Saved = True
    If Workbooks.Count <= 2 Then Application.Quit
    ThisWorkbook.Close

Sorry above code was posted incorrecly.  My mistake, sorry about that.
0
 

Author Comment

by:thandel
ID: 39195137
OK so I tried your code but changed the 1 to a 2 .... and its working... perhaps the if then else resolved this.  Thank you!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question