Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post

    Post Code is throwing - error 53

    I am encounter run-time error '53' file not found when execute rename_files().When i click debug,it link me to

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

    Could you please help me to fix this error.

    The code is to have a spreadsheet with two columns, Col A showing a list of current file names in a particular directory, and Col B the names I want these files to be renamed to. I am dealing with word doc files in this case.

    Code:
    Sub List_Files()
    Dim MyFolder As String
    Dim MyFile As String
    Dim a As Integer
    MyFolder = "C:\DealerExam\" ' <-- Change to your folder
    MyFile = Dir(MyFolder & "*.*")
    a = 0
    Do While MyFile <> ""
        a = a + 1
        Cells(a, 1).Value = MyFile
        MyFile = Dir
    Loop
    MsgBox "You may now list your new names for each file in Column 'B'." & vbCr & vbCr & _
            "All cells must have valid file name.  Run 'ReName_Files' Sub when ready."
    End Sub
    Sub ReName_Files()
    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

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Jaggi

    Use this:

    Code:
    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
    zeddy

  3. #3
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi Zeddy

    Thanks for the code it works flowlessly.

    I have a small doubt here why we have two location chDrive and chDir. The reason why I am asking you this question, because I want to rename the files in the same location and why to add the same location twice.

    Regards,
    Jaggi

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Jaggi

    If you choose a folder on a different drive, for example, E:\Test\ , then you need to change to that Drive as well (if you were last using say, the C: drive).
    If you were last using the C: drive, then you don't need to have ChDrive "C:" as you are already 'on' that drive. Using both is just to be sure.

    zeddy

  5. The Following User Says Thank You to zeddy For This Useful Post:

    Jaggi (2015-02-18)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •