PDA

View Full Version : Batch File Renamer



binar
09-21-2011, 03:19 PM
Fellow Forum Members,
I would be very grateful if any forum member out there can help me develop a batch filename changer. I'm seeking to have CELL A1 dedicated to defining the PATH where the text files to be renamed are located. Column B dedicated to listing the ORIGINAL FILENAMES to be changed and Column C lists the REPLACEMENT FILENAMES that overwrite the ORIGINAL FILENAMES.

Below is a script I'm seeking to recode. I want the sPath to be linked to whatever PATH is inputted in CELL A1. I'm seeking to avoid having to amend the VBA script itself everytime I need to change the PATH. Also, the script below is having problems changing filenames that have extensions included.


Sub ChangeNames()
Dim sPath As String, r As Range
sPath = "C:\test\" 'amend as appropriate
For Each r In Selection
FileCopy sPath & "\" & r.Value, sPath & "\" & r.Offset(0, 1).Value & ".gent"
Next r
End Sub



In short, it would be awesome if the script above could be made more robust and handle batch remaming 500 text filenames without crashing like the code above does. Any help will be greatly appreciated. Thanks.

Bob Phillips
09-22-2011, 12:42 AM
Sub ChangeNames()
Dim sPath As String
Dim Lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

sPath = .Range("A1").Value

Lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = Lastrow To 2 Step -1

Name sPath & Application.PathSeparator & .Cells(i, "B").Text _
As sPath & Application.PathSeparator & .Cells(i, "C").Text
Next i
End With

Application.ScreenUpdating = True
End Sub

binar
09-22-2011, 09:32 PM
Xid,
Thanks for your help. I tested it out and it works perfectly. Much more solid than the code I was trying to make work.

The only change I made to your code was changing the "A1" to "A2" in order to account for the header row I created. My header row reads for Cell A1 = PATH, Cell B1 = Old Filename, Cell B3 = New Filename. I enter the actual path in Cell A2.

The reason I'm explaining this is because I'm now seeking an additional piece of code to complete this project. For Column B (Old Filename) I think it would be helpful if I could push a command button that runs a VBA script that looks at the folder indicated in Cell A2 and then auto populates Column B starting at cell B2 (skips header row Cell B1) all the filenames (with extension included) located inside the folder indicated in Cell A2.

In short, all the filenames of all the files located inside the indicated folder in cell A2 is converted to a TEXT list that Excel places in Column B (old filenames). Such a VBA script will make it easier to populate Column B and it will totally complete this project.

Any help will be greatly appreciated. Thanks.







Sub ChangeNames()
Dim sPath As String
Dim Lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

sPath = .Range("A1").Value

Lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = Lastrow To 2 Step -1

Name sPath & Application.PathSeparator & .Cells(i, "B").Text _
As sPath & Application.PathSeparator & .Cells(i, "C").Text
Next i
End With

Application.ScreenUpdating = True
End Sub

Bob Phillips
09-23-2011, 01:48 AM
But how will you populate the replacement names list?

binar
09-23-2011, 09:34 AM
But how will you populate the replacement names list?



Here is a rundown of the workflow process I'm thinking about using:
First, press a command button to execute a script that fetches the filename data located in the folder defined in Cell A2 and then pastes it in Column B (skipping Header Cell B1).

Second, the Original filenames follow this naming rule:
"DATE"_"Part Number".txt
So they looks like this:

1-21-11_450020
1-22-11_450020
1-23-11_450020
1-24-11_450020
1-25-11_450020
1-26-11_450020

So to answer your question: To get the new Column C filenames all I need to do is copy over from Column B the Old Filenames to Column C. Then using Excel's find and Replace tool I change all of the part numbers so that the New Replacement data looks like this:

1-21-11_460000
1-22-11_460000
1-23-11_460000
1-24-11_460000
1-25-11_460000
1-26-11_460000

Third, I press on the command that executes your script that you posted above and the files in the folder are renamed correctly. In short, the only code I currently need is the one that fetches the filenames and positions them in Column B (skipping header Cell B1).

Any help will be greatly appreciated. Thanks.

shrivallabha
09-24-2011, 07:36 AM
3-4 months back, I was chasing this kind of thing. You might have to modify it as per your requirements.