VBA script to compare latest two files and get output to another file

Hi,

I want to compare latest two files and get output to another file to different folder, here is an exmple...

Folder1 has lot of files with different names with timestamps...

Indvalue_06_14_2013_1944.txt
Indvalue_06_15_2013_1944.txt
Indvalue_06_16_2013_1944.txt
Indvalue_06_17_2013_1144.txt
Indvalue_06_17_2013_1944.txt
Indvalue_06_17_2013_2044.txt
GrtValue_06_14_2013_1944.txt
GrtValue_06_15_2013_1944.txt
GrtValue_06_16_2013_1944.txt
GrtValue_06_17_2013_1944.txt
GrtValue_06_17_2013_2044.txt
NRValue_06_15_2013_1944.txt
NRValue_06_16_2013_1944.txt

Now, i have to take latest two files (which starts with "IndValue") and compare the latest two files and get output file to another folder.

Expected output folder 2:

Indvalue_06_17_2013_1944.txt
Indvalue_06_17_2013_2044.txt - these files should compare now...

Compare the above 2 files and get output in to Folder3.


Indvalue_06_17_2013_1944.txt contains like below...

1|Jan|Ind|Accont1|1000.34
2|Jan|Ind|Accont2|1000.34
3|Jan|Ind|Accont3|1000.34


Indvalue_06_17_2013_2044.txt contains like below...

1|Jan|Ind|Accont|1000.34
2|Jan|Ind|Accont2|1000.34
3|Jan|Ind|Accont3|1000.34
4|Jan|Ind|Accont4|1000.34
5|Jan|Ind|Accont5|100
6|Jan|Ind|Accont6|14560.34
7|Jan|Ind|Accont6

Comparision output should be like below...(compare file1 with file2, wtever extra records in file2 should get result in 3rd file)


4|Jan|Ind|Accont4|1000.34
5|Jan|Ind|Accont5|100
6|Jan|Ind|Accont6|14560.34
7|Jan|Ind|Accont6

Please help on this to write in VB.

can anyone please help on this?

Any one help on this issue pls?

I have the same problem. I found a few VBAs online but none of them highlights all the changes. Let me know if any of them helps you move fwd. I haven't been able to make much out them..

' //
' // Start Code
' //
Sub Compare_Sheets()

Set From_WS = Workbooks( "Book1").Worksheets("Sheet1")
Set To_WS = Workbooks("Book2").Worksheets("Sheet2")

Total_Rows = From_WS. Cells(1, 1). CurrentRegion. Rows. Count
Total_Columns = To_WS. Cells(1, 1). CurrentRegion. Columns. Count

For Rows_Counter = 1 To Total_Rows
For Column_Counter = 1 To Total_Columns
If Trim( LCase( From_WS. Cells( Rows_Counter, Column_Counter). Value)) <> _
Trim( LCase( To_WS. Cells( Rows_Counter, Column_Counter). Value)) Then
From_WS.Cells( Rows_Counter, Column_Counter). Interior. ColorIndex = 4
To_WS.Cells( Rows_Counter, Column_Counter). Interior.ColorIndex = 5
End If
Next Column_Counter
Next Rows_Counter
End Sub
' //
' // End Code
' //

This one is for comparing two sheets but it should also be able to compare two documents
Sub Compare2WorkSheets(ws1 As Worksheet, ws2 As Worksheet)
Dim ws1row As Long, ws2row As Long, ws1col As Integer, ws2col As Integer
Dim maxrow As Long, maxcol As Integer, colval1 As String, colval2 As String
Dim report As Workbook, difference As Long
Dim row As Long, col As Integer
Set report = Workbooks.Add
With ws1.UsedRange
ws1row = .Rows.Count
ws1col = .Columns.Count
End With
With ws2.UsedRange
ws2row = .Rows.Count
ws2col = .Columns.Count
End With
maxrow = ws1row
maxcol = ws1col
If maxrow < ws2row Then maxrow = ws2row
If maxcol < ws2col Then maxcol = ws2col
difference = 0
For col = 1 To maxcol
For row = 1 To maxrow
colval1 = ""
colval2 = ""
colval1 = ws1.Cells(row, col).Formula
colval2 = ws2.Cells(row, col).Formula
If colval1 <> colval2 Then
difference = difference + 1
Cells(row, col).Formula = colval1 & "<> " & colval2
Cells(row, col).Interior.Color = 255
Cells(row, col).Font.ColorIndex = 2
Cells(row, col).Font.Bold = True
End If
Next row
Next col
Columns("A:B").ColumnWidth = 25
report.Saved = True
If difference = 0 Then
report.Close False
End If
Set report = Nothing
MsgBox difference & " cells contain different data! ", vbInformation, "Comparing Two Worksheets"
End Sub

Private Sub CommandButton1_Click()
'Compare2WorkSheets Worksheets("Sheet1"), Worksheets("Sheet2")
Set myWorkbook1 = Workbooks.Open("C:\familycomputerclub-website\Excel2007\testcompare2.xlsx")
Compare2WorkSheets Workbooks("testcompare1.xlsm").Worksheets("Sheet1"), myWorkbook1.Worksheets("Sheet1")
End Sub

Also, the VBA gives output in another sheet