modify all the file names in a directory

seeblue

Board Regular
Joined
Jan 23, 2005
Messages
178
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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

bk

Active Member
Joined
Jun 2, 2002
Messages
387
Here's a start at least

Use this code to get you into the folder:
Code:
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:
Code:
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

Board Regular
Joined
Aug 22, 2005
Messages
101
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".

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,016
Messages
5,569,628
Members
412,284
Latest member
Daibear
Top