1

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
bear_lamp
  • 11
  • 2
  • If _"Calaloo - Direct Brands 2010q4 `92661 ck 口 us 口 Stmt 1.45 PB 8.69 EB 10.14 -mm 56569.pdf"_ is the file name what should be output ? – QHarr Feb 02 '21 at 18:43
  • A better target for the duplicate might be: [Working with Unicode file names in VBA](https://stackoverflow.com/q/33685990/4996248). – John Coleman Feb 02 '21 at 18:44
  • @JohnColeman - I have added it. – BigBen Feb 02 '21 at 18:46
  • @JohnColeman Doesn't tell you how to put the `口` character in a string literal in the code, which is the problem here. – GSerg Feb 02 '21 at 18:47
  • @BigBen I don't think that was a right thing to do. – GSerg Feb 02 '21 at 18:47
  • @GSerg - got it. I've removed it. – BigBen Feb 02 '21 at 18:49
  • @BigBen Shouldn't have! :) The problem is both the inability to have the character in the editor, and the fact that `Dir` does not handle Unicode properly. So both links are required. – GSerg Feb 02 '21 at 18:51
  • @GSerg - yeah I just realized this too... OP is not typing character in the editor, I missed the "I've tried putting both 口 and ? in c2 on the worksheet." Yay for flip-flopping. – BigBen Feb 02 '21 at 18:52
  • @BigBen They do: *"VBA replaces 口 with a question mark..."*, which didn't work because of the IDE. Then they also tried putting it on a sheet, which is a good idea, but didn't work because of Dir. – GSerg Feb 02 '21 at 18:55
  • @GSerg I am still at a loss - I tried using ChrW(21475) and FSO but it still errors out. – bear_lamp Feb 02 '21 at 20:05
  • @bear_lamp Then please edit your question to show your current code. When I use `ChrW$(21475)` and FSO, it correctly identifies files with `口` in their name. – GSerg Feb 02 '21 at 22:00
  • @GSerg ok edited! It's probably an issue with the syntax, but I can't figure it out. – bear_lamp Feb 03 '21 at 00:53
  • @bear_lamp `Name As` also does not support Unicode. Assign `f.Name` to rename. – GSerg Feb 03 '21 at 06:58

0 Answers0