Rename xls files through script

seagreen

Board Regular
Joined
Feb 7, 2009
Messages
71
Hello,
I would like to be able to rename hundreds of files within a directory with the following rules:
1) remove characters before the first - (hyphen) in the filename
2) remove the last 21 characters before the .xls at the end of the filename (removing more than 21 chars at end ok if needed to reduce final name length (w/o extension counted) to <= 31
3) the filenames don't have the same number of chars in the name
4) Some filenames would still have > 31 chars even after performing the truncations specified in 1) and 2) above. I
need to make sure each final filename <= 31 chars because I'm using Excel 2007 and I will be using these names as
sheet names.

I am open to whatever script language is best: windows batch file, VBA, powershell, etc.

Thank you for reading and any help you provide.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
you could use a code that lists all the names of the files in a directory (including sub directories if required), then have them populate into a sheet.
You could Copy the file names into column B, and massage the data accordingly.
Then reun a macro to find a file in the directory again, and change the file.name value to the cell value to the right.

To remove the text before the first hyphen, you could use the search function to location the position of the text, and use the left/right/len functions to massage the data.
Same applies for the <31 length.
Hope that helps.
 
Upvote 0
Thanks dermie_72. Do you have a syntax example for 1)searching for the first hypen and 2)how to make sure it's not > 31 chars by chopping off whatever's necessary before the dot separating the xls extension from the name?
Thanks again.
 
Upvote 0
Its ok to completely cut off the end of the file name no matter how long it is, just CONCATENATE the file extension back on the "new file name" & ".xls" Is there a space before or after the hyphen, this may make a difference with the -1 and +1 below. If space before hyphen make -2, if space after hyphen make +2.

Put file name in cell A1.
Put fx in cell A2: =LEFT(A1,FIND(" - ",A1)-1)
Put fx in cell A3: =MID(A1,FIND(" - ",A1)+1,999)

What is needed after the - can get the MID fx to count 31 characters.

Put fx in cell A4: =MID(A3,1,31)
Put fx in cell A5: =CONCATENATE(A4,".xls")

copy cell A5, paste special "values" into new cell, this will be the new name to replace (rename) back into your file system.

I am sure someone can put all of this into one nested formula and make it nice and clean.
 
Upvote 0
Thanks very much JackHoff. Very cool. I will post again with my complete solution when I figure out the part about bringing in the file names and then writing them back out to the directory when I've massaged the names with you formulas. It may be tonight (EST) before I post an update though.
 
Upvote 0
Its funny, I need to do the same thing down the road in a few months, I need to bring the file name in and only get the last string, its the serial number, there is a space just before the SN so it will be easy to =RIGHT it out to a work sheet but I dont need to change the name or cut any of it out, but I will need what you are working on right now, glad I found your post.
 
Upvote 0
How about this? IMPORTANT : please test on copy of files 'just in case'! Consider backing up real files too, before running the code.

Code:
Sub test()

Const strPATH As String = "F:\temp\"


Dim strFileName As String
Dim strNewName As String


strFileName = Dir(strPATH & "*.xls")


Do While Len(strFileName)
  Name strPATH & strFileName As strPATH & NameNew(strFileName)
  strFileName = Dir
Loop


End Sub


Private Function NameNew(ByVal NameBefore As String) As String


  Dim PositionInStr As Long
  
  NameNew = NameBefore
  
  'take out all characters before the first "-"
  PositionInStr = InStr(1, NameNew, "-", vbTextCompare)
  If PositionInStr > 0 Then NameNew = Mid$(NameNew, PositionInStr, 255)


  'take out 21 characters before ".xls"
  PositionInStr = InStr(1, NameNew, ".xls", vbTextCompare)
  If PositionInStr > 22 Then NameNew = Left$(NameNew, PositionInStr - 21 - 1) & Mid$(NameNew, PositionInStr, 255)


  'maximum 31 characters before ".xls"
  PositionInStr = InStr(1, NameNew, ".xls", vbTextCompare)
  If PositionInStr > 32 Then NameNew = Left$(NameNew, 31) & Mid$(NameNew, PositionInStr, 255)


End Function
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

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
Back
Top