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.

 

[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

Can you work out a way to ensure that full stops as well as replaced by dashes?

Annoyingly this is the requirement of this organisation that we are submitting files to. I tried this by modifying your script to 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(Replace(fileName, " ", "-"), "_", "-"), ".", "-") Then
                    Name folderName & Application.PathSeparator & fileName As folderName & Application.PathSeparator & Replace(Replace(Replace(fileName, " ", "-"), "_", "-"), ".", "-")
                End If
                fileName = Dir()
            Loop
        End If
    End With
End Sub

but it seems it also replaces the "."s in the pdf extension (the files are all PDFs) with dashes, so that the PDF icon does not appear in the file list and when you try to open them, you get a dialog box asking you which programme you need to open them.

Much appreciated if you could fix this!

 

[jkpieterse]

Try this:

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

 

[SimonR]

As it turns out I had already come up with a solution, by adding yet another replace to replace the "-pdf" with ".pdf", but thanks - that's the beauty of Excel (and other Microsoft applications for that matter), many times you can achieve the same result with different methods.