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.
- Select an unchecked file from a scan folder
- Open it in a pdf reader
- Read the contract number in the scan
- Go back to the folder again, select rename that particular file
- Type in the corresponding name and save it
- 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!
So I spent half an hour of my weekends (and life) devising a programming approach which streamlined the process to:
- Press Search and load the entire scan folder into the spreadsheet
- Click on the direct link in the filename to open the file in pdf reader
- Read the contract number in the scan
- Go back to the spreadsheet and press the right arrow key to reach the rename field
- Type in the corresponding name
- Repeat from step 2 for n times
- 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.
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).
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.