modify all the file names in a directory

[seeblue]

i would like to go to c:\test and change the structure of the file name.
I need to remove _BST from the end of the file name.current format is like this XXXXX_10000_bst, also need to replace the underscore with just a space
then trim all excess spaces out of file name. The files all are in pdf format

thanks for reviewing this request

 

[bk]

Here's a start at least

Use this code to get you into the folder:

Sub ModifyMyFileNames
 With Application.FileSearch
     .NewSearch
     .LookIn = "C:\test\"
     If .Execute() > 0 Then
          For variable = 1 to .FoundFiles.Count
               'my actions
          Next i
     End If
 End With
End Sub

That gets you into the folder. In the place of " 'my actions ", put something like this:

origName = .FoundFiles(i)
firstFive = LEFT(origName,5)
nextSix = MID(origName,7,6)
newName = firstFive & " " & nextSix

Unfortunately, I'm unable to test tht syntax for you right now. I believe you can use those formulas straight in the code like I have stated above. However, if you can't just put the .FoundFiles(i) in a cell somewhere and the 2 functions in other cells and then, pull the info back out to concatenate into your new filename (in other words, you can use the variable as a "middle man" or a cell as a "middle man").

The code just above makes the assumption that the format you gave:

XXXXX_10000_bst

was correct in length regarding where the underscores, the "_bst" and everything were. If that assumption is incorrect, then what I gave you will have to modified. If the naming format isn't consistent, those steps may require a different methodology.

HTH. Maybe it's, at worst, a pretty good start.

 

[Ronald Moore]

It's not clear whether your files have the .pdf extension or not, but the following will work either way. By "trim all excess spaces" I assume you mean "replace two or more consecutive spaces with one space".

Sub RenameFiles()
  Dim OldName As String, NewName As String
  ChDir "c:\test"
  OldName = Dir$("*_bst*")
  Do Until OldName = ""
    NewName = Replace(Replace(OldName, "_bst", ""), "_", " ")
    Do Until InStr(NewName, "  ") = 0
      NewName = Replace(NewName, "  ", " ")
    Loop
    Name OldName As NewName
    OldName = Dir()
  Loop
End Sub