Somtimes, a simple bit of VBA code can be really really powerful. This code is that, for me. There are two parts to it, a loop over the files in a specific directory, and then, an action (in this case renaming).
Step 1. Get files & names
Firstly, lets pick up all the files in a particular folder. In this case, I use the folder that contains the file that has this macro in it. I do this, rather than using a filePicker, as it’s just simpler for me.
Sub GetFileNames() ' this one just sets the folder to the same as the active workbook. and iterates over each file in the folder 'it prints the names on the worksheet. folderspec = ActiveWorkbook.Path bob = 1 Dim fs, f, f1, fc, s Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFolder(folderspec) Set fc = f.Files bob = 2 For Each f1 In fc Sheets("Retrieve Filenames").Range("A" & bob).Value = f1.Name bob = bob + 1 Next MsgBox "Done!" End Sub
ActiveWorkbook.Path is the current workbook’s path. That I guess is pretty self explanatory
CreateObject(“Scripting.FileSystemObject”) creates an object that represents the filesystem on your computer, and gives you some methods to play with it.
One of those methods is the GetFolder method, it takes a path, and gives back an object that represents the folder. In this case, the path is the current workbooks path.
Files is a method that represents an array (? or array like object) of the files in the folder previously ‘grabbed’, so then we simply loop over the files using a For Each command. In this case, for each file, I put it’s name on a worksheet.
Step 1 done. We’ve got a list of all the files in a directory. Now:
Step 2. The renamer
The key line in this script is the one where I name the file. In VBA, this can be done by simply using the Name command. The format is: Name inputPath As outputPath
Sub renameFiles(NameArray As Variant) 'this one is the renamer. it takes the selection, iterates through it, names to new name! folderspec = ActiveWorkbook.Path x = 2 Do Until x > UBound(NameArray) If Dir(folderspec & "\" & NameArray(x, 1)) <> "" Then Name folderspec & "\" & NameArray(x, 1) As folderspec & "\" & NameArray(x, 2) Else MsgBox "file: " & NameArray(x, 1) & " cannot be found" End If x = x + 1 Loop End Sub
I’ve built this function so that it takes an array of input and outputs, which i’ve grabbed from a worksheet. This way, I can use formulae, lookups etc, to rename the files in cells, check them, and then apply the changes. Here’s the sub that I wrote that does this task.
Sub grabAndname() 'this one just selects the data, then sends it to the renamer Sheets("Rename Batch of files").Range("A3").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select renameFiles (Selection) End Sub
3. Notes and an example file
So, that’s basically it. I have a spreadsheet with these macros included, that is intended in one step, to grab all the file names in the same directory as the spreadsheet, and dump them onto a worksheet. Then, my process has been filter these, copy the ones I want to change onto the next worksheet, and put the new names next to them. Then run the renamer, and I’m done.
There is one other function that I’ve used fairly regularly in these kinds of processes, and I call it ‘replaceShit’, because there are a bunch of really common characters that are no good in filenames, like comma’s, slashes’ etc.
Public Function replaceShit(textString As String) 'this is a pretty simple macro to replace shitty strings that are unlikely to save in a filename. Dim charArray As Variant Dim repArray As Variant Dim x As Integer charArray = Array("/", "\", " ", ",", "&") repArray = Array("_", "_", "_", "_", "and") For x = 0 To UBound(charArray) textString = Replace(textString, charArray(x), repArray(x)) Next replaceShit = textString End Function
It’s pretty straight forward, and I agree, it could be better, but this is simple…. I have two arrays, the first one is the the characters to replace, and the second one is the characters to replace them with. These arrays MUST be the same length, and the order in each is important. When called, this looks over the input string, for each of the characters that need to be replaced, one at a time, and if found, replaces them.