Excel/File rename

Advertisement


Question
QUESTION: Jerry;
i have about 4 thousand pdf files and their names start with a 6 digit number. I have a cross-reference excel spread sheet with two columns - that six digit number and its corresponding member UID. Is there a way to have all these pdf files renamed so they start with the corresponding UID instead of the six digit number? Even just adding the corresponding UID to the pdf file name would be sufficient....  Thank you very much for any advice!!!!
George

ANSWER: THis macro should do it... it will mark files NOT found in column C.


Option Explicit

Sub RenamePDFs()
Dim fPATH As String, fNAME As String, OldCodes As Range, MyCode As Range, NotFound As Boolean

fPATH = "C:\Path\To\MyFiles\"          'remember the final \ in the path string
         'set a range to all used cells in column A
Set OldCodes = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))

For Each MyCode In OldCodes          'check each code one at a time
   If Len(MyCode) = 6 Then          'make sure it's a 6-digit code
       fNAME = Dir(fPATH & MyCode.Value & "*.pdf")     'find the filename in the fPATH
       If Len(fNAME) > 0 Then          'if the file is found, rename it
         Name fPATH & fNAME As fPATH & Replace(fNAME, MyCode.Value, MyCode.Offset(, 1).Value)
       Else          'if file is not found mark column C
         MyCode.Offset(, 2).Value = "Not found"
         NotFound = True
       End If
   End If
Next MyCode
         
If NotFound Then MsgBox "Not all files were found, missing codes were marked in column C"

End Sub


---------- FOLLOW-UP ----------

QUESTION: Jerry;
i have already thanked you and rated you the highest possible.
It worked like a charm, i was amazed. I am just curious how much would the macro change if i wanted to KEEP the six digit number and PREPEND the UID in front of it EG. UID_123456_file01.pdf. And I also did not tell you that i had duplicate pdf files starting with the same 6 digit number - so i had to run your macro in severall passes - which was no big deal. What would have to change, to do all the renames in one pass?
again, incredibly thankfull for your help, you saved me tons of work !!!!
George.

PS. If the changes are significant and require a lot of time on your part, dont bother with it, i am more than happy with your original macro!

Answer
Like so:

Option Explicit

Sub RenamePDFs()
Dim fPATH As String, fNAME As String, OldCodes As Range, MyCode As Range, NotFound As Boolean

fPATH = "C:\Path\To\MyFiles\"           'remember the final \ in the path string
                                        'set a range to all used cells in column A
Set OldCodes = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))

For Each MyCode In OldCodes                             'check each code one at a time
    If Len(MyCode) = 6 Then                             'make sure it's a 6-digit code
        fNAME = Dir(fPATH & MyCode.Value & "*.pdf")     'find the filename in the fPATH
        If Len(fNAME) > 0 Then                          'if at least one file is found, start a renaming loop
            Do While Len(fNAME) > 0                     'rename the current found file
                Name fPATH & fNAME As fPATH & "UID_" & MyCode.Offset(, 1).Value & "_" & fNAME
                fNAME = Dir                             'get the next filenmae starting with the same code
            Loop
        Else
            MyCode.Offset(, 2).Value = "Not found"      'if file is not found mark column C
            NotFound = True
        End If
    End If
Next MyCode

If NotFound Then MsgBox "Not all files were found, missing codes were marked in column C"

End Sub

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jerry Beaucaire

Expertise

Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files

Experience

Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files ===================== I have been offering free assistance as an Excel aid on many web sites for many years: (http://www.excelforum.com - JBeaucaire) ======== (http://www.askmehelpdesk.com/spreadsheets - JBeaucaire) ======= (http://www.mrexcel.com/forum - jbeaucaire)

Education/Credentials
Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010

©2016 About.com. All rights reserved.