Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Run time error 53.. File Not Found

  1. #1
    Board Regular amitcohen's Avatar
    Join Date
    Jan 2010
    Location
    Israel & South Africa
    Posts
    118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Run time error 53.. File Not Found

    Hi Guys
    Hope you van help me fix the error here

    The following macro forcing a file name change
    In Column A There is a list of files stored in a folder: change_names.
    (All files are in CSV format.)
    In Column B there is a list on new name for each file.

    This macro should force the change, but from some reasons,
    it pops-up a run time error 53.. File Not Found
    And the debug highlight the red line.

    Code:
    Sub ReNameFiles1()
    Dim path As String, filespec As String
    Dim CurrentName As String, NewName As String
    path = "C:\Users\WEB\Desktop\change_names" ' your path
    For i = 1 To 100 ' your range
    Range("A1").Value = Range("A" & i).Value
    Range("B1").Value = Range("B" & i).Value
    CurrentName = Range("A1").Value
    NewName = Range("B1").Value
    Name path & CurrentName As path & NewName & ".csv"
    Next i
    End Sub
    What am I missing here?

    Thanks for help!

    Amit
    Using Excel 2016
    Amit Cohen

  2. #2
    Board Regular Blade Hunter's Avatar
    Join Date
    Mar 2008
    Location
    Sydney, Australia
    Posts
    3,147
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run time error 53.. File Not Found

    There is no slash on the end of your path

    It will be looking for:

    C:\Users\WEB\Desktop\change_namesYourFile.csv

    instead of:

    C:\Users\WEB\Desktop\change_names\YourFile.csv

    Just change this line:
    path = "C:\Users\WEB\Desktop\change_names" ' your path

    and put a \ on the end before the "
    .select, overused and inefficient.

  3. #3
    Board Regular amitcohen's Avatar
    Join Date
    Jan 2010
    Location
    Israel & South Africa
    Posts
    118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run time error 53.. File Not Found

    Is it that simple!?


    Macro runs perfect!
    Thanks,

    Amit.
    Using Excel 2016
    Amit Cohen

  4. #4
    Board Regular Blade Hunter's Avatar
    Join Date
    Mar 2008
    Location
    Sydney, Australia
    Posts
    3,147
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run time error 53.. File Not Found

    I have spent hours on little ones like these before, sometimes the more you stare at it the harder it is to see and it just takes a fresh pair of eyes
    .select, overused and inefficient.

  5. #5
    Board Regular amitcohen's Avatar
    Join Date
    Jan 2010
    Location
    Israel & South Africa
    Posts
    118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run time error 53.. File Not Found

    I couldn't agree more!
    Using Excel 2016
    Amit Cohen

  6. #6
    New Member
    Join Date
    Apr 2015
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run time error 53.. File Not Found

    Quote Originally Posted by amitcohen View Post
    I couldn't agree more!
    I want to use the prog to rename a number of files in a given folder. However, each of the file type is different. I know the new file names along with extension, as also I have the list of old file with extensions included.

    What lines do I change in the given code - I am quite a novice so if the lines to replace are highlighted, with new lines to be used, that will be very useful.

    thanks in advance.

  7. #7
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,838
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Run time error 53.. File Not Found

    Quote Originally Posted by promax View Post
    I want to use the prog to rename a number of files in a given folder. However, each of the file type is different. I know the new file names along with extension, as also I have the list of old file with extensions included.

    What lines do I change in the given code - I am quite a novice so if the lines to replace are highlighted, with new lines to be used, that will be very useful.

    thanks in advance.

    • Change the path to your given folder
    • Column A has the current file names with extension.
    • Column B has the new file names with extension.


    Code:
    Sub ReNameFiles()
        
        Dim strPath As String, cell As Range
        
        strPath = "C:\your path here\" ' your path
        
        If Right(strPath, 1) <> Application.PathSeparator Then strPath = strPath & Application.PathSeparator
        
        For Each cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
            If Len(Dir(strPath & cell.Value)) Then
                Name (strPath & cell.Value) As (strPath & cell.Offset(, 1).Value)
            Else
                If MsgBox(strPath & cell.Value, vbExclamation + vbOKCancel, "File Not Found") = vbCancel Then Exit Sub
            End If
        Next cell
        
        MsgBox "Done", , "Rename Files Complete"
        
    End Sub
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  8. #8
    New Member
    Join Date
    Apr 2015
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run time error 53.. File Not Found

    Quote Originally Posted by AlphaFrog View Post
    • Change the path to your given folder
    • Column A has the current file names with extension.
    • Column B has the new file names with extension.


    Code:
    Sub ReNameFiles()
        
        Dim strPath As String, cell As Range
        
        strPath = "C:\your path here\" ' your path
        
        If Right(strPath, 1) <> Application.PathSeparator Then strPath = strPath & Application.PathSeparator
        
        For Each cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
            If Len(Dir(strPath & cell.Value)) Then
                Name (strPath & cell.Value) As (strPath & cell.Offset(, 1).Value)
            Else
                If MsgBox(strPath & cell.Value, vbExclamation + vbOKCancel, "File Not Found") = vbCancel Then Exit Sub
            End If
        Next cell
        
        MsgBox "Done", , "Rename Files Complete"
        
    End Sub
    Dear AlphaFrog, this worked smoothly, many thanks.

    May I ask for one more help - can this be modified in a way where the file path is provided in a box or a placeholder in the excel sheet instead of going and editing vba script. On a day to day basis, the operations staff would use this more frequently and they would find this useful.

    Likewise, if you can show me how to create a 'Run Macro' button (or rather call it something like "Rename File Now" on the excel sheet itself, that would be great.

    What I liked most was that it actually gives a message box with the files it does not find. It could be most useful if this is listed in a separate sheet for Operations to report back to their Managers to look at exception handling.

    thanks in advance for all your help here.

  9. #9
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,838
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Run time error 53.. File Not Found

    Quote Originally Posted by promax View Post
    Dear AlphaFrog, this worked smoothly, many thanks.

    May I ask for one more help - can this be modified in a way where the file path is provided in a box or a placeholder in the excel sheet instead of going and editing vba script. On a day to day basis, the operations staff would use this more frequently and they would find this useful.

    Likewise, if you can show me how to create a 'Run Macro' button (or rather call it something like "Rename File Now" on the excel sheet itself, that would be great.

    What I liked most was that it actually gives a message box with the files it does not find. It could be most useful if this is listed in a separate sheet for Operations to report back to their Managers to look at exception handling.

    thanks in advance for all your help here.
    You're welcome. That's really three more requests.

    Adding a Command button to a worksheet


    Prompt for file path and label "file not found" in column C
    Code:
    Sub ReNameFiles()
        
        Dim strPath As String, cell As Range
        
        ' Prompt user to select a folder
        With Application.FileDialog(msoFileDialogFolderPicker)
            .InitialFileName = "C:\Temp\"               ' Define default path (change to suit or omit)
            .Title = "Please Select a Folder"
            .ButtonName = "Select Folder"
            .AllowMultiSelect = False
            .Show
            If .SelectedItems.Count = 0 Then Exit Sub   ' User clicked cancel
            strPath = .SelectedItems.Item(1)            ' Your path
        End With
           
        If Right(strPath, 1) <> Application.PathSeparator Then strPath = strPath & Application.PathSeparator
        
        For Each cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
            If Len(Dir(strPath & cell.Value)) Then
                Name (strPath & cell.Value) As (strPath & cell.Offset(, 1).Value)
                cell.Offset(, 2).Value = "Renamed"   'Column C
            Else
                cell.Offset(, 2).Value = "File not found"   'Column C
                If MsgBox(strPath & cell.Value, vbExclamation + vbOKCancel, "File Not Found") = vbCancel Then Exit Sub
            End If
        Next cell
        
        MsgBox "Done", , "Rename Files Complete"
        
    End Sub
    Last edited by AlphaFrog; Apr 13th, 2015 at 10:27 AM.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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