Rename part of the excel file name with VBA

[PaulskinX1]

Hello and thanks for reading,

I am trying to rename slightly the name of each file in a specific folder. I found online a bit of code which I re-adjusted slightly to get the below:

Sub ChangeFileName2()

Dim folderName As String
Dim FSOLibrary As Object
Dim FSOFolder As Object
Dim FSOFile As Object
Dim NewFile As String
Dim CurrentYear As String

'Set the file name to a variable
folderName = "C:\Users\PROJECTS 2021\HE & BBI\"

'Set all the references to the FSO Library
Set FSOLibrary = CreateObject("Scripting.FileSystemObject")
Set FSOFolder = FSOLibrary.GetFolder(folderName)
Set FSOFile = FSOFolder.Files

CurrentYear = "2003"

'Use For Each loop to loop through each file in the folder
For Each FSOFile In FSOFile

NewFile = FSOFile.Name
NewFile = Replace(NewFile, "2002", CurrentYear)

Next

'Release the memory
Set FSOLibrary = Nothing
Set FSOFolder = Nothing
Set FSOFile = Nothing

End Sub

However it doesnt do anything - it doesnt change the name.

When I change NewFile = FSOFile.Name to FSOFile.Name = NewFile in For each loop , it produces error - stops on line FSOFile.Name = NewFile and the error is:

 

[rollis13]

You could use the FSO function .MoveFile to update the name of the file. LINK
Instead of:

For Each FSOFile In FSOFile
    NewFile = FSOFile.Name
    NewFile = Replace(NewFile, "2002", CurrentYear)
Next

use:

For Each FSOFile In FSOFile
    FSOLibrary.MoveFile folderName & FSOFile.Name, folderName & Replace(FSOFile.Name, "2002", CurrentYear)
Next

 

[PaulskinX1]

Hello and thanks for reading,

I am trying to rename slightly the name of each file in a specific folder. I found online a bit of code which I re-adjusted slightly to get the below:

Sub ChangeFileName2()

Dim folderName As String
Dim FSOLibrary As Object
Dim FSOFolder As Object
Dim FSOFile As Object
Dim NewFile As String
Dim CurrentYear As String

'Set the file name to a variable
folderName = "C:\Users\PROJECTS 2021\HE & BBI\"

'Set all the references to the FSO Library
Set FSOLibrary = CreateObject("Scripting.FileSystemObject")
Set FSOFolder = FSOLibrary.GetFolder(folderName)
Set FSOFile = FSOFolder.Files

CurrentYear = "2003"

'Use For Each loop to loop through each file in the folder
For Each FSOFile In FSOFile

NewFile = FSOFile.Name
NewFile = Replace(NewFile, "2002", CurrentYear)

Next

'Release the memory
Set FSOLibrary = Nothing
Set FSOFolder = Nothing
Set FSOFile = Nothing

End Sub

However it doesnt do anything - it doesnt change the name.

When I change NewFile = FSOFile.Name to FSOFile.Name = NewFile in For each loop , it produces error - stops on line FSOFile.Name = NewFile and the error is:

View attachment 74386

I am replying to my post - hope it is allowed;


I now made the procedure above work but only if I open each workbook and save as file with new name - changes to the loop section as below:

For Each FSOFile In FSOFile


Workbooks.Open FSOFile, , , , "Pass"
Savein = ActiveWorkbook.Path
NewFile = FSOFile.Name
FinalFileName = Replace(NewFile, "2022", "2023")
ActiveWorkbook.SaveAs Savein & "\" & FinalFileName
ActiveWorkbook.Close

Next


I dont think this is the best approach but it works - would appreciate some feedback on how to make it better/quicker/more elegant.

 

[PaulskinX1]

You could use the FSO function .MoveFile to update the name of the file. LINK
Instead of:

For Each FSOFile In FSOFile
    NewFile = FSOFile.Name
    NewFile = Replace(NewFile, "2002", CurrentYear)
Next

use:

For Each FSOFile In FSOFile
    FSOLibrary.MoveFile folderName & FSOFile.Name, folderName & Replace(FSOFile.Name, "2002", CurrentYear)
Next

Thank you however it generates the following error:

 

[rollis13]

No idea on how you used my suggestion, worked for me before posting (and after).