@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.
- 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.
- 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.
- Opening Workbooks: When opening workbooks, make sure to handle errors properly, especially if a workbook fails to open for any reason.
- 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.
