Specific file rename macro

NeoTMC

New Member
Joined
Jul 7, 2011
Messages
7
Hi all,

I need a macro to rename some files with specific names. I'll be very thankful if someone can help me, because I have to rename a lot of files every month.

I have many files named like:
John-2001021358-Company Name-June 2011-EX.xlsx
Peter-2001522398-Company Name-June 2011-EX.xlsx
... and so on in a specific shared drive.

Every month after an update I have to rename them to:
John-2001021358-Company Name-July 2011-EX.xlsx
Peter-2001522398-Company Name-July 2011-EX.xlsx
and so on every month.

Is there some one who can help me?
I figured it out with Name "C:\filename.xlsx" As "C:\filename.xlsx" but it takes so much time and its very primitive. If filenames will change, I have to change the code every time. That's why I need help.

Thank you in advance for your help!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Hello NeoTMC,

Are these files all in the same folder?
Is the company name always the same in the file names?
If the month/year is Dec 2011 will the file name become Jan 2012?

Sincerely,
Leith Ross
 
Upvote 0
Assuming that all relevant files are located in the same folder, and that the format for the file name is consistent, try the following macro...

Code:
[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] strPath [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] strFile [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] OrigDate [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] NewDate [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] Cnt [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] x [color=darkblue]As[/color] [color=darkblue]Variant[/color]

    strPath = "C:\Users\Domenic\Desktop\"  [COLOR="Green"]'change the path accordingly[/COLOR]
    
    [color=darkblue]If[/color] Right(strPath, 1) <> "\" [color=darkblue]Then[/color] strPath = strPath & "\"
    
    strFile = Dir(strPath & "*.xls")
    
    [color=darkblue]Do[/color] [color=darkblue]While[/color] Len(strFile) > 0
    
        x = Split(strFile, "-")
        
        [color=darkblue]If[/color] [color=darkblue]UBound[/color](x) = 4 [color=darkblue]Then[/color]
        
            Cnt = Cnt + 1

            OrigDate = x(3)
        
            NewDate = Format(DateAdd("m", 1, OrigDate), "mmmm yyyy")
            
            Name strPath & strFile [color=darkblue]As[/color] strPath & Replace(strFile, OrigDate, NewDate, compare:=vbTextCompare)
            
        [color=darkblue]End[/color] [color=darkblue]If[/color]
            
        strFile = Dir
        
    [color=darkblue]Loop[/color]
    
    [color=darkblue]If[/color] Cnt > 0 [color=darkblue]Then[/color]
        MsgBox "Completed...", vbInformation
    [color=darkblue]Else[/color]
        MsgBox "No such files were found...", vbInformation
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]
 
Upvote 0
Hello NeoTMC,

Are these files all in the same folder?
Is the company name always the same in the file names?
If the month/year is Dec 2011 will the file name become Jan 2012?

Sincerely,
Leith Ross

Leith Ross, all the files are in the same folder, company name is different and yes, the file name will change to Jan 2012 when the date comes.
 
Upvote 0
Assuming that all relevant files are located in the same folder, and that the format for the file name is consistent, try the following macro...

Code:
[FONT=Verdana][COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] test()

    [COLOR=darkblue]Dim[/COLOR] strPath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] strFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] OrigDate [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] NewDate [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] Cnt [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] x [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]

    strPath = "C:\Users\Domenic\Desktop\"  [COLOR=Green]'change the path accordingly[/COLOR]
    
    [COLOR=darkblue]If[/COLOR] Right(strPath, 1) <> "\" [COLOR=darkblue]Then[/COLOR] strPath = strPath & "\"
    
    strFile = Dir(strPath & "*.xls")
    
    [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] Len(strFile) > 0
    
        x = Split(strFile, "-")
        
        [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]UBound[/COLOR](x) = 4 [COLOR=darkblue]Then[/COLOR]
        
            Cnt = Cnt + 1

            OrigDate = x(3)
        
            NewDate = Format(DateAdd("m", 1, OrigDate), "mmmm yyyy")
            
            Name strPath & strFile [COLOR=darkblue]As[/COLOR] strPath & Replace(strFile, OrigDate, NewDate, compare:=vbTextCompare)
            
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            
        strFile = Dir
        
    [COLOR=darkblue]Loop[/COLOR]
    
    [COLOR=darkblue]If[/COLOR] Cnt > 0 [COLOR=darkblue]Then[/COLOR]
        MsgBox "Completed...", vbInformation
    [COLOR=darkblue]Else[/COLOR]
        MsgBox "No such files were found...", vbInformation
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
[/FONT]

Domenic, thank you for your quick answer. As I'm not able to check the code now because the files are at work, ASAP I check it, I'll give my feedback to you. Thank you again and hope your code will help me.
 
Upvote 0
Assuming that all relevant files are located in the same folder, and that the format for the file name is consistent, try the following macro...

Code:
[FONT=Verdana][COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=darkblue]Sub[/COLOR] test()[/FONT]
 
