VBA: Rename Files

=========
QUESTION
———
How can I use VBA to loop through and rename a long list of files in several sub-directories?

 

=========
ANSWER
———
see EXAMPLES

 

=========
EXAMPLES
———
Private Sub RenameFiles_Click()

    Dim fName As String
    Dim fName_New
    Dim myPath As String
    Dim myCounter As String
    
    DoCmd.Hourglass True
    DoCmd.SetWarnings False
    
    Set rsStore = CurrentDb.OpenRecordset("Stores_WithAll", dbOpenDynaset, dbReadOnly)
    rsStore.MoveFirst
    
    myCounter = 0
    
    While Not rsStore.EOF
        myCounter = myCounter + 1
        myPath = "P:StoresappsreportsHistory" & Format(rsStore!Store, "0000") & ""
        fName = myPath & "RTESTIGNORE_S" & Format(rsStore!Store, "0000") & ".7077.15-Aug-11.pdf"
        fName_New = myPath & "RTESTIGNORE.7077.15-Aug-11.pdf"
        
        If Len(Dir(fName)) = 0 Then 'Check to see if file exists
        
                Me.CurrentStatus = _
                    myCounter & " - " & fName & " -- No File" & vbCrLf & _
                    Me.CurrentStatus
                Me.Repaint 'tell user no file found
                
            Else
            
                Name fName As fName_New 'rename file
                
                Me.CurrentStatus = _
                    myCounter & " - Renamed file: " & vbCrLf & _
                    fName & vbCrLf & _
                    " to " & vbCrLf & _
                    fName_New & vbCrLf & _
                    Me.CurrentStatus
    
                'fName & " -- File found! -- Count: " & myCounter & vbCrLf & _
                'Me.CurrentStatus
            Me.Repaint
            
        End If
        
        rsStore.MoveNext
    Wend
    
    rsStore.Close
    
    DoCmd.SetWarnings True
    DoCmd.Hourglass False
    
End Sub

 

=========
APPLIES TO / KEY WORDS
———
VBA
Rename
Check if File Exists

 

=========
REF
———
http://social.msdn.microsoft.com/Forums/en/vbgeneral/thread/a516b87c-acb6-4c7c-98e6-818594c16d09
http://www.vbforums.com/showthread.php?t=337283

 


http://anySiteSupport.com
http://anySiteHosting.com

By | 2011-09-14T10:59:47+00:00 September 14th, 2011|Computers, Documentation, Microsoft, VBA|0 Comments