Move and rename images to another folder based on 2 excel columns


For a project i'm working on i need to quickly rename several 100 images.
i'm familiar with batch rename or find/replace command, but since there's no constant in the new/old file names that is not an option:

I have a MS excell file with 2 columns
Column A - old file name - for example ABC.jpg
Column B - new file name - for example 123.jpg

the extension .jpg will be in the cell
i also have  2 image folders

C:\FolderA - contains over 4,000 product images
C:\FolderB - empty

the intention is that i define the range of cells Columns A&B, and call the range PHOTONAME
i execute the macro

the macro reads value from Cell A1 and checks if FolderA contains an image with the same name (this should NOT be case sensitive if possible)
if so, it make A COPY off this image to FolderB and RENAMES the image to the value of Cell B1 - same row

if the folderA has no image named with the value of cell A1, the macro moves on to the next row

an added difficulty might be that some images contain . - point and stripe
for example  123.456.jpg or 123-abc.jpg

Can someone create a macro for this ?
Who is Participating?
NorieVBA ExpertCommented:
Try this.
Sub MoveImageFiles()
Dim FSO As Object
Dim strDstFolder As String
Dim strSrcFolder As String
Dim strNewName As String
Dim strOldNAme As String
Dim cl As Range

    strSrcFolder = "C:\FolderA\"
    strDstFolder = "C:\FolderB\"
    Set FSO = CreateObject("Scripting.FileSystemObject")
    For Each cl In Range("PHOTONAME").Columns(1).Cells
        If FSO.FileExists(strSrcFolder & cl.Value) Then
            FSO.CopyFile strSrcFolder & cl.Value, strDstFolder & cl.Offset(, 1).Value
        End If
    Next cl

End Sub

Open in new window

ggcAuthor Commented:
Thank you!  it seems to be working fine with the test i just did.
i will try the full excel tomorrow, i dont have the file and folders at hand right now

i will credit you for this!
LET (Learn Excel in Tamil)Reporting Automation ExpertCommented:

Since you already know BAT comments, i am suggesting this

This will help to get the file names list in Note pad,

dir/a/b/-p/o:gen > names.txt

Open in new window

And copy the list and pasted into the attached excel From A6. on From B6 Just put the new name


From D6 you have the CMD for renaming , just open CMD and paste it, not this will run on the same folder,

If Macro will be convenient for you can use it, but Batch run will be always faster than the MACRO,

LET (Learn Excel in Tamil)Reporting Automation ExpertCommented:
Hi ggc,

You can ignore my solution, Just seen Norie's and your comments

ggcAuthor Commented:

This will save me hours of tedious work!
Many,many thanks !
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.