Excel macro: File renamer

A few months ago, the boss at my mom’s workplace tasked her team with renaming the generically-named files from the scanner. It was a no-brainer except that there were thousands of those contract scans and they had 2 weeks to complete the task.

When she came home lamenting how inefficient a few members of her team were and after I had listened to her complaints for an hour and a half, I offered her a solution to improve the efficiency. And so I wrote a simple spreadsheet macro using Visual Basic for Applications (VBA) to cut down on the number of button presses she had to do in a day.

REQUIREMENTS AND CONSTRAINTS

The steps were relatively straightforward.

  1. Select an unchecked file from a scan folder
  2. Open it in a pdf reader
  3. Read the contract number in the scan
  4. Go back to the folder again, select rename that particular file
  5. Type in the corresponding name and save it
  6. Repeat from step 1 for n times

Certainly, it wasn’t the most efficient approach to the problem. This process is very prone to human error and it involves opening and switching a lot of windows and moving the mouse around, a lot!

DESIGN

So I spent half an hour of my weekends (and life) devising a programming approach which streamlined the process to:

  1. Press Search and load the entire scan folder into the spreadsheet
  2. Click on the direct link in the filename to open the file in pdf reader
  3. Read the contract number in the scan
  4. Go back to the spreadsheet and press the right arrow key to reach the rename field
  5. Type in the corresponding name
  6. Repeat from step 2 for n times
  7. Press Rename to rename everything in one go

Originally, when I designed program, I also thought of using the tool myself too so I keep it rather general. Since I don’t know the condition of the scans and doing complete Optical character recognition (OCR) algorithm is a bit of a bother, plus, I don’t want to autotomize the entire thing and put my mom out of job.

That would be bad, really bad.
I refrained from doing so.

I didn’t intend to spend too much time on the interface either so I chose Excel and VBA; a strong combo I know to have worked wonderfully in the past for data management tasks at minimal investment effort; as the programming platform.

IMPLEMENTATION

It took me a little less than an hour to go from interface design to code implementation.

The spreadsheet contains 3 columns: Current filename (A), Rename into (B) and Extension generator /w drag-copy formula (C). The drag-copy formula simply concatenate the new filename without extension in column B and the extension from the old filename.

This feature was meant to save my mom the trouble typing down the file extensions and it was done with the follow Excel formula:

=CONCATENATE($B2, ".", RIGHT($A2, LEN($A2) - FIND(".", $A2)))

For VBA macro functions, the spreadsheet has 2 buttons: Search (CommandButton1) and Rename (CommandButton2) and a static field to display the currently selected Directory. The VBA code is as follow

Private Sub CommandButton1_Click() '<<< Search button
On Error GoTo Error_handler:
 
    GetFileNames
         
Exit Sub
Error_handler:
MsgBox "An error has occured!"
 
End Sub
 
Sub GetFileNames()
Dim xRow As Long
Dim xDirect$, xFname$, InitialFoldr$
InitialFoldr$ = "C:\" '<<< Startup folder to begin searching from
 
With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = Application.DefaultFilePath & "\"
        .Title = "Please select a folder to list Files from"
        .InitialFileName = InitialFoldr$
        .Show
     
    If .SelectedItems.Count <> 0 Then
     
    xDirect$ = .SelectedItems(1) & "\"
    xFname$ = dir(xDirect$, 7)
     
        Do While (Cells(2, 1).Offset(xRow) <> "")
            Cells(2, 1).Offset(xRow) = ""
            Cells(2, 2).Offset(xRow) = ""
            xRow = xRow + 1
        Loop
         
        xRow = 0
 
        Do While xFname$ <> ""
            Cells(2, 1).Offset(xRow) = xFname$
            ActiveSheet.Hyperlinks.Add Cells(2, 1).Offset(xRow), xDirect$ + xFname$ '<<< Add hyperlink
            xRow = xRow + 1
            xFname$ = dir
        Loop
         
        MsgBox "Search completed!"
        Cells(7, 5) = xDirect$ '<<< Display the current directory path at cell E7
    End If
End With
End Sub
 
Private Sub CommandButton2_Click() '<<< Rename button
On Error GoTo Error_handler:
 
    Dim Cell As Range
    Dim index As Integer
    index = 0
     
    Do While Cells(2, 1).Offset(index) <> ""
        If ((Cells(2, 2).Offset(index) <> Cells(2, 1).Offset(index)) And (Cells(2, 2).Offset(index) <> "")) Then
            Name Cells(7, 5) & Cells(2, 1).Offset(index) As Cells(7, 5) & Cells(2, 2).Offset(index)
            Cells(2, 1).Offset(index) = Cells(2, 2).Offset(index)
            ActiveSheet.Hyperlinks.Add Cells(2, 1).Offset(index), Cells(7, 5) & Cells(2, 2).Offset(index)
        End If
        index = index + 1
    Loop
     
        MsgBox "All files have been renamed!"
         
Exit Sub
Error_handler:
MsgBox "An error has occured!"
End Sub

With very basic error handlers.

A few other built-in Excel features were implemented on top of the macro. The columns have data filter enabled and can be sorted in alphabet order. Conditional formatting was used to highlight duplicate new filenames.

Other possibilities include data validation to rule out invalid file names (special symbols and so on) and freezing the first row to make it easier to crosscheck the filenames (although there are only 3 columns, not that many, really).

COMMENTS

Overall, I think this is a very interesting small project I’d like to share. It’s practical alright, my mom was able to complete her two-week task in 4 days and spent the rest of her time on facebook. She was lovey dovey for a few days and then she started lamenting on how bored she was at work now that she had nothing better to do than pretending she was still doing the task.

You know, easy money for facebooking the entire week. Too bad, they didn’t have bonuses for early task completion or extra works. That’s the reality of working in a government-owned monopoly. You work for the same monthly pay whether you work to the maximum or barely skirting the minimum cut-off.

That asides, considering the use of built-in Excel features for added values, the mini-project showed what the engineering process is truly about: it is about “the integration and efficient use of existing tool to new applications”.

Or, as my old professor would put it: “Don’t reinvent the wheel”.

Oh yeah, this tool is pretty useful for managing my ever expansive music library, picture library, and e-book library too. It’s definitely my favorite mini-app.

DOWNLOAD: http://1drv.ms/1gE1NYk

Advertisements

Published by

fujihita

Self-learner, designer, author and programmer.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s