PDA

View Full Version : close all but activeworbook



lior03
02-01-2007, 07:15 AM
hello
i have few workbooks open.i want to close all and leave the active workbook open.
i also want to enable the user decide for himself whether to save changes in each workbook.
this is my version:

Sub closeallbut()
On Error Resume Next
Dim w As Object
MsgBox "are you sure about this", vbYesNo
If vbYes Then
For Each w In Workbooks
If w.name <> ActiveWorkbook.name Then
w.Close savechanges:=True
End If
If vbNo Then
If w.name <> ActiveWorkbook.name Then
w.Close savechanges:=False
End If
End If
Next
End If
End Sub


any suggestions?

Charlize
02-01-2007, 08:21 AM
I came up with this one :
Sub close_workbooks()
Dim wbname As String
Dim wb As Workbook
Dim answer As Long
wbname = ThisWorkbook.Name
For Each wb In Application.Workbooks()
If wb.Name <> wbname Then
answer = MsgBox("Save changes for " & wb.Name, vbYesNo)
Select Case answer
Case 6
wb.Save
wb.Close
Case 7
Application.DisplayAlerts = False
wb.Close
Application.DisplayAlerts = True
End Select
End If
Next wb
End Sub
Charlize

Bob Phillips
02-01-2007, 08:27 AM
Sub closeallbut()
On Error Resume Next
Dim w As Workbook
If MsgBox("are you sure about this", vbYesNo) = vbYes Then
For Each w In Workbooks
If w.Name <> ActiveWorkbook.Name Then w.Close
Next
End If
End Sub

lior03
02-13-2007, 07:50 AM
hello
i want to let the user know the number of workbooks that are open at the moment.

Sub countworkbooks()
Dim wbname As String
Dim wb As Workbook
Dim i As Integer
i = 0
On Error Resume Next
wbname = ActiveWorkbook.name
For Each wb In Application.Workbooks
If wb.name <> ActiveWorkbook.name Then
i = i + 1
MsgBox i
End If
Next


if the number is 1 .namely only activeworkbook is open ,then exit sub with a msgbox - "only one workbook open"
if more then one is open then proceed with selecting which one to close.
thanks

malik641
02-13-2007, 08:47 AM
hello
i want to let the user know the number of workbooks that are open at the moment.

Workbooks.Count

:thumb

dansam
02-17-2007, 01:18 AM
Hi Every one,

All codes are working nicely but when I open a new workbook by clicking on the MS Excel icon form start menu or desktop called (book3) and then I run that code... but the code only closes the workbooks opened from the file > New or from “New” icon ,
So what to do if I want to close all open (I repeat “ALL”) but my workbook.
Regards
Dan

Bob Phillips
02-17-2007, 03:33 AM
I cannot see how the code does (indeed, could) differentiate between workbooks opened from the desktop, New, or the file open dialog. All it does is look at the Workbooks collection.

dansam
02-17-2007, 05:04 AM
So what to do now to close ALL open workbooks but the active one ?

mdmackillop
02-17-2007, 05:33 AM
I cannot see how the code does (indeed, could) differentiate between workbooks opened from the desktop, New, or the file open dialog. All it does is look at the Workbooks collection.
I thinks he's starting another instance of Excel, which won't be accessed by this code.

malik641
02-17-2007, 09:14 AM
...but when I open a new workbook by clicking on the MS Excel icon form start menu or desktop called (book3) and then I run that code... but the code only closes the workbooks opened from the file > New or from ?New? icon...
Malcolm's exactly right. dansam is opening a new instance of the application.

Bob Phillips
02-17-2007, 10:06 AM
I take the point, but that doesn't mean a new instance to me. When I click on a workbook icon on the desktop, it opens in the existing Excel instance.

malik641
02-17-2007, 10:20 AM
When it's a desktop workbook icon, it opens in the existing instance (if any) for me too. But Start-->Programs-->Microsoft Office-->Microsoft Excel 2003 always opens a new instance for me (as it should for everyone I'm pretty sure).

