VBA - Partially renaming Excel files in a folder-CodePudding

I need help regarding renaming excel files partially. I have about 40 of these in a folder which needs partial renaming every month.

The files are saved like:
06.01.2022 - 06.30.2022 - 1458 - ABCD
06.01.2022 - 06.30.2022 - 2579 - EFGH

and I need only the month to be changed like:
07.01.2022 - 07.31.2022 - 1458 - ABCD
07.01.2022 - 07.31.2022 - 2579 - EFGH

I have been trying to use the code below but it is bringing Run-Time error as file not found. I tried looking online but every VBA code seems to change the name entirely . I want the files to have their respective file name and be successful in partially renaming them. Can you please help me?

Thank you very much in advance for the help.

Sub ReNaming()

Dim xlFile As Variant
Dim nFile As String

xlFile = Dir("C:\Test\*.xls*") '/* Folder that contains the files */

Do While xlFile <> "" '/* check if anything is returned */
    If InStr(xlFile, "06.01.2022 - 06.30.2022") <> 0 Then '/* check if there is 'QFR' in the filename
        nFile = VBA.Replace(xlFile, "06.01.2022 - 06.30.2022", "07.01.2022 - 07.31.2022") '/* replace if there is */
        Name xlFile As nFile '/* rename the file */
    End If
    xlFile = Dir '/* Test for more */
Loop
End Sub

CodePudding user response：

You will need to use the full path like below

Public Sub runthis()

Dim xlFile As Variant
Dim nFile As String
Dim directory As String

directory = "C:\Test\"
xlFile = Dir(directory & "*.xls") '/* Folder that contains the files */

Do While xlFile <> "" '/* check if anything is returned */
    If InStr(xlFile, "06.01.2022 - 06.30.2022") <> 0 Then '/* check if there is 'QFR' in the filename
        nFile = VBA.Replace(xlFile, xlFile, "1" & xlFile) '/* replace if there is */
        Name directory & xlFile As directory & nFile '/* rename the file */
    End If
    xlFile = Dir '/* Test for more */
Loop

End Sub