Phantom Of The Open workbook.

  • Thread starter Thread starter Sam
  • Start date Start date
S

Sam

greetings fabulous information resource,

Can anyone explain why this statement;

"If Workbooks.Count = 2 Then Application.Quit"

works properly, ie. closes both the open wbook and Excel, when there's
actually only one workbook open?

The real mystery is why all bar two of about 40 people using this wbook
require their count value to be set to 1 for it to work correctly. The
object of the line being to leave any other open wbook alone when
closing this one. For reasons that I've nfi about there's two of us out
of the forty who, with *only* this wbook open, are told by a msgbox
using the Workbooks.Count that there's really two wbooks open and
consequently we require the Count value to be set to 2.

It's beyond me. Can anyone help with this?


cheers
alvey
 
Hi Sam,

If you have a hidden workbook - your personal.xls macro workbook, perhaps -
then what you believe to be a count of 1 may, in fact, be 2.
 
Probably personal.xls. If they have recorded macros and stored them in their
personal workbook then they will have two books open when it appears as if
only one book is open...
 
Here's some crude code to count the number of visible windows

Dim x As Integer, wbs As Window
x = 0
For Each wbs In Application.Windows
If wbs.Visible = True Then
x = x + 1
End If
Next
MsgBox x & " windows visible"
 
Crikey! Three responses in a matter of minutes!
Can anyone imagine life without the internet now? We'd have to go back
to reading hulking great reference books. Which would be even more
difficult now as we probably can't even lift the bastards anymore.

Anyway, thanks all.
It was of course the Personal Macro wbook causing the problem. [Hits
head with beer coaster]
Thanks for the code Steve.


cheers
 
and WHY would he want to count windows?

it's workbooks OP is interested in. If he closes a workbook
ALL the windows of that workbook are closed anyway...

The only problem is that in a for each enumaration of the workbooks
collection the addins are skipped.

For each wkb in workbooks
wkb.close
next

an alternative that closes all workbooks AND addins
requires a simple xl4 macro..

Sub CloseAllButMe()
Dim vDOCS, vDOC
ThisWorkbook.Names.Add "DOCS", "=documents(3)"
vDOCS = [docs]
ThisWorkbook.Names("docs").Delete
For Each vDOC In vDOCS
If vDOC <> ThisWorkbook.Name Then
Workbooks(vDOC).Close
End If
Next
End Sub





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


STEVE BELL wrote :
 
Sam,

You're welcome!

Hope the code helps...

And Yes! I have learned more on this ng than I could have ever learned in
books...
 
Sounded like he was looking to see how many visible workbooks were opened.
My code seemed to do that.

Correct me if I'm wrong, please...
--
steveB

Remove "AYN" from email to respond
keepITcool said:
and WHY would he want to count windows?

it's workbooks OP is interested in. If he closes a workbook
ALL the windows of that workbook are closed anyway...

The only problem is that in a for each enumaration of the workbooks
collection the addins are skipped.

For each wkb in workbooks
wkb.close
next

an alternative that closes all workbooks AND addins
requires a simple xl4 macro..

Sub CloseAllButMe()
Dim vDOCS, vDOC
ThisWorkbook.Names.Add "DOCS", "=documents(3)"
vDOCS = [docs]
ThisWorkbook.Names("docs").Delete
For Each vDOC In vDOCS
If vDOC <> ThisWorkbook.Name Then
Workbooks(vDOC).Close
End If
Next
End Sub





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


STEVE BELL wrote :
Here's some crude code to count the number of visible windows

Dim x As Integer, wbs As Window
x = 0
For Each wbs In Application.Windows
If wbs.Visible = True Then
x = x + 1
End If
Next
MsgBox x & " windows visible"
Click to expand...
Click to expand...
 
a workbook can have multiple windows (visible and/or hidden)
thus the count of the visible windows is meaningless
insofar as it has no relation to the number of workbooks.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


STEVE BELL wrote :
 
You might be correct... You are more familiar and adapt then myself!

So I am curious now - how would you do it?
That is count the number of unhidden workbooks open in a single session of
Excel...

Thanks...
 
I think this works:

Function lngCount() As Long
Dim wb As Workbook
For Each wb In Workbooks
If wb.Windows(1).Visible Then lngCount = lngCount + 1
Next
End Function
 
Back
Top