File Renaming

Ed in Aus

Well-known Member
Joined
Jul 24, 2007
Messages
829
I am trying to macro a painful process of renaming around 30 files from something like filename.csv to filename pay 21 07/08.csv help please this is painfully slow to do manually
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
I was going to add another section in the middle for each pay.. pays are fortnightly based on the financial year, so we are currently on pay 21, financial year won't need to be updated regularly
 
Upvote 0
You can either use some VBA code to do the rename or if it is a one off, what I usually do is go to a dos prompt and type
Code:
dir /b *.csv >go.bat

Once that is done open the resulting bat file in notepad.

Copy everything and dump it in excel, use simple text string manipulation formulas to write a rename command per line.

Paste the results back into the file in notepad, close and save then double click.

Its quick and it works.

If you are doing it through VBA then you will need to use the
Code:
Name "original file" as "new file"
command.

Here is some example code I have done recently to scan the contents of a DIR into an array (I was doing WAV files, you can change it to suit your needs)
Code:
WAVCounter = 1
WAVFile = Dir$(DirName & "\*.WAV")
Do While WAVFile <> ""
    ReDim Preserve WAVListArray(WAVCounter)
    WAVListArray(WAVCounter) = WAVFile
    WAVFile = Dir$
    WAVCounter = WAVCounter + 1
Loop

I would then have something like:
Code:
    For X = 1 To UBound(WAVListArray)
        Name WavListArray as NewFileName
    Next

Hope that helps.

Dan
 
Upvote 0
Hi Dan a little bit new to bat files can you show me an example

If you are new to them I would reccomend you go with the VBA code instead. A bat file is a sequential list of dos commands for the system to process one after the other.

The construct of batch file would be something like:
Code:
rename "file1.csv" "newfilename.csv"
rename "file2.csv" "another new name.csv"
rename "third file.csv" "yet another new name.csv"

So if we have the current names in column A
example:
Code:
adde_20061123_014500.csv
adde_20061130_014500.csv
adde_20061207_014501.csv
adde_20061214_014501.csv
adde_20061221_014500.csv
adde_20061228_014501.csv
And we have the new names in Column B
example:
Code:
MyNewFileName.csv
SomethingElse.csv
blah blah blah.csv
MyNewFileName2.csv
MyNewFileName3.csv
blah blah blah2.csv
The formula we enter to build our dos rename call is:
Code:
="rename """ & A1 & """ """ & B1 & """"

This gives us a result of
Code:
rename "adde_20061123_014500.csv" "MyNewFileName.csv"
rename "adde_20061130_014500.csv" "SomethingElse.csv"
rename "adde_20061207_014501.csv" "blah blah blah.csv"
rename "adde_20061214_014501.csv" "MyNewFileName2.csv"
rename "adde_20061221_014500.csv" "MyNewFileName3.csv"
rename "adde_20061228_014501.csv" "blah blah blah2.csv"
All ready to paste into a .BAT file and double click.

But like I said, if you have not played around with these before it is probably best to stick with the VBA code.
 
Upvote 0
Hi Dan,

I am trying to have a list of PDF files renamed, there are thousands and renaming them manually is a pain.

So i tried to use the concatenate command but it still did not work.

Is there a way in Excel that we can do this if i have a list of my old file names and the new ones i want to rename it to ?

Could you please give me clear and simple instructions.

Thanks
Suzie
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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