Removing Spaces from File Names

I inherited a folder with a bunch of files in it. Almost every file had spaces in the name. I abhor spaces in file names. Also, I need to put these files in Subversion, so the names would have all be encoded so they’d work as URLs. That is, all of the spaces would become %20 or some such thing. After manually fixing the first one, I opted for a script.

First, I set a reference to “Microsoft Scripting Runtime” (VBE – Tools – References) so I could access the FileSystemObject.

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

I had to test to make sure there was a space in the file name. If there wasn’t, it complained that the file already exists. So it must copy, rename, then delete.

From this

to this

Posted in Uncategorized

33 thoughts on “Removing Spaces from File Names

  1. And here’s a version for those who have upgraded to Excel 2007 and discovered that FileSystemObject is no longer supported:

    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
  2. Here is a different way to do the same thing…

    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
  3. Dick:
    ========
    I now see that John Walkenbach beat me to the “Dir” method of doing this by a few minutes. Just to comment on our code, though… it looks like your Comment Processor removed our “not equal to” sign (a less than symbol followed by a greater than symbol) that was in the Do While statement. It also looks like our ampersands (for concatenation) were changed to this text string… “&” for some reason.

    John:
    ========
    You don’t have to test the filename to see if it contains a space or not… our method does not seem to complain if the filename has no spaces in it.

  4. John… regarding your comment that the FileSystemObject is no longer supported… it looks like if you add a Reference to the Microsoft Scripting Runtime, then the code Dick posted will run as expected in XL2007.

  5. I just noticed that in my comment about the mangled code, my attempt to include the text string that the Comment Processor replaced my ampersands with got displayed as a real ampersand. I’m just playing a hunch here to see if that is the way to include the symbols that got changed. Hopefully, this version of my previously posted code will display correctly…

    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
  6. Okay, it didn’t work. Dick… you can remove my last posting and this one if you want.

  7. Whilst I agree that the Dir method is a more appropriate approach John, I don’t think that FilesystemObject is not supported in Excel 2007. You must be thinking of FileSearch.

  8. John, Rick –

    Obligatory angle bracket comment: Inside the VB tags, WordPress treats angle bracket pairs as HTML delimiters and throws away/ignores anything you there. In the case of “not equals,” just the pair of brackets goes. In other cases, with a less-than sign up there, and a greater-than sign down here, your code is dramatically shortened.:roll:

    It mungs ampersands and an unpaired angle bracket, too.

    …mrt

  9. Michael… I am not familiar with HTML (I’ve never designed a webpage)… is there a way to “protect” the angled brackets and ampersand from the HTML processor so that they display as intended?

  10. Using the library reference when declaring variables is helpful to me…

    Sub RemoveSpaces()
    Dim fso As Scripting.FileSystemObject
    Dim fsoFile As Scripting.File

    Set fso = New Scripting.FileSystemObject

  11. Not sure what happens. Here’s where I use NEQ, GEQ, and LEQ and concatenate.

    Option Explicit

    Sub useArithCompare()
        Dim X, Y, Z
        If X <> Y Then
            If Y > Z Then
                If Z < 0 Then
                ElseIf Z >= 0 Then
                    End If
            ElseIf Y <= Z Then
                Y = X & Z
                End If
            End If
        End Sub

  12. Hi Rick –

    There should be, but alas, no. Escape characters are not honored inside VB tags.

    Workarounds are two:
    Don’t use VB tags. OK for snippets, but kind of defeats the purpose. Regular replies don’t misbehave like this.

    or

    Use things like LT or LTE or GT or GTE or !=. The eye will figure it out, and the VBE will catch it otherwise. Not ideal, but at least the code doesn’t get turned into the Reader’s Digest condensed version ;-)

    I let the ampersands ride. Dick has inquired of WordPress about the problem.

    …mrt

  13. Michael, I didn’t do anything other than copy+paste from the VBE and enclose the code in square-brackets-around-vb and square-brackets-around-slash-vb tags. While I didn’t do it this time, I used to separate NEQ into < space > and LEQ into < space =. Those are the two that might be misconstrued as erroneous HTML tags. GEQ cannot be. So, nothing special should be required.

  14. Tushar… are you sure you didn’t do something different than you described? When I posted my code, I had copy/pasted it from the VB editor, surrounded it with the VB tags and the Comment Processor screwed it all up.

  15. @Tushar –

    If you just pasted from the VBE, then how did you get the VBE to forgo putting spaces between the Y and the right angle bracket in the first IF, the left angle bracket and 0 in the second IF and the Z and the right angle bracket in the first ElseIf? For me on my system the VBE always put spaces around all operators.

  16. This is a test.

    not in a VB block: LT < LE <= NE <> GE >= GT > AMP &

    in a VB block:

    LT &lt; LE &lt;= NE &lt;&gt; GE &gt;= GT &gt; AMP &amp;
  17. Another test, pasting from a unicode editor.

    outside VB block:
    LT <
    LE <=
    NE <>
    GE >=
    GT >
    AMP &

    inside VB block:

    LT &lt;
    LE &lt;=
    NE &lt;&gt;
    GE &gt;=
    GT &gt;
    AMP &amp;
  18. Back on-topic: another fine place to use a batch file, though the batch file would moderately complex.

    @echo off
    @setlocal enableextensions
    @REM default to current directory
    if “%~1? == “” %0 .
    for /f “delims=” %%f in (‘dir /s/b/a-d “pathname-here* *”‘) do call :PROC “%%f”
    goto :EOF
    :PROC
    set f=%~1
    ren “%~1? “%f: =_%”
    :EOF

    Simpler with a real shell and POSIX find and mv commands.

    find “pathname-here” -type f -name “* *” | while read f; do mv “$f” “${f// /_}”; done

    Or as a 1-line script

    find “${1-.}” -type f -name “* *” | while read f; do echo mv “$f” “${f// /_}”; done

  19. RE:Subversion
    Dick,
    I am considering Subversion for VBA code management anong with xls file management. Have you used it for this? How well does it work?

    AlexJ

  20. Re testing whether the filename includes a space.

    I think I would test, so that I don’t make the file system rename a file to the same name. I don’t know what kind of burden that is on the CPU, maybe someone less lazy than I could try it out, but file operations typically cost more than If statements.

  21. Jon brings up an interesting point: renaming “foo bar” when the file “foo_bar” already exists.

    The problem would be needing to interrupt one directory search (for “* *”) with a second search to check if there are any files already named with the current file’s name with spaces replaced by whatever.

    It seems to me it’d be more efficient to
    – load the full directory listing into an array or worksheet range,
    – use a second column for filenames with spaces converted to underscores,
    – sort the table by RESULTING filename,
    – walk through the RESULTING filename field looking for matching adjacent values,
    and when found appending a [1] just before the extension
    – walk through the ORIGINAL filename field changing filenames when original
    filename differs from resulting filename.

    Optional to include another pass through the resulting filenames to spot things like [1][1] or [3][1] just before the extension and converting them to [2] and [4], respectively (for these examples).

    This is simple with the batch file or shell script approaches since full pathname parsing is built-in as opposed to VBA/VBScript where reinventing path/basename/extension parsing is necessary.

  22. As fzz said, Jon has brought up an interesting point; however, I don’t think the problem is as hard as fzz is imagining. I believe this code will handle the problem by affixing Copy(#), where # is a sequence number, to make sure each file name is unique…

    Sub RemoveSpaces()
      Dim Copies As Long, Dot As Long
      Dim Path As String, FileName As String, FN As String, Suffix As String
      Path = “C:Tester”  ‘Note the trailing backslash
     FileName = Dir(Path &amp; “*.*”)
      On Error Resume Next
      Do While Len(FileName)
        FN = FileName
        Suffix = “”
        Copies = 0
        Do
          Err.Clear
          Name Path &amp; FN As Path &amp; Replace(FileName, ” “, “_”)
          If Err.Number Then
            Copies = Copies + 1
            Suffix = ” Copy(“ &amp; Copies &amp; “)”
            Dot = InStrRev(FN, “.”)
            FileName = Left(FN, Dot – 1) &amp; Suffix &amp; Mid(FN, Dot)
          End If
        Loop While Err.Number
        FileName = Dir
      Loop
    End Sub
  23. Rick: I was also implicitly responding to Jon secondary point: but file operations typically cost more than If statements.

    Error trapping OS file operations may be simple (as in brute force), but it ain’t fast. If you want robust AND fast, it’s like I said: better to load into an array or range, process the array or range, then loop once through the array or range (with no subloops) to change filenames as/when necessary.

  24. I have not Alex. I’m just learning subversion and I like it so far. Based on what I know, I would create a subversion repository with my Excel file in it. Then I would use Rob Bovey’s code cleaner for every “build” and I would set it sot that it doesn’t delete the bas files. Then when the folder is committed back to the repository, you have the xls and all the text files, which hopefully would be diff’d. That’s just off the top of my head. I’m sure there would be a hundred problems.

  25. Thanks, Dick. You are proposing the same approach I am considering. I was even planning to use vba code from the old version of Code Cleaner as a model for routines to “harvest” and “replentish” the bas, and sheet and class modules to and from subversion (with full credit to Rob, of course!)

  26. Sub tst()
      c0 = “E:”
      c1 = Dir(c0 &amp; “* *.xls”)
      Do Until c1 = “”
        If InStr(vbCr &amp; c2, vbCr &amp; c1 &amp; vbCr) = 0 Then
            c2 = c2 &amp; c1 &amp; vbCr
        Else
           c3 = c3 &amp; c1 &amp; vbCr
        End If
        c1 = Dir
      Loop

      sq = Split(c2, vbCr)
      st = Split(Replace(c2, ” “, “_”), vbCr)
      For j = 0 To UBound(sq) – 1
        Name c0 &amp; sq(j) As c0 &amp; st(j)
      Next
      If Not IsEmpty(c3) Then MsgBox c3, , “There are a few problems left”
    End Sub


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *