close function at workbooks (collection) level

[lezawang]

Hi
No one talk about "Workbooks.close" --> collection leve<collection level="">. I did google search and they are all talking about "Workbooks("name").close" <-- workbook level<workbook level="">

The code below, will close the first workbook no matter how many workbooks are open. My questions
1) How can I improve that code and control what I want to close for example ask user which workbooks he/she wants to close

 x=inputbox("enter workbook number (index) you want to close")

2) can that code imporve without using inputbox function to control what to close? Thank you so much

Sub april15_2()
    Workbooks.Close
End Sub

</workbook></collection>

 

[DanteAmor]

There is no numerical index for open books, but you can know the order in which they were opened with a For Each.
Try this:

Sub test()
    Dim wb As Workbook, x As Variant, n As Long
    x = InputBox("enter workbook number (index) you want to close")
    If x = "" Then Exit Sub
    If IsNumeric(x) Then
        n = 1
        For Each wb In Workbooks
            If n = x Then
                wb.Close
                Exit Sub
            End If
            n = n + 1
        Next
    End If
End Sub

 

[smozgur]

Something like this:

Sub closeAll()
Dim i As Integer
Dim msg As String
Dim ids As Variant
    On Error Resume Next
    With Application.Workbooks
        For i = 1 To .Count
            If Not .Item(i) Is ThisWorkbook Then
                msg = msg & vbCr & i & "- " & .Item(i).Name
            End If
        Next i
        
        ids = Split(InputBox("Enter the workbook index numbers that you want to close separated by comma and click Ok." & vbCr & msg, "Close Workbooks"), ",")
    
        
        For i = UBound(ids) To 0 Step -1
            If Not .Item(Int(ids(i))) Is ThisWorkbook Then
                .Item(Int(ids(i))).Close False
            End If
        Next i
        
    End With
    On Error GoTo 0
End Sub

It doesn't have a proper error handling, but simply ignoring the wrong entries (like an item number higher than opened workbooks, or a wrong text entry). But it should give an idea.

Note that how I go from the upper item index to zero as items will get confused if you close a workbook with smaller item index.
Also note that I used "don't save" option in the close method as False. You can save by using True, or leave it blank to ask user.
Final note - it is obviously ignoring the workbook with the macro.

Hope it helps.

EDIT: In the first note, I didn't mention that I assumed user entered the item indexes in ascending order (i.e. 3,4,6, not 4,6,3). Array numbers could be sorted first as a solution, but as I said, this is just to give an idea.