Closing Other Workbooks
 

Hi all,

I need a macro that checks for other open workbooks and, if they are there,
closes them (without saving). I'm using Workbook_Activate so that this
check will be done anytime a user changes to another workbook and back
again. Here's my code:

Private Sub Workbook_Activate()
Dim i, NumOpen As Integer
Dim w As Workbook
NumOpen = Application.Workbooks.Count
MsgBox "There are " & NumOpen & " workbooks open"
If NumOpen 1 Then
For Each w In Workbooks
If (w.Name < ThisWorkbook.Name And w.Name < "PERSONAL.XLS") Then
MsgBox w.Name & " will be closed"
Application.Workbooks(w.Name).Close savechanges:=False
End If
Next w
End If
End Sub

This all works just dandy, except that if a workbook gets closed Excel
crashes. If there are no other open workbooks, there is no problem. The
troublesome line of code is:

Application.Workbooks(w.Name).Close savechanges:=False

I've tried several variations on that line which all result in the same
problem (e.g. just using w.close). It does close the other workbook, but
then it crashes. What seems to happen is that it goes through the loop,
closes the workbook, and then exits the sub but then restarts the sub and
that causes the crash.

Any help would be appreciated.

Thanks,

Tim

Closing Other Workbooks
 

Hi Tim,

This code should work. FWIW, I tried your code in both XL 2002 and 2003,
and I experienced no problems. What version of XL are you running? Is it
up to date and fully-patched? What OS? Do you have any third-party add-ins
installed (or anything in XLStart)? If so, you should try unchecking all of
them and running your code again. If you don't have any problems, add the
add-ins back in one by one until you experience the crashes again.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Tim Mayes wrote:
Hi all,

I need a macro that checks for other open workbooks and, if they are
there, closes them (without saving). I'm using Workbook_Activate so
that this check will be done anytime a user changes to another
workbook and back again. Here's my code:

Private Sub Workbook_Activate()
Dim i, NumOpen As Integer
Dim w As Workbook
NumOpen = Application.Workbooks.Count
MsgBox "There are " & NumOpen & " workbooks open"
If NumOpen 1 Then
For Each w In Workbooks
If (w.Name < ThisWorkbook.Name And w.Name < "PERSONAL.XLS")
Then MsgBox w.Name & " will be closed"
Application.Workbooks(w.Name).Close savechanges:=False
End If
Next w
End If
End Sub

This all works just dandy, except that if a workbook gets closed Excel
crashes. If there are no other open workbooks, there is no problem. The
troublesome line of code is:

Application.Workbooks(w.Name).Close savechanges:=False

I've tried several variations on that line which all result in the
same problem (e.g. just using w.close). It does close the other
workbook, but then it crashes. What seems to happen is that it goes
through the loop, closes the workbook, and then exits the sub but
then restarts the sub and that causes the crash.

Any help would be appreciated.

Thanks,

Tim

Closing Other Workbooks
 

Tim, your code worked fine without crashing on my system.
It will only close the file if both excel files are in the same taskbar
window.
In the past I have seen some users' excel act quirky when closing a
file, the following code convention seemed to be the most stable for
me:

Application.DisplayAlerts = False
Workbooks(w.Name).Close SaveChanges:=False
Application.DisplayAlerts = True

Goodluck-Lonnie

Closing Other Workbooks
 

Jake,

Thanks, its good to hear that I'm not crazy. I tried for hours to track
down the problem.

I'm running Excel 2003 (11.63556360) SP1 on Windows XP SP2. I did have
Google Desktop search, but I deleted it after I saw notes here describing
the problems its causing (some I had noticed myself). I also recently
upgrade Adobe Acrobat and it is loading some stuff even after I removed the
..xla from the XLStart directory. I just located another version of that
add-in that was loading as well.

I'll try your suggestions and report back.

Thanks,

Tim
"Jake Marx" wrote in message
...
Hi Tim,

This code should work. FWIW, I tried your code in both XL 2002 and 2003,
and I experienced no problems. What version of XL are you running? Is it
up to date and fully-patched? What OS? Do you have any third-party
add-ins installed (or anything in XLStart)? If so, you should try
unchecking all of them and running your code again. If you don't have any
problems, add the add-ins back in one by one until you experience the
crashes again.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Tim Mayes wrote:
Hi all,

I need a macro that checks for other open workbooks and, if they are
there, closes them (without saving). I'm using Workbook_Activate so
that this check will be done anytime a user changes to another
workbook and back again. Here's my code:

Private Sub Workbook_Activate()
Dim i, NumOpen As Integer
Dim w As Workbook
NumOpen = Application.Workbooks.Count
MsgBox "There are " & NumOpen & " workbooks open"
If NumOpen 1 Then
For Each w In Workbooks
If (w.Name < ThisWorkbook.Name And w.Name < "PERSONAL.XLS")
Then MsgBox w.Name & " will be closed"
Application.Workbooks(w.Name).Close savechanges:=False
End If
Next w
End If
End Sub

This all works just dandy, except that if a workbook gets closed Excel
crashes. If there are no other open workbooks, there is no problem. The
troublesome line of code is:

Application.Workbooks(w.Name).Close savechanges:=False

I've tried several variations on that line which all result in the
same problem (e.g. just using w.close). It does close the other
workbook, but then it crashes. What seems to happen is that it goes
through the loop, closes the workbook, and then exits the sub but
then restarts the sub and that causes the crash.

Any help would be appreciated.

Thanks,

Tim

Closing Other Workbooks
 

Lonnie,

Thanks. After I found and deleted the old PDFMaker.xla (version 5, and I've
upgraded to version 6) it now works without crashing. I'd thought about
turning off DisplayAlerts, so I'll follow your advice and do that as well.

Thanks to both Jake and Lonnie!

Tim

"Lonnie M." wrote in message
oups.com...
Tim, your code worked fine without crashing on my system.
It will only close the file if both excel files are in the same taskbar
window.
In the past I have seen some users' excel act quirky when closing a
file, the following code convention seemed to be the most stable for
me:

Application.DisplayAlerts = False
Workbooks(w.Name).Close SaveChanges:=False
Application.DisplayAlerts = True

Goodluck-Lonnie