Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Closing workbooks w/o closing Excel | Excel Discussion (Misc queries) | |||
closing multi workbooks | Excel Discussion (Misc queries) | |||
Closing workbooks | Excel Programming | |||
Opening and Closing workbooks | Excel Programming | |||
Closing workbooks through macro | Excel Programming |