Changing File Names in Excel Folder

snb: Thanks for your help. This worked on the first four files and the last one, and then I got an error message: "Run-time error '58': File already exists. When I click on debug it highlights: fl.Name = Replace(fl.Name, "_2010", "_2011"). Is there something I need to correct? JPDutch

Tigeravatar: thanks for your help. I tried this out, but nothing happened. Any suggestions? JPDutch

1. Open the file attached.
2. Run the macro GetFileNames and select the folder containing the files
Path will be imported in column A
File Names will be imported in column B
3. Write the new file name in column C
4. Run the macro ChangeFileNames

This is the Macro that will get the file names & path into excel

Sub GetFileNames()
    Dim xRow As Long
    Dim oSht As Worksheet
    Set oSht = ThisWorkbook.ActiveSheet
    Dim xDirect$, xFname$, InitialFoldr$
    xRow = oSht.UsedRange.Rows.Count + 1
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = Application.DefaultFilePath & "\"
        .Title = "Please select a folder to list Files from"
        .InitialFileName = InitialFoldr$
        .Show
        If .SelectedItems.Count <> 0 Then
            xDirect$ = .SelectedItems(1) & "\"
            xFname$ = Dir(xDirect$, 7)
            Do While xFname$ <> ""
                oSht.Range("A" & xRow) = xDirect$
                oSht.Range("B" & xRow) = xFname$
                xRow = xRow + 1
                xFname$ = Dir
            Loop
        End If
    End With
    ThisWorkbook.Save
End Sub

And this one will rename them

Sub ChangeFileNames()
Dim oSht As Worksheet
Set oSht = ThisWorkbook.ActiveSheet
Dim i, r As Integer
Dim strPATH As String
Dim strOLD As String
Dim strNEW As String
Dim strCMD As String


r = oSht.Cells.SpecialCells(xlCellTypeLastCell).Row
If r < 2 Then Exit Sub
On Error Resume Next
With oSht

    For i = 2 To r
        strPATH = .Range("A" & i)
        strOLD = strPATH & .Range("B" & i)
        strNEW = .Range("C" & i)
        strCMD = "rename " & Chr(34) & strOLD & Chr(34) & " " & Chr(34) & strNEW & Chr(34)
        VBA.Shell "C:\Windows\System32\cmd.exe /c " & strCMD, vbHide
        
    Next
End With

End Sub

Please mark the thread as SOLVED if it is.

You can do this easily with a batch file as follows:

From the Command Prompt (available under accessories) run the following from the directory containing the files:

ren Branch????_2010.* Branch????_2011.*

You change directories with the command cd DIRNAME where DIRNAME is the directory name. This can get a bit weird if you are not used to batch commands, so alternately, you can place a text file in the directory which contains the above, rename it to RENAME.BAT, and then double-click it to execute the command.

---GJCase

Jpdutch did you try the macro I have posted?

Kelshaer:

Thank you; I did try it, and it works, but is there a way to avoid retyping? I have hundreds of these files and I'm hoping to save time and effort. Thanks again! JPDutch

Here you Go.
Run the first macro and it will pull the names as the previous one and will automatically type the new name in column C.

Then Run the other Macro to rename them.
Please Mark the thread as solved if it was.

Thanks snb. I'm leaving work and running home...takes about an hour. I'll try this once I get setup tonight.

In the GetFileNames() I'm getting a compile error: variable not defined. "i" below is the variable it highlights.

oSht.Range("C" & xRow) = VBA.Replace(.Range("B" & i), "_2010", "_2011")

Two questions. I've had to change the location from our H:drive to our I:drive. Do I need to make any corrections in the information you gave me? Next, I'm still using the older version of Excel: xls, not xlsm. Will that make a difference?

Thanks in advance for your help!

Open the code and go under the second line where it says Sub GetFileNames() and type the following line:

Dim i as integer

Sorry buddy for being late. You will love this one. It will ask for the folder so you can select any location. It will automatically list the new file names in column C.

Sub GetFileNames()
    Dim xRow As Long
    Dim oSht As Worksheet
    Set oSht = ThisWorkbook.ActiveSheet
    Dim xDirect$, xFname$, InitialFoldr$
    xRow = oSht.UsedRange.Rows.Count + 1
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = Application.DefaultFilePath & "\"
        .Title = "Please select a folder to list Files from"
        .InitialFileName = InitialFoldr$
        .Show
        If .SelectedItems.Count <> 0 Then
            xDirect$ = .SelectedItems(1) & "\"
            xFname$ = Dir(xDirect$, 7)
            Do While xFname$ <> ""
                oSht.Range("A" & xRow) = xDirect$
                oSht.Range("B" & xRow) = xFname$
               Err.clear
                On error resume next
                OSht.range("C" & xRow) = vba.replace (xfname, "_2010", "_2011")
                If err.number >0 then  oSht.Range("C" & xRow) = xFname$
                Err.clear
                xRow = xRow + 1
                xFname$ = Dir
            Loop
        End If
    End With
    ThisWorkbook.Save
End Sub


Sub ChangeFileNames()
Dim oSht As Worksheet
Set oSht = ThisWorkbook.ActiveSheet
Dim i, r As Long
Dim strPATH As String
Dim strOLD As String
Dim strNEW As String
Dim strCMD As String


r = oSht.Cells.SpecialCells(xlCellTypeLastCell).Row
If r < 2 Then Exit Sub
On Error Resume Next
With oSht

    For i = 2 To r
        strPATH = .Range("A" & i)
        strOLD = strPATH & .Range("B" & i)
        strNEW = .Range("C" & i)
        strCMD = "rename " & Chr(34) & strOLD & Chr(34) & " " & Chr(34) & strNEW & Chr(34)
        VBA.Shell "C:\Windows\System32\cmd.exe /c " & strCMD, vbHide
        
    Next
End With

End Sub