VBA Code help

[superfb]

Hi guys,

Was hoping if someone could help me with this code, it seems to run fun, but there are times i get the following error:

Run-time error '53' file not found

The file is in there, but i'm not sure if the file name for e.g Tota - Pay Hours - Overtime - 22.xls is causing the error due to the "-"

Can someone please assist if there code below needs to be changed as it could be causing some form of confusion?

thanks

Sub RenameFiles2()
    Dim myPath As String: myPath = Range("B3")
    Dim r As Long: r = 8
    Dim i As Long
    Dim fn As String, ext As String, fn2 As String
    
    Do Until IsEmpty(Cells(r, 1)) And IsEmpty(Cells(r, 2))
        fn = Cells(r, 2).Value
        If Dir(myPath & fn) <> "" Then
            ext = Right(fn, InStrRev(fn, ".") - 1)
            fn2 = myPath & Left(fn, Len(fn) - InStrRev(fn, "."))
            i = 1
            Do Until Dir(fn2 & " - " & i & "." & ext) = ""
                i = i + 1
            Loop
            fn = fn2 & " - " & i & "." & ext
        End If
        Name myPath & Cells(r, 1).Value As myPath & fn
        r = r + 1
    Loop


End Sub

 

[6StringJazzer]

The only line that is attempting to use that string as a file name is the Name statement. Is that the line that is raising the error? (When reporting a VBA error, always indicate which line is raising the error.)

The "-" character is legal and is not causing your problem. The problem is exactly what the message says: The file name

myPath & Cells(r, 1).Value

is not found. When running this code, either set a Watch or stop the debugger at that line of code to see what string is being built in your code, and compare that to available files. If this string is not not what you expected it to be, then we have to walk back through the code that builds it to see where it's going wrong.

It's not possible to troubleshoot this much because we can't run it without the data you are using in your worksheet a list of filenames you have.

 

[superfb]

The only line that is attempting to use that string as a file name is the Name statement. Is that the line that is raising the error? (When reporting a VBA error, always indicate which line is raising the error.)

The "-" character is legal and is not causing your problem. The problem is exactly what the message says: The file name

myPath & Cells(r, 1).Value

is not found. When running this code, either set a Watch or stop the debugger at that line of code to see what string is being built in your code, and compare that to available files. If this string is not not what you expected it to be, then we have to walk back through the code that builds it to see where it's going wrong.

It's not possible to troubleshoot this much because we can't run it without the data you are using in your worksheet a list of filenames you have.

Hi, that is correct it is the Name code line where the VBA stops

I have attached screenshots of the file names in the workbook Vs what is showing in the code, i assume it isnt reading it correctly?

 

[6StringJazzer]

You are showing a list of filenames stored in Excel, but I have no idea if those files actually exist. Your error is caused by a file that does not exist.

The only differences I see in Current Name and Change Name To is that some letters were made uppercase. There is nothing in your code to do this. I am at a loss to understand what your code is supposed to do.

Your screenshot of the code shows the value of variable fn but I think the problem is the value of myPath & Cells(r, 1).Value, which you did not show.

Your code takes the name from the cell and does backflips to change it in some way. There may be some error in how this works. Even if the original file existed, your screenshot shows a value of fn different than what your other screenshot shows.

If you paste the cells with filenames into your post (always better than a picture) I will try to run your code. I am not going to type in your filenames from scratch.