Macro for renaming files en masse

[SimonR]

Hello,

Can anyone advise of a macro that renames all files in a folder containing spaces and underscores.

The criteria is quite simple - all spaces and underscores should be replaced by dashes (-). I have to do this for many files and doing it all manually is a sure recipe for RSI (not to mention error prone).

Thanks.

 

[jkpieterse]

Perhaps like this?

Sub RenameFilesInFolder()
    Dim FD As FileDialog
    Dim folderName As String
    Dim fileName As String
    Set FD = Application.FileDialog(msoFileDialogFolderPicker)
    With FD
        If .Show Then
            folderName = .SelectedItems(1)
            fileName = Dir(folderName & Application.PathSeparator & "*.*")
            Do Until Len(fileName) = 0
                If fileName <> Replace(Replace(fileName, " ", "-"), "_", "-") Then
                    Name folderName & Application.PathSeparator & fileName As folderName & Application.PathSeparator & Replace(Replace(fileName, " ", "-"), "_", "-")
                End If
                fileName = Dir()
            Loop
        End If
    End With
End Sub

 

[myall_blues]

You could do this from a windows command prompt rather than an excel macro.
Start->Search->search for 'cmd" (without the quotes) and click on command prompt when it comes up.
cd to the folder you want to change the filenames in.
Paste this line at the command prompt and press enter.
cmd /e:on /v:on /c "for %f in ("* *.*") do (set "n=%~nxf" &; set "n=!n: =-!" &; ren "%~ff" "!n!" )"

Important notes:

• Recommend copying a couple of files into a test directory and testing it there before unleashing it on your entire folder.
• The list of files is generated by for %f in ("* *.*"). There is a space between the first two stars to generate the list of files with spaces in the name. On the second pass (see next point) change the space to an underscore.
• The bit doing the work is set "n=!n: =-!". It changes the thing after the colon (space in this instance) to the thing after the equals (dash in this case). That means you'll need to run it twice - once for space to dash, and once for underscore to dash.

 

[SimonR]

Perhaps like this?

Sub RenameFilesInFolder()
    Dim FD As FileDialog
    Dim folderName As String
    Dim fileName As String
    Set FD = Application.FileDialog(msoFileDialogFolderPicker)
    With FD
        If .Show Then
            folderName = .SelectedItems(1)
            fileName = Dir(folderName & Application.PathSeparator & "*.*")
            Do Until Len(fileName) = 0
                If fileName <> Replace(Replace(fileName, " ", "-"), "_", "-") Then
                    Name folderName & Application.PathSeparator & fileName As folderName & Application.PathSeparator & Replace(Replace(fileName, " ", "-"), "_", "-")
                End If
                fileName = Dir()
            Loop
        End If
    End With
End Sub

Looks good - it looks like you don't have to specify the name of the destination folder - does .SelectedItems(1) mean the code will apply to the last folder you accessed (as long as it's open)?

You could do this from a windows command prompt rather than an excel macro.
Start->Search->search for 'cmd" (without the quotes) and click on command prompt when it comes up.
cd to the folder you want to change the filenames in.
Paste this line at the command prompt and press enter.
cmd /e:on /v:on /c "for %f in ("* *.*") do (set "n=%~nxf" &; set "n=!n: =-!" &; ren "%~ff" "!n!" )"

Important notes:

• Recommend copying a couple of files into a test directory and testing it there before unleashing it on your entire folder.
• The list of files is generated by for %f in ("* *.*"). There is a space between the first two stars to generate the list of files with spaces in the name. On the second pass (see next point) change the space to an underscore.
• The bit doing the work is set "n=!n: =-!". It changes the thing after the colon (space in this instance) to the thing after the equals (dash in this case). That means you'll need to run it twice - once for space to dash, and once for underscore to dash.

I'm more comfortable with using the VBA code, as I'm more familiar with VBA than the command prompt, but I'll try this in future if/when I need to do something similar - thanks.

 

[SimonR]

Looks good - it looks like you don't have to specify the name of the destination folder - does .SelectedItems(1) mean the code will apply to the last folder you accessed (as long as it's open)?

Actually, I'm guessing it applies to selected/highlighted files - which seems a safer way of using the code than basing it on a whole folder?

 

[jkpieterse]

It does all files in the folder you select.