Changing names of some files in a directory

This started off by helping myself to Dick’s code in ‘Removing Spaces from File Names’ ( but quickly evolved to meet my own needs.

First, was the requirement to replace a certain text string by another. I added 3 parameters to the subroutine (Dirname, ReplaceWhat, and ReplaceBy). Also evident was that some files had leading spaces as well as multiple consecutive embedded blanks. I added an optional Boolean doTrim.

By using Dir rather than FileSystemObject I could restrict the returned file names to those that matched the search criteria. Consequently, it was certain that the file name would change.

Then, I found some files had characters just before the text to be replaced that were “special characters.” I added an optional boolean useRegExp together with the code to use a regular expression to do the cleaning.

So, a filename like ‘This is a file, change me.xls’ should become ‘This is a file changed you.xls’

Unlike the above code, Dir could not be used to restrict the filenames since it does not support regular expressions. Consequently, I included a test to ensure that the new name differed from the old name before using the Name statement to rename the file.

The code below has been lightly tested as in it worked for the few directories that I had to process, each with a different set of rules.

Invoke the above subroutine as

Tushar Mehta

Posted in Uncategorized

4 thoughts on “Changing names of some files in a directory

  1. I’d never considered using #Const to switch between Early and Late binding. Great idea!

  2. instead of your function addpathseparator

    With Application
            DirName = Replace(DirName & .PathSeparator, String(2, .PathSeparator), .PathSeparator)
        End With
  3. The advantages of using a single bushy procedure vs a wrapper udf calling one of two simpler, specific procedures (one using regexps the other not) is unclear to me.

    But then using VBA for this sort of thing rather than shell scripts, even batch files, seems pointless. While I could use batch files to generate Fibonacci numbers, I wouldn’t. While I could use VBA to rename files, I wouldn’t. Extolling the virtues of driving screws with hammers.

  4. Rob: I spent several formative years programming in languages with a very powerful compile-time language – extending well beyond the simple #If capabilities supported by VB. So, one could say the use of compiler directives is second nature to me.

    In addition to using it for early/late binding, I also use compiler directives to “remove” code without actually removing it and testing new code while leaving the old code alone.

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

Leave a Reply

Your email address will not be published.