Compare two spreadsheet
On Sep 9, 10:44 am, Joel wrote:
Try this code. Change MyFolder as required. I used sheet1 and sheet2 as
sheet names. Change as needed.
Sub updateteam()
Const MyFolder = "c:\temp\team.log"
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fs, f, ts, s
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CreateTextFile MyFolder 'Create a file
Set f = fs.GetFile(MyFolder)
Set ts = f.OpenAsTextStream(ForWriting, TristateUseDefault)
With Sheets("Sheet1")
.Activate
LastRowSh1 = Cells(Rows.Count, "B").End(xlUp).Row
Set TeamRange = .Range(.Cells(2, "B"), _
.Cells(LastRowSh1, "B"))
Sheets("Sheet2").Activate
LastRowSh2 = Cells(Rows.Count, "B").End(xlUp).Row
For RowCount = 2 To LastRowSh2
Set c = TeamRange.Find( _
what:=Cells(RowCount, "B"), _
LookIn:=xlValues)
If Not c Is Nothing Then
'compare fund
If Cells(RowCount, "C") < _
.Cells(RowCount, "C") Then
msg = Cells(RowCount, "B") & ": " & _
"Changed Fund From: " & _
Trim(Str(.Cells(RowCount, "C"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "C")))
ts.writeline msg
Cells(RowCount, "C") = _
.Cells(RowCount, "C")
End If
'compare jan
If Cells(RowCount, "D") < _
.Cells(RowCount, "D") Then
msg = Cells(RowCount, "B") & ": " & _
"Changed Jan From: " & _
Trim(Str(.Cells(RowCount, "D"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "D")))
ts.writeline msg
Cells(RowCount, "D") = _
.Cells(RowCount, "D")
End If
'compare feb
If Cells(RowCount, "E") < _
.Cells(RowCount, "E") Then
msg = Cells(RowCount, "B") & ": " & _
"Changed February From: " & _
Trim(Str(.Cells(RowCount, "E"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "E")))
ts.writeline msg
Cells(RowCount, "E") = _
.Cells(RowCount, "E")
End If
'compare march
If Cells(RowCount, "F") < _
.Cells(RowCount, "F") Then
msg = Cells(RowCount, "B") & ": " & _
"Changed March From: " & _
Trim(Str(.Cells(RowCount, "F"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "F")))
ts.writeline msg
Cells(RowCount, "F") = _
.Cells(RowCount, "F")
End If
'compare april
If Cells(RowCount, "G") < _
.Cells(RowCount, "G") Then
msg = Cells(RowCount, "B") & ": " & _
"Changed April From: " & _
Trim(Str(.Cells(RowCount, "G"))) & _
" To: " & _
Trim(Str(Cells(RowCount, "G")))
ts.writeline msg
Cells(RowCount, "G") = _
.Cells(RowCount, "G")
End If
Else
msg = "Did not find team " & Cells(RowCount, "B")
ts.writeline msg
MsgBox (msg)
End If
Next RowCount
End With
ts.Close
End Sub
" wrote:
Hello every one ,
Am a new bee to Ms excel. Am trying to find way to
compare rows in two different spread sheets lets say spread A and
Spread sheet B . I like to compare row with a key for example taking
teamnumber as Key in two sheets and update data under Jan Feb march
april in spread sheet B and put comments in a file on what was updated
with time stamp.
Spread sheet A.
Id teamname funds jan feb march april
3 greatteam 10 1 2 4 3
Spread Sheet B
Location teamname funds jan feb march april
Dallas greatteam 11 .5 1 2 1
Out put should look like
---------------------------------------------
Spread sheet B
Location teamname funds jan feb march april
Dallas greatteam 10 1 2 4 3
and it would be great if comments or any other form of log file is
creasted with what all are updated. Appreciate the help/input
thanks- Hide quoted text -
- Show quoted text -
Thanks Joel ,
This might be a new bee question i get error message
at fs.createtextfile myfolder in
fs.CreateTextFile MyFolder 'Create a file
Set f = fs.GetFile(MyFolder)
Set ts = f.OpenAsTextStream(ForWriting, TristateUseDefault)
routine. Inital guess was may be i should change privilalges on my
folder ...and try that ...that didnt really help should i change any
privillages ?
Appreciate it
|