I guess it depends on what dansams exactly doing. If he's using one instance of Excel, the code should work. We know that each workbook opened is added to the workbooks collection (per instance of Excel).

...Now that I think about it we forgot to mention that the code should be in an Add-In or in the Personal.xls file. If anyone has Book1 with the code, and use the code while in Book5, the code will stop after closing Book1.

dansam
02-17-2007, 10:39 AM
Hi xld,
I understand what you are saying but my point is when a user wants to make its new workbook and if he start a new instance on excel as I above Mentioned , Then what ????

lucas
02-17-2007, 10:47 AM
...Now that I think about it we forgot to mention that the code should be in an Add-In or in the Personal.xls file.

If it is in the personal.xls and you run it with a different workbook active then the personal.xls is closed.

malik641
02-17-2007, 11:25 AM
Good point, Steve.

I guess if we modify Bob's code to the following we'll be ok:

Sub closeallbut()
On Error Resume Next
Dim w As Workbook
If MsgBox("are you sure about this", vbYesNo) = vbYes Then
For Each w In Workbooks
If w.Name <> ActiveWorkbook.Name And UCase(w.Name) <> "PERSONAL.XLS" Then w.Close
Next
End If
End Sub

Or if the code is in the Personal.xls file.

Sub closeallbut()
On Error Resume Next
Dim w As Workbook
If MsgBox("are you sure about this", vbYesNo) = vbYes Then
For Each w In Workbooks
If w.Name <> ActiveWorkbook.Name And w.Name <> ThisWorkbook.Name Then w.Close
Next
End If
End Sub

lior03
08-17-2007, 08:29 AM
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
On Error Resume Next
Dim i As Integer
i = Workbooks.Count
Dim wb As Workbook
For Each wb In Application.Workbooks
If wb.name <> ActiveWorkbook.name Then
If i >= 2 Then
Set NewItem = Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, temporary:=True)
NewItem.Caption = "close other w.b"
NewItem.OnAction = "closeallbut"
NewItem.faceid = 687
NewItem.BeginGroup = True
End If
End If
Next
End Sub

the aim of this event is to enable me to control and close all w.b other
then the active w.b.
problem is:if 4 w.b are open the shortcut appears 4 times and if 2 w.b are open then the shortcut appears 2 times.
how can i make it appear only once regardless how many w.b are open
other then the activeworkbook?
thanks

Bob Phillips
08-17-2007, 08:40 AM
I may not be getting this, but ...?



Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
On Error Resume Next
Dim i As Integer
i = Workbooks.Count
Dim wb As Workbook
If i >= 2 Then
Set NewItem = Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, temporary:=True)
NewItem.Caption = "close other w.b"
NewItem.OnAction = "closeallbut"
NewItem.FaceId = 687
NewItem.BeginGroup = True
End If
End Sub

lior03
08-17-2007, 12:40 PM
i tried to use xld code further.i want excel to check how many w.b are open at the moment.if one is open then a msgbox appears.if two or more are open then another msgbox appear with the name of each w.b asking me if i want to activate it.

Sub activateworkbooks()
Dim wb As Workbook
Dim i As Integer
For Each wb In Application.Workbooks
i = Workbooks.Count
Select Case i
Case i = 1
MsgBox " only one workbook is open", vbOKOnly, "workbooks open"
Case i >= 2
If MsgBox(wb.FullName, vbYesNo + vbCritical, "activate workbooks") = vbYes Then
wb.Activate
End If
End Select
Next wb
End Sub


thanks

Bob Phillips
08-17-2007, 02:47 PM
And ...?

It works? It does something unwanted?

