Renaming Folder Files from Excel?

Is there a way to extract a list into excel, a list of all the filenames in a folder, into a spreadsheet?  I would like them to be in column E, leaving the first row blank, using a userform to search for the folder I would like to focus on.  Copy them in as text.

This step I will do, a couple other extracts, into column A, B and C, concatenating them to create a new filename... using a better naming convention.

Then replace the existing filename from our first step column E, with a new one that will be in column D which will be a result of the concatenation.

Has anyone ever done this?  Please advise and thanks. -R-
RWayneHAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ken ButtersCommented:
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

Open in new window

0
RWayneHAuthor Commented:
ok.. can you explain a little more.  Where is the requested userform to seek out the target folder?  Please advise.   -R-
0
RWayneHAuthor Commented:
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-
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

RWayneHAuthor Commented:
error on Ln: Sub fileList(folderpath As String)

using Excel 2007
0
Ken ButtersCommented:
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
0
Ken ButtersCommented:
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.
0
RWayneHAuthor Commented:
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
0
Ken ButtersCommented:
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

Open 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.
0
RWayneHAuthor Commented:
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-
0
Ken ButtersCommented:
I believe this is what you asked for.

see attached file:
Book3.xlsm
0
RWayneHAuthor Commented:
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
0
Ken ButtersCommented:
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.
0
Ken ButtersCommented:
updates complete.

stored path to folder in hidden worksheet called "FormInfo"
corrected the currentRow error
changed to close form after rename complete
changed to add msgbox when rename complete - so user is aware it happened succesfully.
Book3.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RWayneHAuthor Commented:
EXCELent!!  works great..  thanks. -R-
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.