renaming files with macro

**kaffal** · 03-11-2009, 10:44 AM

I have several files in one folder. C:/data/
These files are named as date_xxxx.wk1. where xxx are random and the date format is YYYYMMDD.

Everyday I need to rename them as date_cellvalueA1.xls where the date format is DDMMYY. Cellvalue A1 is taken from each of the respective workbook.

As I need to rename them on a daily basis. Can I use 2 field, one to determine the directory path and one to determine what file name to search for.

If the files contain the YYYYMMDD, it will be rename to DDMMYY_cellvalueA1.xls

**ravishankar** · 03-13-2009, 09:34 AM

Hi
Save the file in the folder where you want to rename the files. Run the macro by clicking the button on sheet1. Test it on a sample 3-4 files in a folder before using it. Col A will list current files. col B will list files with new names.
Hope that helps
Ravi

**judoGTI** · 04-28-2009, 04:28 PM

Originally Posted by ravishankar

Hi
Save the file in the folder where you want to rename the files. Run the macro by clicking the button on sheet1. Test it on a sample 3-4 files in a folder before using it. Col A will list current files. col B will list files with new names.
Hope that helps
Ravi

Hello Ravi -

I am interested in the macro you created. I have a similar task, where I need to name name images files. Where Column A will have the existing filename, and Column B will have the new file name.

I am looking at your macro and am wondering if I change the following lines if it will work (Sorry for my noob-ness I have never done anything with VBA before). I deleted two lines and changed the line in BOLD

So if I have a directory full of images:
1.jpg
2.jpg
3.jpg
4.jpg

and my excel as the following (note it may not contain everything in the dir):
| Column A | Column B |
1.jpg | aa1.jpg
3.jpg | cc3.jpg
4.jpg | dd4.jpg

Sub kaffal()
Dim z As Long, e As Long, g As Long
Dim f As String, b As String
Sheets("Sheet1").Select
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
Cells(2, 1).Select
f = Dir(Cells(1, 2) & "*.xls")
Do While Len(f) > 0
ActiveCell.Formula = f
ActiveCell.Offset(1, 0).Select
f = Dir()
Loop
z = Cells(Rows.Count, 1).End(xlUp).Row
For e = 2 To z
If Cells(e, 1) <> ActiveWorkbook.Name Then
b = Cells(1, 2)
Name Cells(1, 2) & Cells(e, 1) As b
Cells(e, 2) = b
End If
Next e
MsgBox "Renaming is complete."
End Sub

**ravishankar** · 04-28-2009, 11:09 PM

HI
Try these macro codes

Sub Judo()
Dim z As Long, e As Long
z = Cells(Rows.Count, 1).End(xlUp).Row
For e = 2 To z
Name Cells(e, 1) As Cells(e, 2)
Next e
MsgBox "Renaming is complete."
End Sub

Ravi

**jolivanes** · 04-28-2009, 11:42 PM

kaffar, Ravi, judoGTI

Here is another possibility I cobbled together with help from several people on these forums.
If you find anything needs to be changed, please let me know also.

HTH

John

**judoGTI** · 04-29-2009, 11:31 AM

Originally Posted by jolivanes

kaffar, Ravi, judoGTI

Here is another possibility I cobbled together with help from several people on these forums.
If you find anything needs to be changed, please let me know also.

HTH

John

Hey John -

This one works great for me, the only problem I have is if it can't find a file in the directory the macro stops. Is there a way I can make it continue to the next row and skip ones it doesn't find?

What Im trying to do, is match peoples security badge picture to their employee number at work, so when people search for their extension they can see the pic of the person. The only thing is sometimes the excel doc contains records of people who havent had their picture taken yet. (I hope that makes sense), so I'd like it to skip over any files it cannot find and continue to work through the spreadsheet.

Think its possible?