how to rename all filenames

swarupa

New Member
Joined
Jan 2, 2021
Messages
32
Office Version
  1. 2010
Platform
  1. Windows
I have folder CS with lots of excel files
ABCD_C01 April
XYXS_C02_May
I want to renaming all these files on the base of
file name contains April, may…..upto March
like
01_ABCD_C01 April
02_XYXS_C002 May
If file name contains April then add prefix 01_
If file name contains May then add prefix 02_
If file name contains June then add prefix 03_
If file name contains July then add prefix 04_
If file name contains August then add prefix 05_
If file name contains September then add prefix 06_
If file name contains October then add prefix 07_
If file name contains November then add prefix 08_
If file name contains December then add prefix 09_
If file name contains January then add prefix 10_
If file name contains February then add prefix 11_
If file name contains March then add prefix 12_
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,041
Office Version
  1. 365
Platform
  1. Windows
If all your file names end in the manner that you have shown, with an underscore and then the month name, then this code should do what you want.
Just change the file path in the code to match yours.
VBA Code:
Sub RenameFiles()

    Dim myFolder As String
    Dim myFile As String
    Dim lastUscore As Long
    Dim lastPeriod As Long
    Dim ext As String
    Dim fName As String
    Dim mnth As String
    Dim dt As Date
    Dim prefix As String
    Dim newFile As String

'   Set folder to look in
    myFolder = "C:\Temp\Test"
    
'   Designate to look for Excel file in folder
    myFile = Dir(myFolder & "\*.xl*", vbReadOnly)

'   Loop through all files in folder
    Do While myFile <> ""
'       Find location of last underscore in file name
        lastUscore = InStrRev(myFile, "_")
'       Find location of last period in file name
        lastPeriod = InStrRev(myFile, ".")
'       Get extension of file
        ext = Mid(myFile, lastPeriod + 1)
'       Get full file name without extension
        fName = Left(myFile, lastPeriod - 1)
'       Get month from file name
        mnth = Mid(myFile, lastUscore + 1, lastPeriod - lastUscore - 1)
'       Build date
        dt = DateValue(mnth & " 1, 2020")
'       Create prefix
        prefix = Format((Month(dt) + 9) Mod 12, "00") & "_"
        If prefix = "00_" Then prefix = "12_"
'       Build new file name
        newFile = myFolder & "\" & prefix & fName & "." & ext
'       Rename file
        Name myFolder & "\" & myFile As newFile
'       Move to next file
        myFile = Dir
    Loop
    
    MsgBox "Macro complete!"

End Sub
 
Solution

swarupa

New Member
Joined
Jan 2, 2021
Messages
32
Office Version
  1. 2010
Platform
  1. Windows
hoo I am really very sorry.
My File Names are
ABCD_C001_2017 April
ABCD_C001_2017 May so....on
then for next year
ABCD_C001_2018 April
ABCD_C001_2018 May
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,041
Office Version
  1. 365
Platform
  1. Windows
If they all follow that format, then simply change this line:
VBA Code:
        lastUscore = InStrRev(myFile, "_")
to this:
VBA Code:
        lastUscore = InStrRev(myFile, " ")

Note that we are just telling it to now look for the last space instead of the last underscore.
 

swarupa

New Member
Joined
Jan 2, 2021
Messages
32
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Hi Joe4
Yeesss Its works

Thanku very much:):):)
So nice of you
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,041
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

swarupa

New Member
Joined
Jan 2, 2021
Messages
32
Office Version
  1. 2010
Platform
  1. Windows
hi Joe4 can you see my another thread. can you help?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,400
Messages
5,641,924
Members
417,247
Latest member
Chitaah

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top