Changing file names causes an error

[superfb]

Hi

i have a macro that changes the names of files in a directory i chose. But i have noticed if i want to name two files the same it causes an error........is there something i can incorporate in the coding to highlight this or to create a second file with the same name?

Sub RenameFiles()


Dim myPath As String
myPath = Range("G2")


r = 2
Do Until IsEmpty(Cells(r, 1)) And IsEmpty(Cells(r, 2))
    Name myPath & Cells(r, 1).Value As myPath & Cells(r, 2).Value
    r = r + 1
Loop




End Sub

 

[bobsan42]

first check if file exists:

[COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Dir[/FONT][/COLOR][COLOR=#303336][FONT=inherit](myPath & Cells(r, 2).Value[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]""[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Then
[/FONT][/COLOR]    Name myPath & Cells(r, 1).Value As myPath & Cells(r, 2).Value
    r = r + 1
ELSE
    '....think of some other filename
end if

 

[superfb]

Hi thank you for the reply- ideally if there is a duplicate folder name like

20070810
20070810

i would like the second folder to be named as 20070810 - 2

 

[superfb]

[TABLE="width: 495"]
<tbody>[TR]
[TD]Old File Name[/TD]
[TD]Change File name[/TD]
[/TR]
[TR]
[TD="align: left"]jak.pdf[/TD]
[TD="align: left"]CRWS-20180608.PDF[/TD]
[/TR]
[TR]
[TD="align: left"]jo.pdf[/TD]
[TD="align: left"]DWI-20100724.PDF[/TD]
[/TR]
[TR]
[TD="align: left"]ko.pdf[/TD]
[TD="align: left"]DWI-20130304.PDF[/TD]
[/TR]
[TR]
[TD="align: left"]lak.pdf[/TD]
[TD="align: left"]Facbook-200130303.pdf[/TD]
[/TR]
[TR]
[TD="align: left"]lo.pdf[/TD]
[TD="align: left"]TransferOver-20090302.pdf[/TD]
[/TR]
[TR]
[TD="align: left"]man.pdf[/TD]
[TD="align: left"]TransferOver-20090622.pdf[/TD]
[/TR]
[TR]
[TD="align: left"]mo.pdf[/TD]
[TD="align: left"]TransferOver-20090302.pdf[/TD]
[/TR]
[TR]
[TD="align: left"]pop.pdf[/TD]
[TD="align: left"]TransferOver-20090622.pdf[/TD]
[/TR]
</tbody>[/TABLE]

The VBA crashes on mo.pdf because the file name i want to change already exists and as a consequence the last two file names do not change

as a consequence if re run the macro it will not run because the files that have been changed before crashing the vba says name already exists

 

[bobsan42]

Try something like this:

Sub RenameFiles()
    Dim myPath As String: myPath = Range("G2")
    Dim r As Long: r = 2
    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 fn
        r = r + 1
    Loop


End Sub

 

[superfb]

Thank you for this, would this work to change the file names in a sub folders too?

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

i get a runtime error 53 "File not found"...

also run time error 75 path error

 

[bobsan42]

when the error pops up select debug and check the values of:
myPath
myPath & Cells(r, 1).Value
fn

see if something is wrong or out of place.
I would guess you are missing a backslash at the end of mypath:

[COLOR=#333333]myPath = Range("G2") & "\"[/COLOR]

but I cannot be sure from a distance. This is something you have to sort out.

 

[superfb]

in the cell g2 i have the file directory

[TABLE="width: 398"]
<tbody>[TR]
[TD="class: xl63, width: 398"]C:\Users\User\Desktop\Fb\[/TD]
[/TR]
</tbody>[/TABLE]


so the "" is not the issue, im quite confused.....

 

[superfb]

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

I just needed to put MyPath and is working now.......

could you kindly explain what each step of the code does? this would be a really useful for me as i can gather a deeper understanding of the code

The other issue i have noticed is that, the macro crashes.......as oppose to just changing the file name i want to for eg

6 names have changed but then the macro crashes

as a result i only require to change one file.........

can the Code incorporate this?

 

[superfb]

I think it would be good if the code could also change the names of the files in sub folders