Run time error 53.. File Not Found

amitcohen

Board Regular
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
 

Blade Hunter

Well-known Member
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 "
 

Blade Hunter

Well-known Member
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 :)
 

promax

New Member
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.
 

AlphaFrog

MrExcel MVP
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]
 

promax

New Member
  • 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.
 

AlphaFrog

MrExcel MVP
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:

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top