BUY EXCEL BOOKS ONLINE: 1. VBA & Macros      2. VBA for Modelers      3. Excel 2013 VBA and Macros     
4. Excel VBA for Dummies      5. Excel with VBA & .NET      6. Mastering VBA      7. Excel 2013 Programming

Replace Characters in the Filenames of a Directory

Friends, this is such a simple problem and hence the solution that I don't need to provide long texts explaining that what it does. Simply, see the figure below and you'll understand quite well that what would the macro below will do or how you can enhance it your way. In a single line, I would still say IT REPLACES CHARACTERS/STRINGS in all filenames present in the provided directory. Just a time saver and good to understand usage of DIR function.

Image:Replace Characters of Filenames ina directory

Function Replace_Filename_Character(ByVal Path As String, _
            ByVal OldChr As String, ByVal NewChr As String)

    Dim FileName As String
    'Input Validation
    'Trailing backslash (\) is a must
    If Right(Path, 1) <> "\" Then Path = Path & "\"
    'Directory must exist and should not be empty.
    If Len(Dir(Path)) = 0 Then
        Replace_Filename_Character = "No files found."
        Exit Function
    'Old character and New character must not be empty or null strings.
    ElseIf Trim(OldChr) = "" And OldChr <> " " Then
        Replace_Filename_Character = "Invalid Old Character."
        Exit Function
    ElseIf Trim(NewChr) = "" And NewChr <> " " Then
        Replace_Filename_Character = "Invalid New Character."
        Exit Function
    End If
    FileName = Dir(Path & "*.*") 'Use *.xl* for Excel and *.doc for Word files
    Do While FileName <> ""    
        Name Path & FileName As Path & Replace(FileName, OldChr, NewChr)
        FileName = Dir
    Replace_Filename_Character = "Ok"
End Function

'Subroutine to explain the above function's usage as per the above figure.

Sub Function_Sample_Usage()
    Dim lResult As String
    lResult = Replace_Filename_Character("D:\test", " ", "_")
    Debug.Print lResult 'Returns Ok
    lResult = Replace_Filename_Character("D:\test", " ", "_")
    Debug.Print lResult 'Returns Ok though no spaces in filenames now
    lResult = Replace_Filename_Character("D:\test\", "", "_")
    Debug.Print lResult 'Returns 'Invalid Old Character'
    lResult = Replace_Filename_Character("D:\test\Test01", " ", "_")
    Debug.Print lResult 'Returns 'No files found' as invalid directory provided.
End Sub