Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-05-23
7
Medium Priority
?
286 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
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.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

610 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