[FONT=Verdana]   [COLOR=darkblue]Dim[/COLOR] strPath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR][/FONT]
[FONT=Verdana]   [COLOR=darkblue]Dim[/COLOR] strFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR][/FONT]
[FONT=Verdana]   [COLOR=darkblue]Dim[/COLOR] OrigDate [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR][/FONT]
[FONT=Verdana]   [COLOR=darkblue]Dim[/COLOR] NewDate [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR][/FONT]
[FONT=Verdana]   [COLOR=darkblue]Dim[/COLOR] Cnt [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR][/FONT]
[FONT=Verdana]   [COLOR=darkblue]Dim[/COLOR] x [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR][/FONT]
 
[FONT=Verdana]   strPath = "C:\Users\Domenic\Desktop\"  [COLOR=green]'change the path accordingly[/COLOR][/FONT]
 
[FONT=Verdana]   [COLOR=darkblue]If[/COLOR] Right(strPath, 1) <> "\" [COLOR=darkblue]Then[/COLOR] strPath = strPath & "\"[/FONT]
 
[FONT=Verdana]   strFile = Dir(strPath & "*.xls")[/FONT]
 
[FONT=Verdana]   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] Len(strFile) > 0[/FONT]
 
[FONT=Verdana]       x = Split(strFile, "-")[/FONT]
 
[FONT=Verdana]       [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]UBound[/COLOR](x) = 4 [COLOR=darkblue]Then[/COLOR][/FONT]
 
[FONT=Verdana]           Cnt = Cnt + 1[/FONT]
 
[FONT=Verdana]           OrigDate = x(3)[/FONT]
 
[FONT=Verdana]           NewDate = Format(DateAdd("m", 1, OrigDate), "mmmm yyyy")[/FONT]
 
[FONT=Verdana]           Name strPath & strFile [COLOR=darkblue]As[/COLOR] strPath & Replace(strFile, OrigDate, NewDate, compare:=vbTextCompare)[/FONT]
 
[FONT=Verdana]       [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR][/FONT]
 
[FONT=Verdana]       strFile = Dir[/FONT]
 
[FONT=Verdana]   [COLOR=darkblue]Loop[/COLOR][/FONT]
 
[FONT=Verdana]   [COLOR=darkblue]If[/COLOR] Cnt > 0 [COLOR=darkblue]Then[/COLOR][/FONT]
[FONT=Verdana]       MsgBox "Completed...", vbInformation[/FONT]
[FONT=Verdana]   [COLOR=darkblue]Else[/COLOR][/FONT]
[FONT=Verdana]       MsgBox "No such files were found...", vbInformation[/FONT]
[FONT=Verdana]   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR][/FONT]

Domenic,

I tried your code, but got the error:

Run-time error '13':
Type mismatch

And the following row was highlighted:
NewDate = Format(DateAdd("m", 1, OrigDate), "mmmm yyyy")

Any advise how to fix it?
 
Upvote 0
I've made something:

Sub RenameFiles()
Dim List() As String, cnt As Long
Dim fname As String
Dim NewMonth As String
OldMonth = Month(Now) - 1
NewMonth = Month(Now)
cnt = 0
fname = Dir("C:\Temp\*-" & OldMonth & "2011-MD.xlsx")
Do
cnt = cnt + 1
ReDim Preserve List(1 To cnt)
List(cnt) = "C:\Temp\" & fname
fname = Dir()
Loop While fname <> ""
For i = 1 To cnt
Name List(i) As Replace(List(i), OldMonth, NewMonth)
Next
End Sub

But got an error:
Run-time error '5':
Invalid procedure call or argument
and highlights the following row:
fname = Dir()

Any ideas how to fix that?
 
Upvote 0
The following macro loops through each Excel file in the folder whose filename fits the specified pattern, and checks whether the filename contains the previous month in the format "mmmm yyyy". If so, the previous month is changed to the current month.

Code:
[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] strPath [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] strFile [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] strPrevMonth [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] strCurMonth [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] Cnt [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    strPrevMonth = Format(DateAdd("m", -1, Date), "mmmm yyyy")
    
    strCurMonth = Format(Date, "mmmm yyyy")

    strPath = "C:\Temp\"
    
    [color=darkblue]If[/color] Right(strPath, 1) <> "\" [color=darkblue]Then[/color] strPath = strPath & "\"
    
    strFile = Dir(strPath & "*-*-MD.xlsx")  [color=green]'change accordingly[/color]
    
    [color=darkblue]Do[/color] [color=darkblue]While[/color] Len(strFile) > 0
    
        [color=darkblue]If[/color] InStr(1, strFile, strPrevMonth, vbTextCompare) > 0 [color=darkblue]Then[/color]
        
            Cnt = Cnt + 1
        
            Name strPath & strFile [color=darkblue]As[/color] strPath & Replace(strFile, strPrevMonth, strCurMonth, Compare:=vbTextCompare)
            
        [color=darkblue]End[/color] [color=darkblue]If[/color]
            
        strFile = Dir
        
    [color=darkblue]Loop[/color]
    
    [color=darkblue]If[/color] Cnt > 0 [color=darkblue]Then[/color]
        MsgBox "Completed...", vbInformation
    [color=darkblue]Else[/color]
        MsgBox "No such files were found...", vbInformation
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

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
Back
Top