Jump to content
Macro Express Forums

Editing The Names of 3000 files from an excel database

Recommended Posts

I have 3000 image files named random things:








I have an excel sheet with the following:


OLD NAME | New Name

daa.jpg | gar.jpg

ngh.jpg | hgr.jpg

fgj.jpg | jkr.jpg



Any advice on a maxcro that would go through and rename all 3000 based on the befire and after names in excel?

Link to comment
Share on other sites

I would create a comma-delimited text file of the 2 columns of interest from the Excel spreadsheet.

Then I'd write a macro:

- read the comma delimited text file into a variable

- split this variable into an array (declare this array in the variables tab and define 3000 elements), using comma for the "on" field

- run through the 3000 image files; for each image file:

- - extract its name

- - locate its name in the array, searching every alternate value starting at 1

- - add 1 to the index of the located array value - this gives you your new name

Note: If you sort the array on the before name, and have the image files in name sequence (NTFS does this for you automatically) then your loop does not need to return to 1 for each new search.


There are many alternative approaches to this (I can think of at least 3), but this one should be reasonably easy to implement.

Link to comment
Share on other sites

  • 2 weeks later...

I would do similar but I would split the before and afters into two arrays. One for the before and one for after. Do the search as Paul said but when I found a match the counter is the index which will match the after array so there's no math. Also it's half as much searching.


Also loading the arrays would probably be simpler to do using ASCII Text File Process.


Another alternative would use the clipboard to copy directly from Excel and split that up. It just depends on user involvement. IE if you want to automate this it's not a great idea. I do this a lot to avoid having the user create a file. Much simpler to select the range in Excel and copy. But only if the user already has it open. Just depends on how it's being used.


Also be aware you will probably need to handle exceptions. For instance what to do if a match is not found? Or what if you have already renamed a file with an 'after' value? What if a after value steps on an existing 'yet to be named' file. One trick for that is to use a temp folder on the same drive and then move them back when done.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...