VBA to close workbook with different name at the end

[cdalgorta]

Hi,

So closing a specific workbook is simple. Google knows too.
But what's the VBA to close a workbook that changes name at the end everyday?

Example:
Today: A_AgingDetail20220721ghtr
Tomorrow: A_AgingDetail20220722erts
A. Tomorrow: A_AgingDetail20220723weph

So the only identical part will always be "A_AgingDetail*"
The file is in my downloads and it is a csv file in case this information is necessary

Thank you in advance!

 

[Jaafar Tribak]

One thing you can do is to loop through all opened workbooks and find the one that contains the A_AgingDetail string in its name . You can use the Like operator or the vba Instr function .

 

[cdalgorta]

One thing you can do is to loop through all opened workbooks and find the one you want. You can use the Like operator or the vba Instr function .

Yes after trying for like an hour, finally figured it out

For Each wbk In Workbooks
If wbk.Name Like "A_AgingDetail*" Then
wbk.Close SaveChanges:=False
End If
Next wbk

Question. Would you know how would I change the above formula if I wanted to "select" that windows?
Like the regular
Windows("name").Activate
How would I edit the above to activate a specific workbook?
I tried

For Each wbk In Workbooks
If wbk.Name Like "A_AgingDetail*" Then
Windows("wbk").Activate
End If
Next wbk

But it did not work.

 

[Jaafar Tribak]

You can just use wbk.Activate

I just wrote a single function that is generic and which could be used for both activating and\or closing the workbook depending on the Action parameter.

Place this in a Standard Module:

Option Explicit

Enum enumAction
    CloseIt
    ActivateIt
End Enum

Function Act_On_Workbook_By_Partial_Name( _
    ByVal PartialName As String, _
    ByVal Action As enumAction, _
    Optional ByVal SaveChanges As Boolean = True _
) As Boolean

    Dim owb As Workbook
   
    For Each owb In Workbooks
        If owb.Name Like PartialName & "*" Then
            If Action = ActivateIt Then
                owb.Activate
            Else
                owb.Close SaveChanges
            End If
            Act_On_Workbook_By_Partial_Name = True
            Exit For
        End If
    Next owb

End Function

Now you could activate the workbook as follows:

Sub Test1()

    If Act_On_Workbook_By_Partial_Name("A_AginghgDetail20220721ghtr", ActivateIt) = False Then
        MsgBox "Workbook whose partial name is:" & vbNewLine & _
               """A_AginghgDetail20220721ghtr"" not found!", vbCritical
    End If
   
End Sub

And you could close the workbook using the same function as follows : (SaveChanges is set to TRUE by default)

Sub Test2()

    If Act_On_Workbook_By_Partial_Name("A_AginghgDetail20220721ghtr", CloseIt) = False Then
        MsgBox "Workbook whose partial name is:" & vbNewLine & _
               """A_AginghgDetail20220721ghtr"" not found!", vbCritical
    End If
   
End Sub

The only difference between the above two tests is the second argument ActivateIt vs CloseIt

 

[cdalgorta]

You can just use wbk.Activate

I just wrote a single function that is generic and which could be used for both activating and\or closing the workbook depending on the Action parameter.

Place this in a Standard Module:

Option Explicit

Enum enumAction
    CloseIt
    ActivateIt
End Enum

Function Act_On_Workbook_By_Partial_Name( _
    ByVal PartialName As String, _
    ByVal Action As enumAction, _
    Optional ByVal SaveChanges As Boolean = True _
) As Boolean

    Dim owb As Workbook
  
    For Each owb In Workbooks
        If owb.Name Like PartialName & "*" Then
            If Action = ActivateIt Then
                owb.Activate
            Else
                owb.Close SaveChanges
            End If
            Act_On_Workbook_By_Partial_Name = True
            Exit For
        End If
    Next owb

End Function

Now you could activate the workbook as follows:

Sub Test1()

    If Act_On_Workbook_By_Partial_Name("A_AginghgDetail20220721ghtr", ActivateIt) = False Then
        MsgBox "Workbook whose partial name is:" & vbNewLine & _
               """A_AginghgDetail20220721ghtr"" not found!", vbCritical
    End If
  
End Sub

And you could close the workbook using the same function as follows : (SaveChanges is set to TRUE by default)

Sub Test2()

    If Act_On_Workbook_By_Partial_Name("A_AginghgDetail20220721ghtr", CloseIt) = False Then
        MsgBox "Workbook whose partial name is:" & vbNewLine & _
               """A_AginghgDetail20220721ghtr"" not found!", vbCritical
    End If
  
End Sub

The only difference between the above two tests is the second argument ActivateIt vs CloseIt

Thank you!

 

[Jaafar Tribak]

Thank you!

Thanks.
BTW, If you have used wbk.Activate, it won't bring the workbook to the front if the latter is minimized to the taskbar.

In order to force the workbook to the front, you will need to use the Window object as follows:

wbk.Activate
If wbk.Windows(1).WindowState = xlMinimized Then
wbk.Windows(1).WindowState = xlNormal
End If