How to replace unicode character in filenames using VBA

775 views Asked by At

I have hundreds of files in a folder with the character 口 in the filename. I would ultimately like to move the files using a vba macro. I have a second macro to replace characters in filenames, but it's not working to replace 口. VBA replaces 口 with a question mark, so the macro breaks when trying to rename it. How do I get around this?

example filename: "Calaloo - Direct Brands 2010q4 `92661 ck 口 us 口 Stmt 1.45 PB 8.69 EB 10.14 -mm 56569.pdf"

(updated to include unicode code and use FSO)

Rename macro code:

Sub rename_files_replace_character_FSO()
    Dim FSO As Scripting.FileSystemObject, fldr As Folder, f As File, ts As TextStream
    Dim sPath, sOld, sNew, sNewFile As String
    Dim wbMain As Workbook
    Dim wsMain As Worksheet

    Set wbMain = ThisWorkbook
    Set wsMain = wbMain.Worksheets("Rename Files")
    Set FSO = New Scripting.FileSystemObject
    sPath = wsMain.Range("B2").Value
        If Right(sPath, 1) <> "\" Then sPath = sPath + "\"
    sOld = wsMain.Range("C2").Value
    sNew = wsMain.Range("D2").Value
    
        Set fldr = FSO.GetFolder(sPath)
        For Each f In fldr.Files
            sNewFile = Replace(f, ChrW$(21475), sNew)
            Debug.Print f
            Name f As sNewFile
        Next
    Set f = Nothing
    Set fldr = Nothing
    Set FSO = Nothing

End Sub
0

There are 0 answers