Activating Workbook Regardless of Capitalization

[FrenchCelt]

Hello,

I have some VBA code I use regularly when constructing macros that switch back and forth between different workbooks:

For Each w In Application.Workbooks
        If (w.Name) Like "Workbook Name" Then
            Exit For
        End If
    Next w
        If Not w Is Nothing Then
        w.Activate
        Else
            MsgBox "No workbook open!"
        End If

It's been very useful until now, as I have a workbook where sometimes the filename is capitalized and sometimes it's not. If there is a mismatch, the code errors out. I could simply use code to save the file with a specific name so that it always matches, but I like to first activate the correct workbook before saving to avoid someone accidentally running the code on the wrong workbook and saving that with the incorrect filename and completely blowing the process with the remaining code. Is there a way to do this so that the code will work even if the workbook is "Workbook Name" or "WORKBOOK NAME" or "workbook name" or any other capitalization variation?

 

[Momentman]

Hello,

I have some VBA code I use regularly when constructing macros that switch back and forth between different workbooks:

For Each w In Application.Workbooks
        If (w.Name) Like "Workbook Name" Then
            Exit For
        End If
    Next w
        If Not w Is Nothing Then
        w.Activate
        Else
            MsgBox "No workbook open!"
        End If

It's been very useful until now, as I have a workbook where sometimes the filename is capitalized and sometimes it's not. If there is a mismatch, the code errors out. I could simply use code to save the file with a specific name so that it always matches, but I like to first activate the correct workbook before saving to avoid someone accidentally running the code on the wrong workbook and saving that with the incorrect filename and completely blowing the process with the remaining code. Is there a way to do this so that the code will work even if the workbook is "Workbook Name" or "WORKBOOK NAME" or "workbook name" or any other capitalization variation?

You could maybe modify the second line to

If ucase(w.Name) Like "WORKBOOK NAME" Then

cause UCASE to convert to upper case and then compare to an uppercased string

 

[FrenchCelt]

Unfortunately that didn't work. I still get the same error message if the file name capitalization does not match:

On this line:

If Not w Is Nothing Then

But it works fine if the capitalization matches.

 

[Alex Blakenburg]

What happens if you replace this:

        If (w.Name) Like "Workbook Name" Then

With this:
(where = 0 means a match, and vbTextCompare means its not case sensitive)

        If StrComp(w.Name, "Workbook Name", vbTextCompare) = 0 Then

 

[FrenchCelt]

I get the same error as before.

 

[Alex Blakenburg]

I have logged out for the night but you are going to have to give us a specific example so we can test the same scenario.

 

[FrenchCelt]

I would have to send the entire file. It's ok, I'll just engrain the notion that the people who use the macro have to run it while the correct workbook is active and that any screwups are their fault. I like to dummyproof my macros to encompass multiple possible points of failure due to incompetence on the part of the people I create them for (none of whom are Excel savvy in the least), but it's not always possible.