Can I automate renaming an existing file from a list of names? 2

[Apollo6]

I have a directory with several hundred .jpg files. I also have a spreadsheet that contains the names of what I want to rename the .jpg files to.

Example:
Directory Contents
C:\Pictures\jsmith.jpg
C:\Pictures\jdoe.jpg
C:\Pictures\jwilliams.jpg

first initial & lastname & .jpg


Spreadsheet: Names.xls

A1 = johnsmith.jpg
A2 = johndoe.jpg
A3 = janewilliams.jpg

Lets say I start with A1, johnsmith.jpg from Names.xls. I want to be able to search through C:\Pictures\ and find jsmith.jpg and rename it to johnsmith.jpg.

I'm not sure this can be done but being that there are several hundred .jpg files that need to be renamed, it would be great to automate this as much as possible.

Any suggestions would greatly be appreciated.

 

[Apollo6]

Although it would take some extra keying, would it help if my spreadsheet, Names.xls, contained a column of cross reference names. For example:

A1 = johnsmith.jpg
C1 = jsmith.jpg

This would allow me the load whatever is in column C into a variable and find it in C:\Pictures\. Once I found the file, rename it to what is in column A...

Just brain storming...

 

[xlbo]

Absolutely - there's no way you can do it with what you've described 'cos you'd have to find the correct file and to do that you'd have to parse out the names in your cells to convert thee full 1st name to the initial and that ain't possible 'cos you havwe no delimeter to say where the 1st name ends
You either need a mapping system as Apollo6 suggested or a delimeter between the 1st name and last name. Once either of those is in place, I have some code that will do the job for you Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Apollo6]

Geoff-

After thinking about, if I went and keyed a cross reference column in my spreadsheet, I might as well just go through the directory and rename all the files manually.

Is there a way to build a text file or some type of export of the names of files in a directory?

Else, I don't think I'm going to be able to get around the task of manually renaming the files.

Thanks in advance for suggestions.

 

[RobBroekhuis]

As xlbo suggested, just putting a delimiter in the full name would make it possible to solve your issue, e.g.
john-smith.jpg instead of johnsmith.jpg

Barring that, I guess it COULD be done, somewhat clumsily, by trying all the different interpretations of johnsmith.jpg:

johnsmith.jpg
jhnsmith.jpg
jnsmith.jpg
jsmith.jpg
jmith.jpg
jith.jpg
jth.jpg
jh.jpg

In other words:

s=sheets("names&quot.range("A1&quot
s=left(s,len(s)-4) 'remove .jpg
HitFound=false
do while len(s)>1 and not HitFound
HitFound=FileExists(s+".jpg&quot
if HitFound then
...rename s+".jpg" to sheets("names&quot.range("A1&quot
end if
s=left(s,1)+mid(s,3)
loop

You'd have to code the FileExists and rename operations (if you need help with that, let us know).
Rob

 

[Apollo6]

OK, so I understand both correctly:

I need to add a delimiter to both the actuall file name and the new file name in my spreadsheet?

Example:
Current File Name: C:\Pictures\jsmith.jpg
New File Name: C:\Pictures\j-smith.jpg

Current spreadsheet Name: A1 = johnsmith.jpg
New spreadsheet Name: A1 = john-smith.jpg

If this is the case, it would be fairly easy to just add that to both names.

 

[RobBroekhuis]

You need delimiters just in your list of names on your sheet. That way, you can write a program to deduce the initial-only name, and go rename it. For example:

[A1]="john-smith.jpg"
s=[A1]
ShortName=left(s,1) & mid(s,instr(s,"-&quot+1)
...now rename file ShortName to s, or, if you prefer not to have the delimiter in the filename, you can eliminate that by:
LongName=left(s,instr(s,"-&quot-1) & mid(s,instr(s,"-&quot+1)
and renaming ShortName to LongName
Rob

 

[Apollo6]

Rob-

I would prefer not to have the "-" in the final long name. I am going to start putting in the delimiter in the spreadsheet names.

Thanks for the help.

 

[Apollo6]

I've come up with the following procedure to loop through the worksheet and directory. What I don't know how to do is actually rename the file... Although it may not be pretty, am I on the right track?


Sub Rename_Files()

Dim FSO
Dim FLDR
Dim FC
Dim FO
Dim shortname As String
Dim NewFile As String
Dim longname As String
Dim path As String

Set FSO = CreateObject("Scripting.FileSystemObject&quot
Set FLDR = FSO.GetFolder("D:\Databases\Human_Resources\Pictures\&quot
Set FC = FLDR.Files

ChDir "D:\Databases\Human_Resources\"
Workbooks.Open FileName:="D:\Databases\Human_Resources\Names.xls", _
UpdateLinks:=0
Range("A1&quot.Select
path = "D:\Databases\Human_Resources\Pictures\"

Do While ActiveCell.Value <> &quot;&quot;
NewFile = &quot;&quot;
shortname = &quot;&quot;
longname = &quot;&quot;
NewFile = ActiveCell.Value
shortname = Left(NewFile, 1) & Mid(NewFile, InStr(NewFile, &quot;-&quot + 1)
longname = Left(NewFile, InStr(NewFile, &quot;-&quot - 1) & Mid(NewFile, InStr(NewFile, &quot;-&quot + 1)
For Each FO In FC
If FO = path & shortname Then
????
End If
Next
ActiveCell.Offset(1, 0).Select
Loop

End Sub


Thanks again for the help.

 

[Apollo6]

In the For loop:

FO.Name = longname

Thanks for both of your suggestions and help.