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