Run time error 53.. File Not Found

amitcohen

Board Regular
Joined
Jan 14, 2010
Messages
118
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
[COLOR=Red]Name path & CurrentName As path & NewName & ".csv"[/COLOR]
Next i
End Sub
What am I missing here?

Thanks for help!

Amit
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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 "
 
Upvote 0
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 :)
 
Upvote 0
I couldn't agree more!
:LOL:
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.
 
Upvote 0
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:
[COLOR=darkblue]Sub[/COLOR] ReNameFiles()
    
    [COLOR=darkblue]Dim[/COLOR] strPath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], cell [COLOR=darkblue]As[/COLOR] Range
    
    strPath = "[COLOR="#FF0000"]C:\your path here\[/COLOR]" [COLOR=green]' your path[/COLOR]
    
    [COLOR=darkblue]If[/COLOR] Right(strPath, 1) <> Application.PathSeparator [COLOR=darkblue]Then[/COLOR] strPath = strPath & Application.PathSeparator
    
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] cell [COLOR=darkblue]In[/COLOR] Range("A1", Range("A" & Rows.Count).End(xlUp))
        [COLOR=darkblue]If[/COLOR] Len(Dir(strPath & cell.Value)) [COLOR=darkblue]Then[/COLOR]
            Name (strPath & cell.Value) [COLOR=darkblue]As[/COLOR] (strPath & cell.Offset(, 1).Value)
        [COLOR=darkblue]Else[/COLOR]
            [COLOR=darkblue]If[/COLOR] MsgBox(strPath & cell.Value, vbExclamation + vbOKCancel, "File Not Found") = vbCancel [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] cell
    
    MsgBox "Done", , "Rename Files Complete"
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
Upvote 0
  • 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:
[COLOR=darkblue]Sub[/COLOR] ReNameFiles()
    
    [COLOR=darkblue]Dim[/COLOR] strPath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], cell [COLOR=darkblue]As[/COLOR] Range
    
    strPath = "[COLOR=#FF0000]C:\your path here\[/COLOR]" [COLOR=green]' your path[/COLOR]
    
    [COLOR=darkblue]If[/COLOR] Right(strPath, 1) <> Application.PathSeparator [COLOR=darkblue]Then[/COLOR] strPath = strPath & Application.PathSeparator
    
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] cell [COLOR=darkblue]In[/COLOR] Range("A1", Range("A" & Rows.Count).End(xlUp))
        [COLOR=darkblue]If[/COLOR] Len(Dir(strPath & cell.Value)) [COLOR=darkblue]Then[/COLOR]
            Name (strPath & cell.Value) [COLOR=darkblue]As[/COLOR] (strPath & cell.Offset(, 1).Value)
        [COLOR=darkblue]Else[/COLOR]
            [COLOR=darkblue]If[/COLOR] MsgBox(strPath & cell.Value, vbExclamation + vbOKCancel, "File Not Found") = vbCancel [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] cell
    
    MsgBox "Done", , "Rename Files Complete"
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

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.
 
Upvote 0
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:
[color=darkblue]Sub[/color] ReNameFiles()
    
    [color=darkblue]Dim[/color] strPath [color=darkblue]As[/color] [color=darkblue]String[/color], cell [color=darkblue]As[/color] Range
    
    [color=green]' Prompt user to select a folder[/color]
    [color=darkblue]With[/color] Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = "C:\Temp\"               [color=green]' Define default path (change to suit or omit)[/color]
        .Title = "Please Select a Folder"
        .ButtonName = "Select Folder"
        .AllowMultiSelect = [color=darkblue]False[/color]
        .Show
        [color=darkblue]If[/color] .SelectedItems.Count = 0 [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]   [color=green]' User clicked cancel[/color]
        strPath = .SelectedItems.Item(1)            [color=green]' Your path[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
       
    [color=darkblue]If[/color] Right(strPath, 1) <> Application.PathSeparator [color=darkblue]Then[/color] strPath = strPath & Application.PathSeparator
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] cell [color=darkblue]In[/color] Range("A1", Range("A" & Rows.Count).End(xlUp))
        [color=darkblue]If[/color] Len(Dir(strPath & cell.Value)) [color=darkblue]Then[/color]
            Name (strPath & cell.Value) [color=darkblue]As[/color] (strPath & cell.Offset(, 1).Value)
            cell.Offset(, 2).Value = "Renamed"   [color=green]'Column C[/color]
        [color=darkblue]Else[/color]
            cell.Offset(, 2).Value = "File not found"   [color=green]'Column C[/color]
            [color=darkblue]If[/color] MsgBox(strPath & cell.Value, vbExclamation + vbOKCancel, "File Not Found") = vbCancel [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] cell
    
    MsgBox "Done", , "Rename Files Complete"
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top