Loop through Excel files and compare files not working

cdfjdk1 

It seems like there are a few issues in your VBA code that might be causing it to not work as expected. Let us go through them and correct them step by step.

1. Variables Initialization: Ensure that all variables are properly declared and initialized. In VBA, each variable should be explicitly declared with its type (Dim statement). Make sure to initialize each variable before using it.
2. Looping through Files: When looping through files in a folder, you should use the FileSystemObject correctly to get the files in the folder. Also, use a For Each loop to iterate through each file.
3. Opening Workbooks: When opening workbooks, make sure to handle errors properly, especially if a workbook fails to open for any reason.
4. Worksheet Comparison: Ensure that you correctly compare the worksheets between the edited set and the reference set.

Here is an updated version of your code with corrections and comments:

Vba Code is untested backup your file first.

Sub Compare_Spreadsheets()

    Dim editPath As String
    Dim refPath As String
    Dim filename As String
    Dim refFname As String
    Dim editwbk As Workbook
    Dim refwbk As Workbook
    Dim editws As Worksheet
    Dim refws As Worksheet
    Dim editFolder As Object
    Dim FileSystem As Object
    Dim editFile As Object
    
    editPath = "C:\path\Set_1\"
    refPath = "C:\path\Set_2\"
    
    ' Set up reference workbook (assuming you want to compare with one specific reference workbook)
    refFname = "ReferenceWorkbook.xlsx"  ' Update this with the actual reference workbook name
    
    ' Open the reference workbook
    Set refwbk = Workbooks.Open(refPath & refFname)
    Set refws = refwbk.Worksheets(1) ' Assuming the worksheet index is 1, adjust as necessary
    
    ' Loop through files in edited folder
    Set FileSystem = CreateObject("Scripting.FileSystemObject")
    Set editFolder = FileSystem.GetFolder(editPath)
    
    For Each editFile In editFolder.Files
        filename = editFile.Name
        
        ' Check if the file is an Excel file
        If InStr(1, filename, ".xls") > 0 Or InStr(1, filename, ".xlsx") > 0 Then
            
            ' Open the edited workbook
            Set editwbk = Workbooks.Open(editPath & filename)
            
            ' Assuming each workbook has only one worksheet
            Set editws = editwbk.Worksheets(1) ' Adjust index if needed
            
            ' Compare each cell in the edited worksheet with the reference worksheet
            For Each cell In editws.UsedRange
                If cell.Value <> refws.Range(cell.Address).Value Then
                    cell.Interior.Color = vbYellow
                    MsgBox "Changed value in " & cell.Address & " in sheet " & editws.Name
                End If
            Next cell
            
            ' Close the edited workbook without saving changes
            editwbk.Close SaveChanges:=False
            
        End If
    Next editFile
    
    ' Close the reference workbook
    refwbk.Close SaveChanges:=False
    
End Sub

Explanation of Changes:

• Variable Declarations: All variables (editPath, refPath, filename, etc.) are properly declared and initialized.
• File Looping: Uses For Each editFile In editFolder.Files to correctly loop through each file in the editPath folder.
• Workbook and Worksheet Handling: Opens both edited and reference workbooks, and compares each cell in the edited worksheet (editws) with the corresponding cell in the reference worksheet (refws).
• Comparison Logic: Compares cell values and highlights differences in yellow using cell.Interior.Color.

Make sure to replace "ReferenceWorkbook.xlsx" with the actual name of your reference workbook in refFname. Adjust the worksheet index (Worksheets(1)) if your workbooks have more than one worksheet and you need to compare a specific one.

This should help you get started with comparing the edited set of Excel files against the reference set and highlighting the updates. Adjust the code further based on your specific requirements and file structures as needed. The text, steps and code were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.