Solved

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

Posted on 2013-05-23
7
240 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
  • 4
  • 3
7 Comments
 
LVL 15

Accepted Solution

by:
weinberk earned 500 total points
Comment Utility
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
Comment Utility
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:weinberk
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:thandel
Comment Utility
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:weinberk
Comment Utility
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
Comment Utility
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
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now