Macros to Grab File Name, List and Rename in Folder Getting 'File not found' Error

[jbenfrancis1]

I have these two macros working together to grab the file names in a folder and insert them onto sheet "File Names" in column A. I then have a list of Names I want them to be changed to in Column B. However when I run the renamefiles macro I receive File Not Found errror. No files names are duplicates.

Sub getfile()

Dim myPath As String
Dim myFile As String

myPath = "C:\Users\joe\Desktop\2.23"
myFile = Dir(myPath & "\*.csv")

r = 1
Do While myFile <> ""
   Worksheets("File Names").Cells(r, 1).Value = myFile
   r = r + 1
   myFile = Dir

Loop

End Sub

Sub RenameFile()

Dim myPath As String
myPath = "C:\Users\joe\Desktop\2.23"

Dim ws As Worksheet
Set ws = Worksheets("File Names")
r = 1

Do Until IsEmpty(ws.Cells(r, 1)) And IsEmpty(ws.Cells(r, 2))

   Name myPath & ws.Cells(r, 1).Value As myPath & ws.Cells(r, 2).Value
    
    
    r = r + 1
Loop

End Sub

 

[Joe4]

You forgot the backslash after "myPath" in your second procedure.

You accounted for it here in your first procedure:

myFile = Dir(myPath & "\*.csv")

but it looks like you didn't in your second procdure.

Note: A quick easy way to see what it is trying to do is to temporary replace your rename step with a message box tht returns the name you are trying to copy from and to, i.e.
temporarily replace this line:

   Name myPath & ws.Cells(r, 1).Value As myPath & ws.Cells(r, 2).Value

with this:

    MsgBox myPath & ws.Cells(r, 1).Value & vbCrLf & myPath & ws.Cells(r, 2).Value

and run it to see what file names it is trying to use.

 

[jbenfrancis1]

You forgot the backslash after "myPath" in your second procedure.

You accounted for it here in your first procedure:

myFile = Dir(myPath & "\*.csv")

but it looks like you didn't in your second procdure.

Note: A quick easy way to see what it is trying to do is to temporary replace your rename step with a message box tht returns the name you are trying to copy from and to, i.e.
temporarily replace this line:

   Name myPath & ws.Cells(r, 1).Value As myPath & ws.Cells(r, 2).Value

with this:

    MsgBox myPath & ws.Cells(r, 1).Value & vbCrLf & myPath & ws.Cells(r, 2).Value

and run it to see what file names it is trying to use.

Im not exactly following where the back slash would need to go after mypath in procedure 2

 

[Joe4]

Im not exactly following where the back slash would need to go after mypath in procedure 2

Did you try my suggestion regarding the message box?
This will show you exactly what the two file path & names you are building.
Once you see that, I think the issue will become obvious to you.

This is also a good debugging technique to learn. When something isn't working the way you expect, use Message Boxes to see exactly what the value is that you are building.

 

[jbenfrancis1]

Ah ha! Yes I see, thank you so much!

 

[Joe4]

You are welcome.

The quickest/easiest fix is to just place a backslash at the end of your path, i.e.

Sub RenameFile()

Dim myPath As String
myPath = "C:\Users\joe\Desktop\2.23\"

Dim ws As Worksheet
Set ws = Worksheets("File Names")
r = 1

Do Until IsEmpty(ws.Cells(r, 1)) And IsEmpty(ws.Cells(r, 2))

   Name myPath & ws.Cells(r, 1).Value As myPath & ws.Cells(r, 2).Value
    
    
    r = r + 1
Loop

End Sub