VBA code to change date in multiple file names in one folder

Fatabuna

New Member
Joined
Nov 8, 2011
Messages
19
Hello,

I have a folder with multiple files where part of the file name is always the last week-ending date. Something like this:

John 14-02-2016.xlsm
Peter 14-02-2016.xlsm
Steve 14-02-2016.xlsm

In this folder I also have a master file where cell B18 holds the previous week-ending date and cell B19 holds the current week-ending date. Both dates are saved as text and the format is consistent with the one used in the file name, i.e. DD-MM-YYYY.

I am trying to run a macro from the master file that will change the date in the names of all the other files saved in the same folder from the value in cell B18 to the value in cell B19. So, John 14-02-2016.xlsm should become John 21-02-2016 etc.

The folder path needs to be flexible, because the whole folder is copied weekly for an update. There are always the same files in the folder and their names never change, except for the date.

Below is a code I put together from various answers found in Google. On an attempt to run it it returns an error on the first line (very discouraging!). Can someone please help me see what I have done wrong? My knowledge of VBA is pretty limited.

Thank you very much.


Sub Rename_Files()

Const sFolder = Application.ThisWorkbook.Path


Dim sOldDate As String
Dim sNewDate As String
Dim sOldName As String
Dim sNewName As String

sOldDate = Cells(2, 18).Value
sNewDate = Cells(2, 19).Value
sOldName = Dir(sFolder & "*.*")


Do While sOldName <> ""
If InStr(1, sOldName, sOldDate) <> 0 Then
sNewName = Replace(sOldName, rOldDate, rNewDate)
Name sFolder & sOldName As sFolder & sNewName
End If
sOldName = Dir
Loop


End Sub
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Tinbendr

Well-known Member
Joined
Jul 21, 2010
Messages
997
Please use Code Tags when posting code.

Try using sOldDate = Cells(2, 18).Text instead of .Value. (and the other line as well)

F8 to step through the code. Hover over each variable to see if they are populated with the right value.
 
Last edited:

Fatabuna

New Member
Joined
Nov 8, 2011
Messages
19
Thank you for the advice. F8 stops the code on the first line, just like when I try to run it. Hovering over the variables doesn't do anything.

Would you please help by telling me how to change the code?

Thanks.
 

Fatabuna

New Member
Joined
Nov 8, 2011
Messages
19

ADVERTISEMENT

The error message says:

Compile error: Constant expression required

And the ".Path" bit is highlighted.

Thank you.
 

Tinbendr

Well-known Member
Joined
Jul 21, 2010
Messages
997
You can not use dynamic content when declaring a constant.

Just change to
Code:
Dim sFolder as String
sFolder = Application.ThisWorkbook.Path
 

Fatabuna

New Member
Joined
Nov 8, 2011
Messages
19

ADVERTISEMENT

Thanks. This certainly took care of the Const bug - the macro appears to run now and there are no error messages. Sadly it doesn't change the file names.
 

Tinbendr

Well-known Member
Joined
Jul 21, 2010
Messages
997
Change the date Format function to match.
Code:
Sub Rename_Files()

Dim sFolder As String
Dim sOldDate As String
Dim sNewDate As String
Dim sOldName As String
Dim sNewName As String

sFolder = Application.ThisWorkbook.Path & "\"

sOldDate = Format(Cells(2, 18).Value, "mmddyy")
sNewDate = Format(Cells(2, 19).Value, "mmddyy")
sOldName = Dir(sFolder & "*.*")


Do While sOldName <> ""
    If InStr(1, sOldName, sOldDate) <> 0 Then
        sNewName = Replace(sOldName, sOldDate, sNewDate)
            'Debug.Print sNewName
            Name sFolder & sOldName As sFolder & sNewName
    End If
    sOldName = Dir
Loop

End Sub
 

Fatabuna

New Member
Joined
Nov 8, 2011
Messages
19
Thank you for your time and patience, however, this macro isn't working either.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,371
Messages
5,595,784
Members
414,020
Latest member
Meghdad

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