Specific file rename macro

N

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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
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
Leith Ross said:
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
Click to expand...

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
Domenic said:
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]
Click to expand...

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
Domenic said:
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]
Click to expand...

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
You must log in or register to reply here.

Similar threads

D
  • Question Question
VBA Download URL and rename file
Replies
6
Views
212
Jeffrey Mahoney
Jeffrey Mahoney
D
  • Solved
Rename files with VBA macro wildcard
Replies
9
Views
377
Trebor76
T
S
  • Solved
Macro for renaming files en masse
Replies
8
Views
150
SimonR
S
D
  • Solved
VB search folder for older version of a file and move it to archive
Replies
7
Views
173
Davefromleeds
D
G
  • Question Question
Rename pictures and export them into JPG files
Replies
1
Views
259
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

Share this page

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