Code is working fine on some instances and throw error on some instances.

[Jagdev Singh]

Hi Experts

I have the below code with rename the PDF files within the folder via excel cell value. The first code runs and list all the PDF names available in column A and what ever I add in column B will get renamed the old files. It works fine few of the times, but throw error file not available on the following code.

Entire code

Sub ListFiles()
Dim MyFolder As String
Dim MyFile As String
Dim j As Integer
MyFolder = "C:\DealerExam"
MyFile = Dir(MyFolder & "\*.*")
a = 0
Do While MyFile <> ""
    a = a + 1
    Cells(a, 1).Value = MyFile
    MyFile = Dir
Loop
End Sub
Sub ReName_Files()
ChDrive "C:"  ' <-- Change to your folder drive
ChDir "C:\DealerExam\"  ' <-- Change to your folder
Dim r As Integer
r = 1
Do Until IsEmpty(Cells(r, "A")) Or IsEmpty(Cells(r, "B"))
Name Cells(r, "A") As Cells(r, "B")
r = r + 1
Loop
MsgBox "All your old file names in Column 'A' have been reNamed" & vbCr & _
  "to the adjacent new name in column 'B'."
End Sub

Error code
Name Cells(r,"A") As Cells(r,"B")

Error - run-time error '53' file not found

Regards,
JD

 

[Luke M]

The Dir statement retrieves only the file name, which you seem to have setup correctly. However, in your second macro, you don't give the folder path, only the File Name. I'm betting that if the current folder (you save the workbook somewhere, and the containing folder becomes the current folder) changes between the two macros, then the 2nd one will error out. Does this work better?

Const MyFolder As String = "C:\DealerExam\"
Sub ListFiles()
Dim MyFile As String
Dim j As Integer

MyFile = Dir(MyFolder & "*.*")
a = 0
Do While MyFile <> ""
    a = a + 1
    Cells(a, 1).Value = MyFile
    MyFile = Dir
Loop
End Sub
Sub ReName_Files()
Dim r As Integer
r = 1
Do Until IsEmpty(Cells(r, "A")) Or IsEmpty(Cells(r, "B"))
    Name MyFolder & Cells(r, "A").Value As MyFolder & Cells(r, "B").Value
    r = r + 1
Loop
MsgBox "All your old file names in Column 'A' have been reNamed" & vbCr & _
        "to the adjacent new name in column 'B'."
End Sub

 

[Jagdev Singh]

Hi Luke

Thanks for the code I will check it tomorrow when I go back to office with some files.

I recently update the above code for second class..

Sub ReName_Files()
ChDrive "C:"  ' <-- Change to your folder drive
ChDir "C:\DealerExam\"  ' <-- Change to your folder
Dim r As Integer
r = 1
Do Until IsEmpty(Cells(r, "A")) Or IsEmpty(Cells(r, "B"))
Name Cells(r, "A") As Cells(r, "B")
r = r + 1
Loop
MsgBox "All your old file names in Column 'A' have been reNamed" & vbCr & _
  "to the adjacent new name in column 'B'."
End Sub

I am still facing the error will check your code tomorrow.

Regards,
JD

 

[Luke M]

Hmm, that should have fixed it. Is there something special about the file name it gets stuck on, or does it stop on the first file name?

 

[Jagdev Singh]

Hi Luke

I got the error. What is happening is that whenever the macro encountered the same name file it throws this error msg. Is it possible either to skip such case and continue renaming the other files or rename such files (1) like something at the end. When we normally you in case we have same named file in a folder.

Regards,
JD

 

[Luke M]

Good idea, we'll build an error log.

Sub ReName_Files()
ChDrive "C:"  ' <-- Change to your folder drive
ChDir "C:\DealerExam\"  ' <-- Change to your folder
Dim r As Integer
r = 1
Do Until IsEmpty(Cells(r, "A")) Or IsEmpty(Cells(r, "B"))
    On Error GoTo ErrLog
    Name Cells(r, "A") As Cells(r, "B")
SkipIt:
    On Error GoTo 0
    r = r + 1
