Rename files per table

[rheide]

Hello everyone,
I am trying to find a way to rename 1000+ files. I have a table with the old name and the new name. Here is what needs to happen for example:

1000_E.pdf --> ABC.pdf
1001_E.pdf --> INVOICE123.pdf
1002_E.pdf --> INV9991.pdf


I've read about the name function but I really don't know how to do this as a batch change.

Can anyone suggest a way to attack this?

Thanks in advance and have a great day,
Randy

 

[Tim K.]

The syntax for VBA Name statement is

Name OldFileNameWithPath As NewFileNameWithPath

Now what you have to do is open the table as a recordset and loop thru the records and change the files name.

Private Sub ChangeFilesName()
    Dim dbs As Object, rst As Object
    Dim strPath As String

    Set dbs = CurrentDb
    Set rst = dbs.Openrecordset("Table1")
    strPath = "c:\myfiles\"
    If Not rst.EOF THen
        Do While Not rst.EOF
            Name strPath & rst("OldFileNameField") As strPath & rst("NewFileNameField") 
            rst.MoveNext
        Loop
    End If
    rst.Close
    dbs.Close
    Set rst = Nothing
    Set dbs = Nothing
End Sub

 

[rheide]

Tim,
That did exactly what I wanted it to. Thank you soo much for your help. I appreciate it. Hopefully this code can come in useful for someone else.

Have a great day,
Randy

 

[KLL]

Tim K's code solved my problem! Thank you.

 

[bkivler]

This is great code - exactly what I need for a project I'm currently working on. My files are in a number of different folders...I have the folder names in my table as well but can't seem to get the looping to work correctly. I include my box field in the path and it correctly renames files in the first folder but then doesn't move onto subsequent folders. Could really use help with this - thanks in advance!