Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining if Other Workbooks Are Open
Excel 2003. I have placed a button on my workbook's "switchboard" and want
to do the following when the button is depressed: 1. If there are other workbooks opened, then just close this workbook (Activeworkbook.Close) 2. If there are no other workbooks opened, then quit the Excel application (Application.Quit) How may I test to see if there are any other workbooks opened before I take one of the above two actions? Thanks for the help. -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining if Other Workbooks Are Open
Hi Doug
If Application.Workbooks.Count = 1 Then ' only me Else ' others End If Remember to deal with potential "save changes ?" requests. HTH. Best wishes Harald "Chaplain Doug" skrev i melding ... Excel 2003. I have placed a button on my workbook's "switchboard" and want to do the following when the button is depressed: 1. If there are other workbooks opened, then just close this workbook (Activeworkbook.Close) 2. If there are no other workbooks opened, then quit the Excel application (Application.Quit) How may I test to see if there are any other workbooks opened before I take one of the above two actions? Thanks for the help. -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining if Other Workbooks Are Open
Since a user might have personal.xls or other hidden workbooks, you can't
just check workbooks.count. Perhaps something like: Dim cnt as Long Dim bk as Workbook cnt = 0 for each bk in Workbooks if bk.Name < thisworkbook.Name then if bk.Windows(1).Visible then cnt = cnt + 1 end if end if Next if cnt = 0 then application.close ' code stops at this point else thisworkbook.Close end if -- Regards, Tom Ogilvy "Chaplain Doug" wrote in message ... Excel 2003. I have placed a button on my workbook's "switchboard" and want to do the following when the button is depressed: 1. If there are other workbooks opened, then just close this workbook (Activeworkbook.Close) 2. If there are no other workbooks opened, then quit the Excel application (Application.Quit) How may I test to see if there are any other workbooks opened before I take one of the above two actions? Thanks for the help. -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining if Other Workbooks Are Open
"Tom Ogilvy" skrev i melding
... Since a user might have personal.xls or other hidden workbooks, you can't just check workbooks.count. Good point. I never use that one myself, so it's not on my radar. Otoh, I often open xla files, edit them and forget wether I saved them or not. Best wishes Harald |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining if Other Workbooks Are Open
Thanks Tom (and Harald):
This led to another question for which I submitted a post. Excel 2003. I was checking how many workbooks Excel thought were open, when I only had one workbook open. Workbooks.Count returned 3. I checked what they we Workbooks(1).Name = PERSONAL.XLS Workbooks(2).Name = PERSONAL (version 1).xls Workbooks(3).Name = The workbook I have open Why do I have the PERSONAL.XLS opened, when I did not explicitly open it? Why are there two versions of PERSONAL.XLS open? How may I get rid of any or all of these "PERSONAL" workbooks? If I need to have one of them, then how do I get rid of the other (like the (version 1))? Thanks for the help. "Tom Ogilvy" wrote: Since a user might have personal.xls or other hidden workbooks, you can't just check workbooks.count. Perhaps something like: Dim cnt as Long Dim bk as Workbook cnt = 0 for each bk in Workbooks if bk.Name < thisworkbook.Name then if bk.Windows(1).Visible then cnt = cnt + 1 end if end if Next if cnt = 0 then application.close ' code stops at this point else thisworkbook.Close end if -- Regards, Tom Ogilvy "Chaplain Doug" wrote in message ... Excel 2003. I have placed a button on my workbook's "switchboard" and want to do the following when the button is depressed: 1. If there are other workbooks opened, then just close this workbook (Activeworkbook.Close) 2. If there are no other workbooks opened, then quit the Excel application (Application.Quit) How may I test to see if there are any other workbooks opened before I take one of the above two actions? Thanks for the help. -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining if Other Workbooks Are Open
Looks like Bob Phillips answered this one at your other post.
-- Regards, Tom Ogilvy "Chaplain Doug" wrote in message ... Thanks Tom (and Harald): This led to another question for which I submitted a post. Excel 2003. I was checking how many workbooks Excel thought were open, when I only had one workbook open. Workbooks.Count returned 3. I checked what they we Workbooks(1).Name = PERSONAL.XLS Workbooks(2).Name = PERSONAL (version 1).xls Workbooks(3).Name = The workbook I have open Why do I have the PERSONAL.XLS opened, when I did not explicitly open it? Why are there two versions of PERSONAL.XLS open? How may I get rid of any or all of these "PERSONAL" workbooks? If I need to have one of them, then how do I get rid of the other (like the (version 1))? Thanks for the help. "Tom Ogilvy" wrote: Since a user might have personal.xls or other hidden workbooks, you can't just check workbooks.count. Perhaps something like: Dim cnt as Long Dim bk as Workbook cnt = 0 for each bk in Workbooks if bk.Name < thisworkbook.Name then if bk.Windows(1).Visible then cnt = cnt + 1 end if end if Next if cnt = 0 then application.close ' code stops at this point else thisworkbook.Close end if -- Regards, Tom Ogilvy "Chaplain Doug" wrote in message ... Excel 2003. I have placed a button on my workbook's "switchboard" and want to do the following when the button is depressed: 1. If there are other workbooks opened, then just close this workbook (Activeworkbook.Close) 2. If there are no other workbooks opened, then quit the Excel application (Application.Quit) How may I test to see if there are any other workbooks opened before I take one of the above two actions? Thanks for the help. -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining if Other Workbooks Are Open
Harald,
Your post was not visible to me when I posted, so while it appears I was contradicting you in a rather rude fashion, that is certainly not what I was doing. I was just explaining to the OP why I had such a verbose response. Best Regards, Tom Ogilvy "Harald Staff" wrote in message ... "Tom Ogilvy" skrev i melding ... Since a user might have personal.xls or other hidden workbooks, you can't just check workbooks.count. Good point. I never use that one myself, so it's not on my radar. Otoh, I often open xla files, edit them and forget wether I saved them or not. Best wishes Harald |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
when i open an Excell file 3 workbooks open.Why? | Excel Discussion (Misc queries) | |||
When I open Excel, workbooks open automatically. How can I stop t | Excel Discussion (Misc queries) | |||
Excel 2003 Workbooks.Open with CorruptLoad=xlRepairFile fails on Excel 5.0/95 file due to Chart, with Error 1004 Method 'Open' of object 'Workbooks' failed | Excel Programming | |||
Workbooks.Open closes other workbooks | Excel Programming | |||
Workbooks.Open / .Open Text - How do you stop the .xls addition? | Excel Programming |