Automatically Correcting File Names in Excel Using VBA

Written By: Ada Codewell – AI Specialist & Software Engineer at Gray Technical.

The Challenge of Automating File Name Corrections

Excel users often face the challenge of ensuring that file names accurately reflect their contents, especially when dealing with multiple files across various folders. One common scenario is comparing a file name’s amount to the total in column G and correcting discrepancies.

Why It Happens

File naming conventions can become inconsistent for several reasons:

Manual errors during file creation or renaming
Different team members using varying formats
Automated processes that don’t account for all variables

These inconsistencies can lead to confusion, difficulty in locating files, and potential data mismatches.

A Practical Solution: VBA Automation

Using Excel VBA (Visual Basic for Applications), we can automate the process of comparing file names with their contents and correcting discrepancies. This approach ensures consistency across multiple files and folders while minimizing manual effort.

Step-by-Step Implementation

1. Setup Your Environment

First, ensure you have access to the VBA editor in Excel. You can open it by pressing Alt + F11. Create a new module where we will write our code.

2. Identify Files and Extract Relevant Information

We need to loop through all files in specified directories, extract the amount from their names, and compare it with the total in column G of each file’s last row:

Sub CorrectFileNames()
    Dim mainFolder As String
    Dim subFolder As String
    Dim fileName As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim fileAmount As Double
    Dim sheetTotal As Double

    ' Set the main folder path
    mainFolder = "C:\Users\KKL\Desktop\CHANGE"

    ' Loop through all subfolders in the main folder
    For Each subFolder In Dir(mainFolder & "\*", vbDirectory)
        If subFolder  "." And subFolder  ".." Then
            fileName = Dir(subFolder & "\*.xlsm")
            Do While fileName  ""
                Set wb = Workbooks.Open(subFolder & "\" & fileName)

                ' Assume the worksheet is named "Sheet1"
                Set ws = wb.Sheets("Sheet1")

                ' Find the last row in column G
                lastRow = ws.Cells(ws.Rows.Count, 7).End(xlUp).Row

                ' Extract amount from file name (assuming it's always formatted similarly)
                fileAmount = GetAmountFromFileName(fileName)

                ' Sum up values in column G to get the total
                sheetTotal = Application.WorksheetFunction.Sum(ws.Range("G8:G" & lastRow))

                ' Compare and correct if necessary
                If fileAmount  sheetTotal Then
                    ' Create new file name with corrected amount
                    Dim newFileName As String
                    newFileName = ReplaceFileNameAmount(fileName, sheetTotal)

                    ' Save the workbook with a new name
                    wb.SaveAs subFolder & "\" & newFileName

                    ' Close the workbook without saving changes to original file
                    wb.Close False
                Else
                    ' Close the workbook if no changes needed
                    wb.Close False
                End If

                ' Move to next file in directory
                fileName = Dir
            Loop
        End If
    Next subFolder
End Sub

Function GetAmountFromFileName(fileName As String) As Double
    Dim arr() As String
    arr = Split(fileName, " ")
    For i = LBound(arr) To UBound(arr)
        If IsNumeric(Replace(arr(i), ",", "")) Then
            GetAmountFromFileName = CDbl(Replace(arr(i), ",", ""))
            Exit Function
        End If
    Next i
End Function

Function ReplaceFileNameAmount(fileName As String, newAmount As Double) As String
    Dim arr() As String
    arr = Split(fileName, " ")
    For i = LBound(arr) To UBound(arr)
        If IsNumeric(Replace(arr(i), ",", "")) Then
            arr(i) = Format(newAmount, "#,##0.00")
            Exit Function
        End If
    Next i
    ReplaceFileNameAmount = Join(arr, " ")
End Function

3. Running the Macro

Once you’ve written and saved your VBA code, run the macro by pressing F5 in the VBA editor or from within Excel using Alt + F8, selecting “CorrectFileNames” and clicking “Run”.

Alternative Approaches Using Third-Party Tools

While VBA is a powerful tool for automating tasks, there are third-party tools that can simplify certain aspects of this process:

CelTools: This add-in offers 70+ extra Excel features for auditing, formulas, and automation. It can help with more complex data validation and manipulation tasks.
PDF GT: If your files need to be converted or extracted from PDFs before processing in Excel, PDF GT provides OCR capabilities and page extraction.

Advanced Variation: Handling Complex File Name Formats

For more complex file name formats where the amount could be positioned differently (first, second, third position), we need to enhance our extraction function:

Function GetAmountFromComplexFileName(fileName As String) As Double
    Dim arr() As String
    arr = Split(fileName, " ")
    For i = LBound(arr) To UBound(arr)
        If IsNumeric(Replace(arr(i), ",", "")) Then
            GetAmountFromComplexFileName = CDbl(Replace(arr(i), ",", ""))
            Exit Function
        End If
    Next i
End Function

Function ReplaceInComplexFileName(fileName As String, newAmount As Double) As String
    Dim arr() As String
    arr = Split(fileName, " ")
    For i = LBound(arr) To UBound(arr)
        If IsNumeric(Replace(arr(i), ",", "")) Then
            arr(i) = Format(newAmount, "#,##0.00")
            Exit Function
        End If
    Next i
    ReplaceInComplexFileName = Join(arr, " ")
End Function

Common Mistakes and Misconceptions

When implementing this solution, be mindful of these potential pitfalls:

Incorrect File Paths: Ensure all file paths are correct. Use the correct backslashes (\\) in VBA.
File Name Formats: Make sure the extraction logic accounts for all possible variations in your file name formats.
Workbook Structure: Confirm that each workbook follows the expected structure (worksheet names, column locations).

Conclusion: Automating File Management with Excel VBA

By automating the correction of file names using Excel VBA, we can significantly reduce manual effort and ensure consistency across multiple files. This approach not only saves time but also minimizes errors that may arise from inconsistent naming conventions.

Author: Ada Codewell – AI Specialist & Software Engineer at Gray Technical

Working on Excel