Some of the tasks I had to do at work involved running a couple of “master” spreadsheet models every month for new market conditions, for a lot of clients. Running the models took ages, but the part I loathed the most was, I had to rename each workbook individually. I figured, my time is far too valuable and created an Excel Spreadsheet Application to rename the files for me.
In this post, I will show you how to use the FileDialogue to select a folder and save its path to a cell. Then we will use the FileSystemObject, File and Folder objects to list the files in the spreadsheet and rename them from within Excel. Finally, we will combine these techniques to create a spreadsheet application that you can use to replace a string in the filenames of all the files in a folder.
Step 1: Fetching the Folder path
- The First step is to write a function thatusesFileSystemObject to fetch the path
'==================================================================================== 'A fairly generic Function to fetch a Forlder's Path 'Author : Ejaz Ahmed - email@example.com 'Date: 01 January 2013 'Website : https://strugglingtoexcel.wordpress.com/ '==================================================================================== Function OpenFolder(Optional ByVal AllowMulti As Boolean = False) As String 'Declare the FileDialog Object Dim diaFolder As FileDialog On Error Resume Next 'Standard Error Handling code 'Set the FileDialogue to be able to select only folders Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker) 'Set AllowMultiSelect property to true to allow the user 'to select multiple files diaFolder.AllowMultiSelect = AllowMulti 'Show the Dialog box diaFolder.Show 'Return the file path OpenFolder = diaFolder.SelectedItems(1) & "\" 'Empty the object Set diaFolder = Nothing Err.Clear 'Standard Error Handling code On Error GoTo 0 'Standard Error Handling code End Function
- Then we write a simple macro that writes the path to a cell in the sheet. Please feel free to download the Workbook and snoop around, for some of the ancillary macros won’t make complete sense unless you take a look at the actual spreadsheet layout. Click the image below to download the file.
Step 2: Listing files inside selected Folder
- Subsequently, we write a macro to list all the files in the selected folder into a range in the spreadsheet.
'==================================================================================== 'A macro that uses FileSystemObject to access the names of all the files in a folder 'Author : Ejaz Ahmed - firstname.lastname@example.org 'Date: 01 January 2013 'Website : https://strugglingtoexcel.wordpress.com/ '==================================================================================== Sub GetFiles(Optional ByVal booDummy As Boolean = True) 'Declare the FileSystemObject Objects Dim objFSO As FileSystemObject Dim objFolder As Folder Dim objFile As File 'Declare ancillary variables Dim strPath As String Dim lngCount As Long 'Specify the path to the folder strPath = shtTool.Range("FilePath").Value2 'Create an instance of the FileSystemObject Set objFSO = CreateObject("Scripting.FileSystemObject") 'Get the folder On Error Resume Next 'Standard Error Handling code Set objFolder = objFSO.GetFolder(strPath) If Not Err.Number = 0 Then 'Standard Error Handling code MsgBox "Cannot find Folder. Please Check if the specified folder exists." & _ vbNewLine & "VBA Error Description : " & Err.Description Err.Clear 'Standard Error Handling code End End If On Error GoTo 0 'Standard Error Handling code 'If the folder does not contain files, exit the sub If objFolder.Files.Count = 0 Then MsgBox "No files were found...", vbExclamation Exit Sub End If 'Set the Counter to 1 lngCount = 1 'Loop through each file in the folder For Each objFile In objFolder.Files 'Use the counter and named ranges to fill up the sheet. 'Please feel free to accomplish this however you see fit. 'This is my preferred method, for various self percieved advantages. shtTool.Range("FileNumber")(1).Offset(lngCount - 1).Value = lngCount shtTool.Range("FullPath")(1).Offset(lngCount - 1).Value = objFile.Path shtTool.Range("FileName")(1).Offset(lngCount - 1).Value = RemoveExtension(objFile.Name) shtTool.Range("Extension")(1).Offset(lngCount - 1).Value = FileExtension(objFile.Name) shtTool.Range("DateLastModified")(1).Offset(lngCount - 1).Value = Format(objFile.DateLastModified, "D MMM YYYY") shtTool.Range("NewName")(1).Offset(lngCount - 1).Formula = "=Prefix&SUBSTITUTE(FileName,ReplaceWhat,ReplaceWith)&Suffix" shtTool.Range("Override")(1).Offset(lngCount - 1).Value = vbNullString shtTool.Range("Override")(1).Offset(lngCount - 1).NumberFormat = "General" shtTool.Range("FinalName")(1).Offset(lngCount - 1).Formula = "=IF(ISBLANK(Override),NewName,Override)&Extension" 'Incriment the counter lngCount = lngCount + 1 Next objFile End Sub
Step 3: Design the Supporting structure to compose the new name
- A few of the things you might want to do to file names are appending text to the beginning or end of the file name; replace text in the file name with something else. These are the functionalities that I have added to my application.
- Note that adding a suffix needs theextensiontobeseperated out first and I have used the following functionstoaccomplish that:
'==================================================================================== 'Function to get the file extension 'Author : Ejaz Ahmed - email@example.com 'Date: 01 January 2013 'Website : https://strugglingtoexcel.wordpress.com/ '==================================================================================== Function FileExtension(ByVal strfilename As String) As String FileExtension = "." & Right(strfilename, Len(strfilename) - InStrRev(strfilename, ".", , vbTextCompare)) End Function '==================================================================================== 'Function to remove the file extension 'Author : Ejaz Ahmed - firstname.lastname@example.org 'Date: 01 January 2013 'Website : https://strugglingtoexcel.wordpress.com/ '==================================================================================== Function RemoveExtension(ByVal strfilename As String) As String RemoveExtension = Left(strfilename, InStrRev(strfilename, ".", , vbTextCompare) - 1) End Function
Step 4 : Write a macro to Rename the Files using ‘FileSystemObject’
- We add in the code that actually renames the files:
'==================================================================================== 'A macro that uses FileSystemObject to Rename all the files in a folder 'Author : Ejaz Ahmed - email@example.com 'Date: 01 January 2013 'Website : https://strugglingtoexcel.wordpress.com/ '==================================================================================== Sub RenameFiles(Optional ByVal booDummy As Boolean = True) 'Recalculate workbook to make sure the formula that composes the new names are 'Calculated shtTool.Calculate 'Declare the FileSystemObject objects Dim objFSO As FileSystemObject Dim objFile As File 'Create an instance of the FileSystemObject Set objFSO = CreateObject("Scripting.FileSystemObject") 'Declare and Initialize ancillary variables Dim rngFullPath As Range Dim rngFinalName As Range Set rngFullPath = shtTool.Range("FullPath") Set rngFinalName = shtTool.Range("FinalName") Dim lngCount As Long On Error Resume Next 'Standard Error handling code 'Loop through the Files imported into the spreadsheet and Renaming them. For lngCount = 1 To rngFullPath.Count Set objFile = objFSO.GetFile(rngFullPath(lngCount).Value) 'Error handling for files that are not available If Not Err.Number = 0 Then 'Standard Error handling code MsgBox "File Not Found." & vbNewLine & "VBA Error Description : " & Err.Description Err.Clear End End If objFile.Name = rngFinalName(lngCount).Value 'Error handling for invald file name If Not Err.Number = 0 Then 'Standard Error handling code MsgBox "Invalid Filename." & vbNewLine & "VBA Error Description : " & Err.Description Err.Clear End End If Next lngCount On Error GoTo 0 'Standard Error handling code End Sub
- Finally we assign all these macros to suitable buttons.
Instructions to use the Application:
- Download the file from the section below.
- Click the ‘ImportFiles‘ Button to browse for the folder that has the files you would like to rename.
- If you have changed any of the file names manually or added a few more files after importing them to the spreadsheet, you can click the ‘Refresh File List‘ button. This lists the files in the path selected earlier, without having to browse for it again.
- Add in the rules to the ‘Replace What‘, ‘Replace With‘, ‘Prefix‘, and ‘Suffix‘ ranges. You may also override the names with a custom rule or text by specifying it in the ‘Override‘ column. I have modified the file name to replace ‘December 2013‘ with ‘January 2014‘; Prefix ‘Model‘ to the filename; append ‘ – Done‘ to the end. Note that I have overridden the name for the third file. Do note that they will be reset after the files are renamed.
- Finally click the ‘Rename Files‘ button. This renames all the files, and lists the new file names. You may further modify the file names or choose to reset the tool by clicking the ‘Reset‘ button.
I saved this workbook in Excel 2003 format, considering the fact that most organizations may not have upgraded to the latest version of Microsoft Office yet. There may be some formatting related compatibility issues when opened in Excel 2003, do let me know if hinders accessibility. The workbook has a lot of supporting macros and hidden cells to make it user friendly. I have also incorporated a few self learned practices into this application, I will be writing dedicated posts about them in the near future. I have left the code unprotected, please feel free to change it however you please. However, when you develop a full fledged spreadsheet application, make sure you lock it down.