Renaming the file name (or part of the file name) from a list

**bbottjen** · 08-28-2012, 10:01 PM

I need help with a VBA/macro that will rename multiple files from an Excel list (1) rename the entire file name (2) rename part of the file name.

my path to the original file is C:\Temp\1234_customer#_$0.xlsx

(1) Change the whole file name to 4321_customer#_120.xlsx
(2) Change only part of the name from 1234 to 4321

Attached file is an example of the list structure for both of my problems.

Any help is greatly appreciated!

**patel45** · 08-29-2012, 02:32 AM

this is an example, you can modify it for your goal

Sub renamefile()

        Dim fs, f
        Set fs = CreateObject("Scripting.FileSystemObject")
        fpath = "D:\DATI\prova" ' <<<<<< to change
        Set f = fs.getfolder(fpath)
        Set NFile = f.Files
        For Each pf1 In NFile
            NomeFile = pf1.Name
            newname = ' <<<<<< here your code
            Name pf1 As fpath & "\" & newname

        Next
    End Sub

**bbottjen** · 09-02-2012, 02:26 PM

I am struggling to get this to work. I understand the fpath is the destination/renaming folder, but I am trying to reference column B on the attachment as the new file name. I am new to VBA so any help would be greatly appreciated- Thanks in advance!

**jindon** · 09-02-2012, 08:30 PM

Not sure about your list(2) though
This is for List one.
Sheet1("Rename entire file") must be active before you run the code

Sub test()
    Dim r As Range, fn As String, msg As String
    Const myDir As String = "C:\Temp\"
    For Each r In Range("a2", Range("a" & Rows.Count).End(xlUp))
        fn = Dir(myDir & r.Value)
        If fn <> "" Then
            Name myDir & r.Value As myDir & r(, 2).Value
        Else
            msg = msg & vbLf & r.Value
        End If
    Next
    If Len(msg) Then MsgBox "Files not found" & msg
End Sub