Changing File Names in Excel Folder

1. Open the file attached.
2. Run the macro GetFileNames and select the folder containing the files
Path will be imported in column A
File Names will be imported in column B
3. Write the new file name in column C
4. Run the macro ChangeFileNames

This is the Macro that will get the file names & path into excel

Please Login or Register  to view this content.

And this one will rename them

Please Login or Register  to view this content.

Please mark the thread as SOLVED if it is.

You can do this easily with a batch file as follows:

From the Command Prompt (available under accessories) run the following from the directory containing the files:

ren Branch????_2010.* Branch????_2011.*

You change directories with the command cd DIRNAME where DIRNAME is the directory name. This can get a bit weird if you are not used to batch commands, so alternately, you can place a text file in the directory which contains the above, rename it to RENAME.BAT, and then double-click it to execute the command.

---GJCase

snb: Thanks for your help. This worked on the first four files and the last one, and then I got an error message: "Run-time error '58': File already exists. When I click on debug it highlights: fl.Name = Replace(fl.Name, "_2010", "_2011"). Is there something I need to correct? JPDutch

Tigeravatar: thanks for your help. I tried this out, but nothing happened. Any suggestions? JPDutch

Jpdutch did you try the macro I have posted?

Kelshaer:

Thank you; I did try it, and it works, but is there a way to avoid retyping? I have hundreds of these files and I'm hoping to save time and effort. Thanks again! JPDutch

Here you Go.
Run the first macro and it will pull the names as the previous one and will automatically type the new name in column C.

Then Run the other Macro to rename them.
Please Mark the thread as solved if it was.

In the GetFileNames() I'm getting a compile error: variable not defined. "i" below is the variable it highlights.

oSht.Range("C" & xRow) = VBA.Replace(.Range("B" & i), "_2010", "_2011")

Two questions. I've had to change the location from our H:drive to our I:drive. Do I need to make any corrections in the information you gave me? Next, I'm still using the older version of Excel: xls, not xlsm. Will that make a difference?

Thanks in advance for your help!

Thanks snb. I'm leaving work and running home...takes about an hour. I'll try this once I get setup tonight.

Open the code and go under the second line where it says Sub GetFileNames() and type the following line:

Dim i as integer

Sorry buddy for being late. You will love this one. It will ask for the folder so you can select any location. It will automatically list the new file names in column C.

Please Login or Register  to view this content.