VBA Code for renaming filename base on month

[luna007]

VBA Code for renaming filename base on month

I have monthwise excel files viz.,
Client1_Form_April
Client1_Form_May
Client1_Form_June
Client1_Form_July so…on for many clients

I want to rename these files if excel filename contains April then filename should be 01_ Client1_Form_April

If Excel filename contains May then filename should be 02_Client1_Form_May so…on Upto 12 months

I need a vba code for it with folder picker

 

[rlv01]

You can use a UDF to do the filename conversion. One example:

Function MakeFileName(ByVal InputStr As String) As String
    Dim Client As String, ClientMonth As String, ClientNO As String, Ext As String
    Dim SPos As Long

    InputStr = Trim(InputStr)
    SPos = InStrRev(InputStr, ".")

    If SPos > 0 Then
        Ext = "." & Right(InputStr, Len(InputStr) - SPos)
        InputStr = Split(InputStr, ".")(0)
    Else
        Ext = ""
    End If
    ClientMonth = Split(Trim(InputStr), "_")(UBound(Split(Trim(InputStr), "_")))
    Client = Split(Trim(InputStr), "_")(0)
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[^0-9]"
        ClientNO = Right("00" & .Replace(Client, ""), 2)
    End With

    MakeFileName = ClientNO & "_" & Client & "_Form_" & ClientMonth & Ext
End Function

Sub TestRenamingUDF()
    Dim OldName As String, NewName As String, S As String

    OldName = "Client1_Form_April"
    NewName = MakeFileName(OldName)

    MsgBox OldName & " -> " & NewName
End Sub

This only addresses the topic/title of your post. I did not provide folder-picker code.

 

[luna007]

Thanks
Can you provide folder picker code. please

 

[rlv01]

Sorry, no. But there are many examples here on MrExcel. You can use the search feature to find them.

 

[luna007]

ok thanks