Comparing File Names with Cell Values in Excel Using VBA

Working with file names in Excel that contain numerical amounts can be tricky, especially when you need to compare those amounts with values within your spreadsheets. This article will guide you through creating a VBA macro that compares the amount in the file name with the amount in the last row of column G. If they don’t match, the macro will rename the file and save it.

Why This Problem Happens

The need to compare and synchronize data between file names and worksheet cells is common in many businesses. This task can be challenging because:

• Amounts in file names can appear in different locations (beginning, middle, end)
• There are formatting issues such as commas and periods
• Manual verification is time-consuming and error-prone

Step-by-Step Solution

1. Set Up Your Workbook

First, ensure your Excel file is structured properly:

• Column G should contain the amounts to be compared
• The last row of column G is the key value to compare with the file name

2. Write the VBA Macro

Open the Visual Basic for Applications editor by pressing Alt + F11. Insert a new module and add the following code:

Sub CompareFileNameWithCell()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim fileNameAmount As String
    Dim cellAmount As String
    Dim amountInFileName As String
    Dim newFileName As String

    ' Set the workbook and worksheet you are working with
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet1")

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

    ' Extract the amount from the last cell in column G
    cellAmount = ws.Cells(lastRow, "G").Value
    cellAmount = Replace(cellAmount, ",", "")
    cellAmount = Replace(cellAmount, ".00", "")

    ' Extract the amount from the file name
    fileNameAmount = Mid(wb.Name, InStr(wb.Name, " ") + 1)
    fileNameAmount = Left(fileNameAmount, InStr(fileNameAmount, " "))

    ' Remove commas and periods for comparison
    amountInFileName = Replace(fileNameAmount, ",", "")
    amountInFileName = Replace(amountInFileName, ".00", "")

    ' Compare the amounts
    If amountInFileName  cellAmount Then
        MsgBox "The file name is wrong. You need to correct it to ADFGGT " & cellAmount & ".00 OUT"

        ' Create the new file name
        newFileName = "ADFGGT " & cellAmount & ".00 OUT"
        Name wb.FullName As newFileName

        MsgBox "File renamed successfully!"
    Else
        MsgBox "The amounts match."
    End If
End Sub

3. Run the Macro

Close the VBA editor and run the macro by pressing Alt + F8, selecting CompareFileNameWithCell, and clicking Run. The macro will check if the amounts match, and if not, it will rename the file.

Advanced Variation

To handle more complex scenarios where the amount could be in different positions within the file name, you can enhance the VBA code:

Sub EnhancedCompareFileNameWithCell()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim fileNameAmount As String
    Dim cellAmount As String
    Dim extractedAmount As String
    Dim newFileName As String

    ' Set the workbook and worksheet you are working with
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet1")

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

    ' Extract the amount from the last cell in column G
    cellAmount = ws.Cells(lastRow, "G").Value
    cellAmount = Replace(cellAmount, ",", "")
    cellAmount = Replace(cellAmount, ".00", "")

    ' Function to extract numerical amount from file name
    extractedAmount = ExtractAmountFromFileName(wb.Name)

    ' Compare the amounts
    If extractedAmount  cellAmount Then
        MsgBox "The file name is wrong. You need to correct it to ADFGGT " & cellAmount & ".00 OUT"

        ' Create the new file name
        newFileName = "ADFGGT " & cellAmount & ".00 OUT"
        Name wb.FullName As newFileName

        MsgBox "File renamed successfully!"
    Else
        MsgBox "The amounts match."
    End If
End Sub

Function ExtractAmountFromFileName(fileName As String) As String
    Dim pattern As String
    Dim regEx As Object
    Dim matches As Object
    Dim match As Object

    Set regEx = CreateObject("VBScript.RegExp")
    pattern = "\d+,\d+\.\d+"
    regEx.Pattern = pattern
    regEx.Global = True
    regEx.IgnoreCase = False

    Set matches = regEx.Execute(fileName)

    For Each match In matches
        ExtractAmountFromFileName = Replace(match.Value, ",", "")
        ExtractAmountFromFileName = Replace(ExtractAmountFromFileName, ".00", "")
        Exit Function
    Next match
End Function

Common Mistakes or Misconceptions

The following are common mistakes users make when implementing such macros:

• Not accounting for different formats of numbers (e.g., 1,000.00 vs. 1000)
• Assuming the amount in the file name will always be in the same position
• Overlooking the need to handle special characters and spaces

Tool Recommendation

For handling complex data extraction and automation tasks in Excel, consider using CelTools. CelTools offers a wide range of features that can significantly enhance your productivity:

Conclusion

Comparing file names with cell values in Excel can be made efficient and accurate using VBA. By following the steps outlined above, you can automate this process, saving time and reducing errors. Consider integrating tools like CelTools for even more advanced data handling capabilities.

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