File name change

[MN_Excel]

I have about 100 different excel files that need to be renamed so that they end with the month and year of the file (ie. Revenue Jan12). Does anyone know of a way to make a massive change to the end of each file or do I need to do each individually?

Thanks,
Jeff

 

[jim may]

What are there current names?

 

[MN_Excel]

All the names vary, but all must eventually end with "MMMYY"

 

[dermie_72]

Are all the files stored in the same directory?
Will the dates be the same, if they're different what is the deciding factor?

 

[MN_Excel]

Yes, all the files are stored in the same directory and all the files within that directory will need to have the same MMMYY attached to the end.

 

[dermie_72]

I have briefly tested this and it worked for me. See how you go with this. You will need to modify some of it to suit, however this could be streamlined further if you want...but it works which is the main point :D

Sub File_name_change()
'For MN_Excel http://www.mrexcel.com/forum/excel-questions/672666-file-name-change.html
Dim filesystem As Object, myfolder As Object
Dim myfiles As Object, myfile As Object, Pastefile As Object
Dim Dte As String
Dim Strt As String
Dim cncat As String

On Error Resume Next

Set filesystem = CreateObject("Scripting.filesystemobject")
Set myfolder = filesystem.getfolder("C:\LOCATION OF THE FOLDER") 'Sets the folder where the files are
Set myfiles = myfolder.Files
Dte = "0512" 'Whatever the constant date is
For Each myfile In myfiles
    Strt = myfile.Name
    cncat = Strt & Dte
    myfile.Name = cncat & ".xls" 'Choose the file extension that suits.
Next
End Sub