Solved

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

Posted on 2013-05-23
7
259 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
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:weinberk
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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

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:weinberk
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

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…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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 …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

785 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