Rename Images from Folder to match an Excel worksheet

[Cruiser69]

Hi all.

I've searched but can't find quite what I'm looking for.

Is there a way to rename jpg images in a folder to match the numbers on a worksheet

The images in the folder will be named the same as the codes in Column B

I want to rename the codes to match the numbers in Column A

The problem is that some of the codes will be duplicated, as in numbers 4 & 6

The codes will change on a weekly basis.

I can copy the image folder to the desktop so I can keep the original filenames for the next week.

A	B
Lot	Code
1	AA12
2	BB23
3	DE22
4	RR23
5	BB12E3
6	RR23
7	FF22

Thanks for looking

Graham

 

[bobsan42]

try this code:

Sub copyFiles()
    Const sourceFolder = "...\Desktop\input\" 'Adjust as necessary
    Const fExt = ".jpg"  'Adjust as necessary
    Const destinationFolder = "...\Desktop\output\"  'Adjust as necessary
    Const namesRangeAddress = "B2:B8"  'Adjust as necessary
    
    Dim namesRng As Range, cc As Range
    Set namesRng = ActiveSheet.Range(namesRangeAddress)
    
    Dim fName As String, fPath As String
    Dim fNewPath As String, fNewName As String
    Dim fso As Object, oFile As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    'On Error Resume Next
    For Each cc In namesRng
        fName = cc.Value
        fNewName = cc.Offset(, -1).Value
        fPath = sourceFolder & fName & fExt
        fNewPath = destinationFolder & fNewName & fExt
        With fso
            If .fileexists(fPath) Then
                .CopyFile fPath, fNewPath, True
            End If
        End With
    Next cc
    Set fso = Nothing
    Set cc = Nothing
    Set namesRng = Nothing
    MsgBox "Done."
End Sub

Adjust as necessary the folder paths, file extension, names range.
A bit too simple but works if all is setup properly.
I can think of some extras and improvements but this will get you started.

 

[Cruiser69]

Thanks for this.
I have tried it on a couple of folders. It works well but seems to miss some images

 

[bobsan42]

The code does not check all images in the folder. It gets the names from the table, then copies the relevant image to a new folder with a new name.
If some image names are not in the table they will not be processed. Also the code will not know what new name to assign to them.