cellMatrix.net

Removing Spaces from File Names

For a number of reasons I prefer to never use spaces in file names. Recently Dick Kuslieka at the Daily Dose of Excel posted an interesting VBA technique for removing spaces from file names. John Walkenbach commented with a revision that will allow the same functionality using Excel 2007 and Rick Rothstein commented with another alternative. These snippets are great additions to my VBA toolbox.

Dick's Kuslieka's original post:

Sub RemoveSpaces()
    
    Dim fso As FileSystemObject
    Dim fsoFile As File
    
    Set fso = New FileSystemObject
    
    For Each fsoFile In fso.GetFolder("C:\Tester").Files
        If InStr(1, fsoFile.Name, " ")> 0 Then
            fsoFile.Name = Replace(fsoFile.Name, " ", "_")
        End If
    Next fsoFile
    
End Sub

John Walkenbach's version for Excel 2007:

Sub RemoveSpaces()
    Const Folder As String = "C:\Tester\"
    Dim FileName As String, NewName As String
    FileName = Dir(Folder)
    Do While FileName  ""
        If InStr(1, FileName, " ")> 0 Then
            NewName = Replace(FileName, " ", "_")
            Name Folder & FileName As Folder & NewName
        End If
        FileName = Dir
    Loop
End Sub

and Rick Rothstein commented with another version:

Sub RemoveSpaces()
  Dim Path As String, FileName As String
  Path = "C:\Tester\"  'Note the trailing backslash
  FileName = Dir(Path & "*.*")
  Do While FileName  ""
    Name Path & FileName As Path & Replace(FileName, " ", "_")
    FileName = Dir
  Loop
End Sub