Loop through Excel files and compare files not working
I have a process that exports two sets of identical Excel files – a first set where users can edit data and a second reference set that enables file compare to identify updates made in the first set. Each file has only one sheet and the file name is the sheet name plus extension.
I need a macro to loop through both sets of files and highlight the updates in the first (edited) set, but I cannot get the loop through the set of edited files to work. I have tried both While Wend and For Each Next. My code runs, but does nothing.
Help to identify my error greatly appreciated!
Sub Compare_Spreadsheets_While_Wend3()
Dim editPath, refPath, filename, refFname As String
Dim editwbk, refwbk As Workbook
Dim editws, refws As Worksheet
Dim wbk, FileSystem, Folder, editFile, refFile, FSO, editFolder As Object
editPath = “C:pathSet_1”
refPath = “C:pathSet_2”
‘Loop through files in reference folder
refFile = refPath & “*.xls*”
While refFile <> “”
refFname = ActiveWorkbook.Name
Set FileSystem = CreateObject(“Scripting.FileSystemObject”)
Set editFolder = FileSystem.GetFolder(editPath)
For Each editFile In editFolder.Files
filename = editFile.Name
‘Problem here
If editFile.Name = refFname Then
Set editwbk = Workbooks.Open(editPath & “” & filename)
For Each editws In editwbk.Worksheets
If editws.Name = refws.Name Then
For Each cell In editws.Range(“A1”).CurrentRegion
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
Exit For
End If
Next editws
Exit For
editwbk.Close SaveChanges:=True
End If
Next editFile
Wend
End Sub
I have a process that exports two sets of identical Excel files – a first set where users can edit data and a second reference set that enables file compare to identify updates made in the first set. Each file has only one sheet and the file name is the sheet name plus extension.I need a macro to loop through both sets of files and highlight the updates in the first (edited) set, but I cannot get the loop through the set of edited files to work. I have tried both While Wend and For Each Next. My code runs, but does nothing.Help to identify my error greatly appreciated!Sub Compare_Spreadsheets_While_Wend3()
Dim editPath, refPath, filename, refFname As String
Dim editwbk, refwbk As Workbook
Dim editws, refws As Worksheet
Dim wbk, FileSystem, Folder, editFile, refFile, FSO, editFolder As Object
editPath = “C:pathSet_1”
refPath = “C:pathSet_2”
‘Loop through files in reference folder
refFile = refPath & “*.xls*”
While refFile <> “”
refFname = ActiveWorkbook.Name
Set FileSystem = CreateObject(“Scripting.FileSystemObject”)
Set editFolder = FileSystem.GetFolder(editPath)
For Each editFile In editFolder.Files
filename = editFile.Name
‘Problem here
If editFile.Name = refFname Then
Set editwbk = Workbooks.Open(editPath & “” & filename)
For Each editws In editwbk.Worksheets
If editws.Name = refws.Name Then
For Each cell In editws.Range(“A1”).CurrentRegion
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
Exit For
End If
Next editws
Exit For
editwbk.Close SaveChanges:=True
End If
Next editFile
Wend
End Sub Read More