lior03
08-18-2007, 12:12 AM
Sub activateworkbooks()
Dim wb As Workbook
Dim ans As VbMsgBoxResult
Dim i As Integer
For Each wb In Application.Workbooks
i = Workbooks.Count
If i >= 2 Then
If wb.name <> ActiveWorkbook.name Then
ans = MsgBox("would you like to visit " & wb.FullName & " ? ", vbYesNo + vbCritical, "activated workbooks:")
Select Case ans
Case vbYes
wb.Activate
Exit Sub
Case vbNo
Exit Sub
End Select
End If
End If
Next
End Sub

my other thread was a mistake .how can i add to this macro code of tell the user through a msgbox that only the activeworkbook is open.
thanks

Bob Phillips
08-18-2007, 02:17 AM
Sub activateworkbooks()
Dim wb As Workbook
Dim ans As VbMsgBoxResult
Dim i As Integer
For Each wb In Application.Workbooks
i = Workbooks.Count
If i >= 2 Then
If wb.name <> ActiveWorkbook.name Then
ans = MsgBox("would you like to visit " & wb.FullName & " ? ", vbYesNo + vbCritical, "activated workbooks:")
Select Case ans
Case vbYes
wb.Activate
Exit Sub
Case vbNo
Exit Sub
End Select
End If
Else
MsgBox "Only active workbook open"
End If
Next
End Sub

lior03
10-25-2007, 10:09 AM
i want to replace the builtin save icon with a macro of my own.my nacro saves all open workbook.

Sub SaveAll()
Dim wb As Workbook
If MsgBox("save all activeworkbooks?", vbExclamation + vbYesNo, "saveall - " & Now) = vbYes Then
For Each wb In Workbooks
MsgBox wb.FullName, vbInformation + vbOKOnly, "last saved at - " & wb.BuiltinDocumentProperties("last save time")
wb.Activate
wb.Save
Next wb
End If
End Sub


as i understand the macro to replace an icon is:

Sub unchangesavebutton()
CommandBars("standard").Controls("????").OnAction = ""
End Sub


why can't i replace the button.i am using a hebrew office xp version of excel.
thanks

lior03
07-27-2008, 05:35 AM
hello
u have this macro thats save all open workbook:

On Error Resume Next
If MsgBox("save all active workbooks?", vbInformation + vbYesNo, "save all - " & Now) = vbYes Then
Dim wb As Workbook
For Each wb In Workbooks
MsgBox wb.FullName, vbInformation + vbOKOnly, wb.BuiltinDocumentProperties("last save time")
wb.Activate
wb.Save
Next wb
If Workbooks.Count > 1 Then
UserForm15.Show
End If
End If


then i ask excel to close all open workbooks other then the activeworkbook:

Dim wbname As String
Dim wb As Workbook
Dim answer As Long
On Error Resume Next
wbname = ActiveWorkbook.name
For Each wb In Application.Workbooks
If wb.name <> wbname And wb.Saved = True Then
answer = MsgBox("Save changes for " & wb.name, vbYesNoCancel + vbExclamation, _
"close all inactive workbooks?")
Select Case answer
Case vbYes
wb.Close savechanges:=True
Case vbNo
Application.DisplayAlerts = False
wb.Close
Application.DisplayAlerts = True
Case vbCancel
Exit Sub
End Select
End If
Next wb


how can i prevent excel from asking me twice to save an already saved workbooks?.how can i merge the tow macros into one.
thanks

Bob Phillips
07-27-2008, 07:23 AM
I think you mean



Dim wbname As String
Dim wb As Workbook
Dim answer As Long
On Error Resume Next
wbname = ActiveWorkbook.name
For Each wb In Application.Workbooks
If wb.name <> wbname And wb.Saved = False Then
answer = MsgBox("Save changes for " & wb.name, vbYesNoCancel + vbExclamation, _
"close all inactive workbooks?")
Select Case answer
Case vbYes
wb.Close savechanges:=True
Case vbNo
Application.DisplayAlerts = False
wb.Close Savechanges:=False
Application.DisplayAlerts = True
Case vbCancel
Exit Sub
End Select
End If
Next wb