Fun with Files

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
 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)
        MsgBox "file: " & NameArray(x, 1) & " cannot be found"
    End If
    x = x + 1
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))
 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.

Example file:



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Create a free website or blog at

Up ↑

%d bloggers like this: