modify all the file names in a directory

FROGGER24

Well-known Member
Joined
May 22, 2004
Messages
701
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,114
Messages
5,835,462
Members
430,358
Latest member
zzc1128

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top