Check for other open wokbooks

[ordnance1]

I was using the line below at the end of my code and all was well. Until
someone had other Excel workbooks open (besides the one I had created) and
the code ran doing what it was supposed to do and closed excel completely. I
do want the Excel to close down, but only if my workbook is the only
workbook open.

So is there any way to amend what I have to check for other open workbooks,
and if there are any open the just close my workbook only?



Application.Quit

 

[Suri]

I was using the line below at the end of my code and all was well. Until
someone had other Excel workbooks open (besides the one I had created) and
the code ran doing what it was supposed to do and closed excel completely.. I
do want the Excel to close down, but only if my workbook is the only
workbook open.

So is there any way to amend what I have to check for other open workbooks,
and if there are any open the just close my workbook only?

Application.Quit

Try
Application.ActiveWorkbook.Close(SaveChanges:=False)

 

[ordnance1]

Thanks.

That will close the active workbook but I need to close Excel only if there
are no other workbooks open.

 

[Gord Dibben]

Application.Workbooks.Count

If more than 1 then close current WB only

If only 1 then close WB and Application.Quit


Gord Dibben MS Excel MVP

 

[ordnance1]

Thanks

here is my test code based on your reply: But I get an Invalid use of
Property error and it highlights .Count

Sub test()

Application.Workbooks.Count

If Application.Workbooks.Count = 0 Then

Application.Quit
End

End If

ThisWorkbook.Close False

End Sub

 

[Dave Peterson]

Option Explicit
Sub test()

If Application.Workbooks.Count = 1 Then
'save this workbook with the code
'ThisWorkbook.Save
'or
'or just lie to excel so the user doesn't get a prompt!
ThisWorkbook.Saved = True

'no matter what, quit excel
Application.Quit 'which closes the workbook, too.
End If

End Sub

=========
I don't know what you're doing, but I've never understood how a developer could
guess whether an open workbook should be saved when it's closed.

Either choice could cause a catastrophe -- closing the file without saving may
lose hours of work and saving the file could mean that the test changes that
should have been discarded are now there in place of the real data!

 

[ordnance1]

Thank you sir

Just to explain.

I have created a vacation calendar with which we track what days people
(over 200) have signed up for vacation. This document we keep protected and
only a couple of people have the access to edit it.

For the sake of security (some people are more computer literate than
others) I have created a viewer document that people can access from there
desk or from 3 kiosks we have out in our team room, which has links to the
original document and updates every 1 minute to reflect the latest changes.
The powers to be did not want the file to remain open on the kiosks for an
extended period of time so I included the coded to close the file after 15
minutes. Since one could not edit the viewer document, nor save changes I
just wanted to close the application after 15 minutes and the next user
would click on a desktop shortcut to reopen it. My problem was with those
people that would open it from their desk compute. They may well have other
workbooks open and not appreciate having Excel unexpectedly closed.

 

[Dave Peterson]

If all the users shouldn't open the file to modify, then I'd just give it a nice
password so they can't.

Show the SaveAs dialog
Tools|General options
and a memorable password to modify (not for opening).

Then the user will have to open it in readonly mode.

Alternatives would be to mark the file as Readonly -- either by using windows
explorer or by putting it on a share that the users only have readonly access
to.