Renaming Folder Files from Excel?

This code will do what you requested.

Please note prerequisite of adding library reference to "Microsoft Scripting Runtime" in VBA editor.

In this example, I am calling the routine "FileList" from testit.  you just need to pass "FileList" a string containing path of the folder you want to process.

Option Explicit

Sub testit()
    fileList "C:\Testing"
End Sub
'
' To use make sure you select :
'    "Microsoft Scripting Runtime" from Tools / References
'
Sub fileList(folderpath As String)

    Dim fso As FileSystemObject
    Dim aFile As File
    Dim aFolder As Folder
    Dim aSheet As Worksheet
    Dim CurrentRow As Long
    
    Set fso = New FileSystemObject
    Set aFolder = fso.GetFolder(folderpath)
    Set aSheet = ThisWorkbook.Sheets("Sheet1")
    
    CurrentRow = 2
    For Each aFile In aFolder.Files
            
    ' Store name in Column E
    
        aSheet.Cells(CurrentRow, 5) = aFile.Name
        CurrentRow = CurrentRow + 1
    
    ' Rename file from name in columne E to new Name from Column D
        
        If aSheet.Cells(CurrentRow, 4) <> "" Then
            Name aFolder.Path & "/" & aSheet.Cells(CurrentRow, 5) As aFolder.Path & "/" & aSheet.Cells(CurrentRow, 4)
        End If
    Next

End Sub

Select allOpen in new window

ok.. can you explain a little more.  Where is the requested userform to seek out the target folder?  Please advise.   -R-

not clear on Ln 6, 7, 8 and 9.  Need this refined a bit.  We are talking about 20K files in one of the largest folders.  -R-

error on Ln: Sub fileList(folderpath As String)

using Excel 2007

I interpreted your question to mean that you were going to create the userform yourself, and that you only need the code necessary to populate Column E.

I've created a userform in the attached workbook, and added the code necessary to populate the files in Column 'E' based on the userform.

the way the attached workbook works :  if Column D is empty when you run it, then Column E will be populated, but the rename will be skipped.

If Column D contains a string, it will be used to rename the file listed in Column E.

NOTE: make sure you test it on a small-subset of your files, to make sure it works like you want it to, before renaming 20k+ files.
Book3.xlsm

6,7,8,9 are a comment.

From within the Visual Basic Editor... which you can get to by hitting ALT-F11 when in Excel, you will need to add a library reference to Microsoft Scripting runtime.

The code is dependent upon that reference.

the way you add a reference is

1) ALT-F11 to open up VBA Editor
2) select 'Tools' Menu item
3) Select  'References"
4) Scroll down until you see 'Microsoft Scripting Runtime' and place a check mark next to it.
5) select ok button

NOTE : This has already been done in previously attached Excel file.  These steps would only be necessary when creating the solution from scratch.

Ok..  I do however need the code to rename the files too.  After I import the needed characteristics to detemine the new filename.  The last step is to rename all the files to the new naming convention.  I do not know how to take the name in column D and replace it with the name of the original in column E

The renaming is done by this code (which is already part of the code)

   ' Rename file from name in columne E to new Name from Column D
        
        If aSheet.Cells(CurrentRow, 4) <> "" Then
            Name aFolder.Path & "/" & aSheet.Cells(CurrentRow, 5) As aFolder.Path & "/" & aSheet.Cells(CurrentRow, 4)
        End If

Select allOpen in new window

In order for it to work, you have to have the string in Column D that you want the file(s) to be renamed to.

i need to make this a two step process, because there is a bunch of things in need to do before doing the actual file renaming.  Once your macro sets column E, I then have to do some work on the file.  Can we create two buttons?  One to pull the files in from selected folder and another to rename them back to the same folder, when I am ready and done populating column D?  This would be ideal.  This is very close.  Thanks. -R-

I believe this is what you asked for.

see attached file:
Book3.xlsm

can we take the extra button named Rename Files out of the userform? and put it under the Open Form?  When I import the filenames I exit the userform, and when I re-enter the user form after making all my edits in column D.  It should already know where it got the files from.  Am I not using this correctly?  Do we have to store the path somewhere that I pulled the files from?  Please advise and thanks.  -R-

 the Rename Files fails on CurrentRow, 4...  a compile error that says it is not defined.

' Rename file from name in columne E to new Name from Column D

    If aSheet.Cells(CurrentRow, 4) <> "" Then
        Name aFolder.Path & "/" & aSheet.Cells(CurrentRow, 5) As aFolder.Path & "/" & aSheet.Cells(CurrentRow, 4)
    End If

The "Open Form" is a generic Microsoft Open Folder Dialog box, so that is not going to be possible to add buttons to it.  It sort of comes that way "off the shelf" so to speak.

As it stands right now, once you close the form, the variables in the form, including the path to the files, is lost.

Yes... I would need to save that folder path somewhere.

Also...I'll review the error you are getting.

EXCELent!!  works great..  thanks. -R-