Preston55
asked on
How to do a find replace on file names within a directory with a script.
I have a an excel sheet that will have first/last name and an account number.
I have a directory of files that are named via acct number, I would like to script a way to rename all those account numbers to first/last name. For example find replace "account number" with the name listed by the account number.
Does anybody know of a good way to do that, or have a working script
I have a directory of files that are named via acct number, I would like to script a way to rename all those account numbers to first/last name. For example find replace "account number" with the name listed by the account number.
Does anybody know of a good way to do that, or have a working script
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
OK, if that is the case you will need to use this code:
This will replace the account number in any filename that begins with the account number.
Cheers
Sub renameFiles()
myPath = "C:\New Folder\" 'the path to your directory (remember backslash at end)
i = 2 'start on second row, assuming you have headers
Do Until Cells(i, 1).Value = "" 'loop down the excel sheet until the first empty cell
strFile = Dir(myPath & Cells(i, 1).Value & "*.xls*", vbNormal)
Do While strFile <> ""
Name strFile As Replace(strFile, Cells(i, 1).Value, Cells(i, 2).Value & Cells(i, 3).Value)
strFile = Dir
Loop
i = i + 1
Loop
End Sub
This will replace the account number in any filename that begins with the account number.
Cheers
Is this Excel--as in .xls/.xslx--or .csv? Also, do you have a preference on which scripting language? I see you put several.
Also, as someone who has a predilection for using Excel to manipulate stuff in a pinch, I am wondering why not try this low-rent solution: assuming your have columns A (first name), B (last name) and C (account number), why not use something this is in column D:
The output would be:
A: Will
B: Steele
C: 12345
D: ren C:\your\folder\path\12345. ext C:\your\folder\path\WillSt eele.ext
Drag this down to your last row and then copy/paste Column D values into a .bat file. Double-click the .bat file and renaming is complete. I would also STRONGLY recommend testing and backing up your directory before running this.
Also, as someone who has a predilection for using Excel to manipulate stuff in a pinch, I am wondering why not try this low-rent solution: assuming your have columns A (first name), B (last name) and C (account number), why not use something this is in column D:
="ren C:\your\folder\path\"&C1&".ext C:\your\folder\path\"&A1&B1&".ext"
The output would be:
A: Will
B: Steele
C: 12345
D: ren C:\your\folder\path\12345.
Drag this down to your last row and then copy/paste Column D values into a .bat file. Double-click the .bat file and renaming is complete. I would also STRONGLY recommend testing and backing up your directory before running this.
Sorry, posted too soon. The command in traditional cmd shell would be:
="ren C:\your\folder\path\"&C1&".ext "&A1&B1&".ext"
Do not use the full path to prefix the second file name.
ASKER
john smith is account number 34576,
so he may have two files which would be 34576_abcdef.tif and 34576_mnop.tif
With using those scripts can it preserver the _abcdef, yet change the 34576 to John Smith
Hope that makes sense, Thanks Again.