Loop
MsgBox "All your old file names in Column 'A' have been reNamed" & vbCr & _
  "to the adjacent new name in column 'B'."
Exit Sub
ErrLog:
Debug.Print Cells(r, "A").Value & " did not get renamed to " & Cells(r, "B").Value
GoTo SkipIt
End Sub

This code will write to the Immediate window of the VBE (press Ctrl+G in the VBE if you don't see it). You can then view the results of the macro after it runs, see if there's something odd going on.

 

[Jagdev Singh]

Hi Luke
I tried the code, but is it possible in case if the same named file is already present, in case if the other file of same name encountered it should rename it with adding (1) or (2) with its name. Like in case if we have abc.doc file saved in a particular folder and we try to save the same name file then it gets saved like abc(1).doc is this possible via VBA. I am not sure if it is possible or not.
Regards,
JD

 

[Luke M]

We can try to do that. In case there's some other issue causing the problem, we'll build in an eventual "give up" test.

Sub ReName_Files()
Dim fCount As Long
Dim newName As String

ChDrive "C:"  ' <-- Change to your folder drive
ChDir "C:\DealerExam\"  ' <-- Change to your folder
Dim r As Integer
r = 1
Do Until IsEmpty(Cells(r, "A")) Or IsEmpty(Cells(r, "B"))
    fCount = 0
    newName = Cells(r, "B").Value
    On Error GoTo BadName
    Name Cells(r, "A") As newName
SkipIt:
    On Error GoTo 0
    r = r + 1
Loop
MsgBox "All your old file names in Column 'A' have been reNamed" & vbCr & _
  "to the adjacent new name in column 'B'."
Exit Sub
BadName:
If fCount >= 10 Then
    'Eventually we want to give up, in case there's some other problem going on
Debug.Print Cells(r, "A").Value & " did not get renamed to " & Cells(r, "B").Value
GoTo SkipIt
Else
    fCount = fCount + 1
    newName = Replace(Cells(r, "B").Value, ".", "(" & fCount & ").")
    Resume
End If

End Sub

 

[Jagdev Singh]

Hi Luke

I am still getting the error number 53 with the above code. Please find the tool attached I highlighted the cell which is not able to get rename with the duplication of the name in Column B.

Regards,
JD

 

[NARAYANK991]

Hi Jaggi ,

The problem has nothing to do with the code as such ; the issue is you are starting the loop with :

r = 1

so that the first file name considered will be :

Old File Name

Make the appropriate change , and the code will work.

However , the code does not take into account that the files whose names are given in column A may not exist ; this error should be trapped independent of the issue of duplicate file names in column B , so that it does not go through the loop within the error handler.

Try this :

Sub ReName_Files()
    Dim fCount As Long
    Dim newName As String
    Dim r As Integer

    ChDrive "C:\Users\Sinderjt\Desktop\New folder (6)\New folder\"  ' <-- Change to your folder drive
    ChDir "C:\Users\Sinderjt\Desktop\New folder (6)\New folder\"  ' <-- Change to your folder

    r = 2
    Do Until IsEmpty(Cells(r, "A")) Or IsEmpty(Cells(r, "B"))
      fCount = 0
      newName = Cells(r, "B").Value
      On Error Resume Next
NameIt:
      Name Cells(r, "A") As newName
      If Err > 0 Then GoTo BadName:
SkipIt:
      r = r + 1
    Loop

    MsgBox "All your old file names in Column 'A' have been reNamed" & vbCr & _
  "to the adjacent new name in column 'B'."
    Exit Sub
BadName:
    If fCount >= 10 Then
    'Eventually we want to give up, in case there's some other problem going on
      Debug.Print Cells(r, "A").Value & " did not get renamed to " & Cells(r, "B").Value
      Err.Clear
      GoTo SkipIt
    Else
      fCount = fCount + 1
      newName = Replace(Cells(r, "B").Value, ".", "(" & fCount & ").")
      GoTo NameIt:
    End If
End Sub

Narayan

 

[Jagdev Singh]

Hi Narayan

Thanks for the code. It works perfectly fine.

@Luke thanks for all your help.

Regards,
JD