renaming files with macro

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

Originally Posted by jolivanes

JudoGTI
Sorry, I don't quite follow you. If you click on the "Get Files" button, you have to select the files you want to rename. You can select consecutive multiple files by holding down the Shift key or select non consecutive multiple files by holding down the Ctrl key while selecting with your mouse. I have another file somewhere that brings up the picture you choose so you can rename it. Is that what you're after?
Let me know and I'll hunt it down in my excel files I have.

HTH
John

John -

My mistake, I forgot the original purpose of the macros. I guess Im sort of 'customizing (not really but I'll explain)' its purpose.

First challenge) I'm getting a list of id numbers from a database. Technically speaking I should have 1 picture for each ID. But because the people responsible for retaining the pictures dont do a very good job, when someone is terminated or gets a new photo they didn't delete the old picture. This makes it so I cannot just select the entire directory of photos because some dont have matching new names in the database (I have ~3000 pics for ~1750 database records). But using the CTRL-Click will take forever (over 1000 photos)

Second Challenge) The people responsible for entering information to the DB are lazy and have no thumbs (Im guessing) because there are a lot of typo's. So I might have a photo for 1234.jpg but the ID in the database might say 123t.jpg When click the 'Change Name in Folder' button I get a message saying File Not Found (that might not be the exact verbiage but you get the point).

Because Im dealing with 1000's of files and files name (coming from separate sources, directory of pics and DB with ids to rename them) it makes it tough to work around. I was hoping to rename the ones I could (probably 90%) and then have a clear listing of the ones that need personal attention. I hope I explained that well enough. I know Im using the spreadsheet for a purpose that it was not defined for so this 'change/mod' might not be doable, but it would help me out if its an easy enough change.

Maybe add a check in the For-loop in the 'Change Name in Folder' macro to fetch the value from the cell, verify file exists, if exists, change name, if NOT exists, skip to next grid value?

Thanks for the help, and for the original spreadsheet.