Solved

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

Posted on 2013-05-23
7
278 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 500 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
Technology Partners: 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

Industry Leaders: 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!

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

695 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