Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for other open wokbooks
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for other open wokbooks
On Apr 28, 10:30*pm, "ordnance1" wrote:
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:=Fals e) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for other open workbooks
Thanks.
That will close the active workbook but I need to close Excel only if there are no other workbooks open. "Suri" wrote in message ... On Apr 28, 10:30 pm, "ordnance1" wrote: 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:=Fals e) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for other open workbooks
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 On Wed, 28 Apr 2010 14:57:10 -0700, "ordnance1" wrote: Thanks. That will close the active workbook but I need to close Excel only if there are no other workbooks open. "Suri" wrote in message ... On Apr 28, 10:30 pm, "ordnance1" wrote: 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:=Fals e) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for other open workbooks
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 "Gord Dibben" <gorddibbATshawDOTca wrote in message ... 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 On Wed, 28 Apr 2010 14:57:10 -0700, "ordnance1" wrote: Thanks. That will close the active workbook but I need to close Excel only if there are no other workbooks open. "Suri" wrote in message ... On Apr 28, 10:30 pm, "ordnance1" wrote: 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:=Fals e) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for other open workbooks
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 wrote: 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 "Gord Dibben" <gorddibbATshawDOTca wrote in message ... 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 On Wed, 28 Apr 2010 14:57:10 -0700, "ordnance1" wrote: Thanks. That will close the active workbook but I need to close Excel only if there are no other workbooks open. "Suri" wrote in message ... On Apr 28, 10:30 pm, "ordnance1" wrote: 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:=Fals e) -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for other open workbooks
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" wrote in message ... 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 wrote: 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 "Gord Dibben" <gorddibbATshawDOTca wrote in message ... 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 On Wed, 28 Apr 2010 14:57:10 -0700, "ordnance1" wrote: Thanks. That will close the active workbook but I need to close Excel only if there are no other workbooks open. "Suri" wrote in message ... On Apr 28, 10:30 pm, "ordnance1" wrote: 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:=Fals e) -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for other open workbooks
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. ordnance1 wrote: 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" wrote in message ... 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 wrote: 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 "Gord Dibben" <gorddibbATshawDOTca wrote in message ... 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 On Wed, 28 Apr 2010 14:57:10 -0700, "ordnance1" wrote: Thanks. That will close the active workbook but I need to close Excel only if there are no other workbooks open. "Suri" wrote in message ... On Apr 28, 10:30 pm, "ordnance1" wrote: 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:=Fals e) -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to make excel 2007 open 2 wokbooks in 2 different windows | Excel Discussion (Misc queries) | |||
If Then to check if a workbook is open | Excel Discussion (Misc queries) | |||
How check if workbook open? | Excel Programming | |||
Check for open workbook | Excel Programming | |||
How do I check opened wokbooks? | Excel Programming |