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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

dermie_72

Well-known Member
Joined
Sep 4, 2012
Messages
1,540
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.
 

seagreen

Board Regular
Joined
Feb 7, 2009
Messages
71
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.
 

JackHoff

New Member
Joined
Jan 7, 2014
Messages
44
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.
 

seagreen

Board Regular
Joined
Feb 7, 2009
Messages
71

ADVERTISEMENT

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.
 

JackHoff

New Member
Joined
Jan 7, 2014
Messages
44
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.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,219
Members
417,131
Latest member
Seanr19871

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