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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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!
LearnReporting 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,

LearnReporting 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 !
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.