Determining if Other Workbooks Are Open

[Guest]

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.

 

[Harald Staff]

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

 

[Tom Ogilvy]

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

 

[Harald Staff]

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

 

[Guest]

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

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.

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

 

[Tom Ogilvy]

Looks like Bob Phillips answered this one at your other post.

--
Regards,
Tom Ogilvy

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

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]

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