There can be only 1

[Vamphyri]

I have written the following code to close all workbooks that are not named in the code. However, the code tries to close the specified workbook as well. D'Oh!

I think I'm on the right track, but could use a helping hand.

Sub CloseAllOthers()
Dim wb As Workbook
Dim wbName As String
For Each wb In Application.Workbooks
    If wbName = "2008 Census (HB 2002) Conversion" Then
    Exit Sub
    Else
    Workbooks.Close
    End If
Next
End Sub

Thanks


In the immortal words of Socrates, who said:
"I drank what?

 

[mintjulep]

You never assign any value to the variable wbName.

 

[SkipVought]

\\\Hi,

Sub CloseAllOthers()
Dim wb As Workbook

For Each wb In Application.Workbooks
    If thisworkbook.name <> "2008 Census (HB 2002) Conversion.xls" Then
        wb.Close
    End If
Next
End Sub

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[macropod]

Hi Vamphyri,

You've got some problems. First off 'wbName' is defined as a string, but what you're after is a workbook name. Secondly (if the naming issue was resolved), the way your sub is coded, it would abort as soon as it encountered the named workbook. If that was the first workbook all the others would remain open. Alternatively, if the named workbook isn't the first one found, 'Workbooks.Close' tries to close all of them.

Try this instead:

Sub CloseAllOthers()
Dim wb As Workbook
For Each wb In Application.Workbooks
    If wb.Name <> "2008 Census (HB 2002) Conversion.xls" Then wb.Close
Next
End Sub

Note that the name test is case-sensitive and you need to include the extension.

Cheers

[MS MVP - Word]

 

[combo]

If you intend to close all workbooks except the one the code runs, you can:

For Each wb In Application.Workbooks
    If Not wb is ThisWorkbook Then wb.Close
Next

It will work for any name of the workbook.


combo

 

[macropod]

A note of caution:

As coded, these macros will also close your Personal.xls workbook (if you have one). That might have undesirable consequences.

Cheers

[MS MVP - Word]

 

[SkipVought]

Good point macropod...

Sub CloseAllOthers()
Dim wb As Workbook
For Each wb In Application.Workbooks
    Select Case  wb.Name
       case "2008 Census (HB 2002) Conversion.xls", "Personal.xls"
       case else
          wb.delete
    end select
Next
End Sub

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[ClulessChris]

Skip

wb.delete?
should this not be
wb.close?

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[SkipVought]

close.

I'd better take a breath.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[fumei]

...and a deep one at that.



faq219-2884

Gerry
My paintings and sculpture

 

[SkipVought]

"Step away from the keyboard, and put down your mouse!"

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[Vamphyri]

Worked like a charm! I would give out stars to all, but am not sure if that is "Kosher" or not. If so, let me know and I'll give them away like candy.

Thanks to all


In the immortal words of Socrates, who said:
"I drank what?

 

[SkipVought]

If you award stars, it ought to be for [blue]valuable[/blue] posts.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}