Please advise - I wrote a code in VBA that iterates through files in certain directory, rename them, and write the status to Excel. I would like to edit the code so that if the command Name returns error (For example if NewFileName already exists), then the parameter status would be "Fail", and continue to the next iteration. Could anyone help me how to do so?
Sub RenameFiles()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer
Dim directory As String
Dim OldFileName As String
Dim NewFileName As String
Dim illegal As String
Dim legal As String
Dim status As String
directory = (ThisWorkbook.Worksheets(4).Range("G6").Value) & "\"
illegal = (ThisWorkbook.Worksheets(4).Range("G8").Value)
legal = (ThisWorkbook.Worksheets(4).Range("G10").Value)
'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder(directory)
i = 1
'loops through each file in the directory and rename them
For Each objFile In objFolder.Files
OldFileName = objFile.Name
NewFileName = Replace(OldFileName, illegal, legal)
'Rename
Name directory & OldFileName As directory & NewFileName
'print old file name
Cells(i + 1, 1) = OldFileName
'print new file name
Cells(i + 1, 2) = NewFileName
'print new file path
Cells(i + 1, 3) = directory & NewFileName
'print status
If (OldFileName <> NewFileName) Then
status = "Success"
'Ifelse ()
Else
status = "No Change"
End If
Cells(i + 1, 3) = status
i = i + 1
Next objFile
End Sub
Bookmarks