Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2004

    Unanswered: compare 2 excel files

    currently we have a file which is generated by macros(stored in a separate file) geting information from some text files. the procedure with which text files are aquired have changed and so I modified the macros and now hope that the end-result (new excel file) is identical to the old one generated from old text files. Visually I cant tell any difference (i.e. certain control values match) but I am still dubious and as the files are rather large I would really hate to manually compare them. Is there any pre-writen function for comparing two files (or worksheets) or at least could you suggest how would I go about writing a program that would do that?

  2. #2
    Join Date
    Jan 2004
    Aberdeen, Scotland
    Its far easier to do something like this in access if its just for quality checks and will only be used a few times,
    Export thee files into access and run the wizard to find unmatched records,

    Or if it has to be done in excel go about it like this

    Select a unique field in one of your worksheets pick each value in turn and find it in the second worksheet and check to make sure each value is identical in all the cells of that row,

    Heres a bit of code i quickly through together

    Sub Test()
        Dim wks1 As Worksheet, wks2 As Worksheet
        Dim Lastcell As Range, CompareCell As Range
        Dim MyCheck As String
        Dim I As Integer, J As Integer
        Set wks1 = Worksheets("Sheet1"): Set wks2 = Worksheets("Sheet2")
        Set Lastcell = Cells.Find("*", , , , , xlPrevious)
        If Not Lastcell Is Nothing Then
            For I = 1 To Lastcell
                MyCheck = Cells(I, 1).Value
                Set CompareCell = Range(Cells(1, 1), Cells(Lastcell.Row, 1)).Find(MyCheck)
                If Not CompareCell Is Nothing Then
                    For J = 1 To Lastcell.Column
                        If Not wks1.Cells(I, J).Value = wks2.Cells(CompareCell.Row, J).Value Then
                            MsgBox "Sheets not Identical"
                            Exit Sub
                        End If
                    Next J
                     MsgBox "Sheets not Identical"
                    Exit Sub
                End If
            Next I
            MsgBox "Sheets not Identical"
            Exit Sub
        End If
        MsgBox "Sheets Identical"
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts