Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    3 Star Lounger
    Join Date
    Jan 2007
    Location
    Massachusetts, USA
    Posts
    272
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Two part Macro needed for batch file renaming (Excel 2003 SP3)

    Hello,

    I have an excel spreadsheet with two columns in it.

    The first column contains all the original graphic names. The second column contains all the new graphic names.

    I am wondering if I can create a macro in Excel 2003, that will do the following:

    1. Link all the names in COLUMN A of the spreadsheet, to the actual graphics in my graphics folder.
    2. Rename all the graphics (through the Excel Macro) from the old name to the new name. For example, D5 becomes E5.

    The graphics are an assortment of various file types and file names, but the new names have a logical catalog number.

    Can such a macro be created to automate this process?

    I have tried 3rd party products, but the applications are not able to handle the assorted variety of graphics to be renamed.

    Currently, I am tasked with the renaming of thousands of legacy graphics.

    Your feedback is very much appreciated.

    Regards,

    Jim
    Attached Images Attached Images

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Two part Macro needed for batch file renaming (Excel 2003 SP3)

    2. could be done but what exactly do you mean by 1.?

  3. #3
    3 Star Lounger
    Join Date
    Jan 2007
    Location
    Massachusetts, USA
    Posts
    272
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Two part Macro needed for batch file renaming (Excel 2003 SP3)

    Hello Hans,

    Perhaps Excel is not the best way to handle this task.

    I just thought that I could create a column of legacy graphic names in Column A, that are hyperlinked
    to the actual graphic file name.

    Then, the macro would change D5 to E5 and D6 to E6 right down the list.

    The end result would be that my folder on my C drive (e.g. C:Temp) would
    have ALL OF THE ASSORTED graphic names changed through this spreadsheet front end.

    I am not sure if there is another way to do it outside of Excel. I am not simply changing 1001.bmp to 1001.emf. It is not that simple.

    There are multiple file types and a assorted variety of file names (as shown on my original screen shot).

    Is there a macro available that can handle the renaming of graphic file names - from a spreadsheet?

    I have thousands of legacy graphic names I must now change, to a new catalog order (e.g. G00010.jpg) and it would save
    me a lot of time if I could automate this process.


    Thanks as always.

    Jim

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Two part Macro needed for batch file renaming (Excel 2003 SP3)

    Renaming a.gif to b.jpg will not change the file type from .gif to .jpg so the result may not be what you want.

    Perhaps you can use a batch conversion/rename tool - do a Google search for graphics batch conversion or take a look at FastStone Photo Resizer - Powerful Image Converter/Resizer (free).

  5. #5
    3 Star Lounger
    Join Date
    Jan 2007
    Location
    Massachusetts, USA
    Posts
    272
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Two part Macro needed for batch file renaming (Excel 2003 SP3)

    Hello Hans,

    Actually I think I put a bad screenshot example up there before. The graphic extension will remain the same (.jpg or .emf. or .bmp or .png or .ai), but the name of the file (the file NAME itself) will get automatically changed the to name given in the new column on my spreadsheet. Here is another screen shot that will illustrate this process better ( I hope).
    Thanks,

    Jim
    Attached Images Attached Images

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Two part Macro needed for batch file renaming (Excel 2003 SP3)

    If you put the path and name in a column, or the path in one and the name in another or if all the same path, the name in a column and the path in a cell you can use the HYPERLINK function to link to the file no matter the type:

    =HYPERLINK(link_location,friendly_name)

    You could even put the "friendly_name" (what is displayed) in a column if desired. The path&name is the "link_Location". These columns with the paht/name and friendly_name may be hidden if desired...

    Steve

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Two part Macro needed for batch file renaming (Excel 2003 SP3)

    A batch rename utility will probably do what you want, but here is a macro:

    Sub RenameFiles()
    ' Modify as needed but keep trailing backslash
    Const strPath = "C:Pictures"
    Dim r As Long
    Dim n As Long
    n = Cells(Rows.Count, 4).End(xlUp).Row
    For r = 5 To n
    Name strPath & Cells(r, 4) As strPath & Cells(r, 5)
    Next r
    End Sub

  8. #8
    3 Star Lounger
    Join Date
    Jan 2007
    Location
    Massachusetts, USA
    Posts
    272
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Two part Macro needed for batch file renaming (Excel 2003 SP3)

    Hello Steve,

    Thanks for the post. I think I understand what you are saying about linking from the spreadsheet cells to each graphic name, but how do I create a Macro to:

    1. Find the name in the original column and automatically link to that graphic name in the cell.

    2. Have the macro look at the new graphic name (not yet linked) in the second column and then change D5 to E5, D6 to E6 and so forth - so that my end result is that I have automated
    the renaming of thousands of graphics. Is it possible to automate step 1 or do I have to manually add in the =HYPERLINK(link_location,friendly_name) first, for each cell (e.g. D5, D6, D7) - before I can do the compare and replace (e.g. D5 becomes E5, D6 becomes E6 and D7 becomes E7)?

    Thanks to you and Hans (and anyone else) for a potential solution.

    Regards,

    Jim

  9. #9
    3 Star Lounger
    Join Date
    Jan 2007
    Location
    Massachusetts, USA
    Posts
    272
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Two part Macro needed for batch file renaming (Excel 2003 SP3)

    Hi Hans,

    I think you posted the same time as me (missed your post). I will try out your suggestion now.
    Thanks,
    Jim

  10. #10
    3 Star Lounger
    Join Date
    Jan 2007
    Location
    Massachusetts, USA
    Posts
    272
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Two part Macro needed for batch file renaming (Excel 2003 SP3)

    Wonderful!
    Thanks Hans and Steve for the suggestions. This macro works perfect!

    -J

  11. #11
    3 Star Lounger
    Join Date
    Jan 2007
    Location
    Massachusetts, USA
    Posts
    272
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Two part Macro needed for batch file renaming (Excel 2003 SP3)

    Hello Hans,

    I spoke to soon. The macro you provided renamed some of the .emf, .ai, jpg, .vsd and .png files, but not all of them.
    The Macro actually stopped on an Error 53 file not found error message.
    This is what I can tell you about this spreadsheet I have.

    1. Macro starts at D5 and Ends at D725
    2. Macro starts at E5 and Ends at E725

    Hi, my name is Mike Wiggins, and I work with Jim. We have been getting Error Code 53 while running this macro. I put a breakpoint in the macro and it fails at the "NAME" line. I have found that the names being read by the macro are correct but for some strange reason, the concatenation of the names seems to cause the equivalent of a "file naming error" (my choice of words) message. Everything else seems fine. [sounds of me scratching my head].

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Two part Macro needed for batch file renaming (Excel 2003 SP3)

    Please tell us which file names cause the error.

  13. #13
    3 Star Lounger
    Join Date
    Jan 2007
    Location
    Massachusetts, USA
    Posts
    272
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Two part Macro needed for batch file renaming (Excel 2003 SP3)

    Here is the file with the code on the spreadsheet itstelf.
    Attached Files Attached Files

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Two part Macro needed for batch file renaming (Excel 2003 SP3)

    Thank you. But which file names cause the macro to fail?

  15. #15
    3 Star Lounger
    Join Date
    Jan 2007
    Location
    Massachusetts, USA
    Posts
    272
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Two part Macro needed for batch file renaming (Excel 2003 SP3)

    Hello Hans,
    I have no idea where the macro breaks or which graphics are causing the problem. The only error message I get is: Run Time Error 53 File Not Found.

    Any ideas?

    -J

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •