Comparing excel files in VB code

Discussion in 'Visual Basic ( VB )' started by Nagesh.Anand, Jun 4, 2010.

  1. Nagesh.Anand

    Nagesh.Anand New Member

    Jun 4, 2010
    Likes Received:
    Trophy Points:
    Hi Forum,

    I need a code in VB to compare 2 excel files.

    Requirement: There will be 2 files, Base file and Actual File.
    Code should compare Actual file with Base file and if there is descriprncy it should highlight in red colour in Actual file and it shoul put comment for that cell in Actual file. Comment should contain corresponding value in Base file.

    Please help me.. This is first assignment in company.

  2. parveen21

    parveen21 New Member

    May 28, 2010
    Likes Received:
    Trophy Points:
    Home Page:
    I have written code that takes two Excel files and compares them.
    Anything in the second Excel File that does not match the exact same cell from the first Excel File gets a cross-hatch pattern to signify that something has changed.
    This file is then saved with a new name so that the original two files are left un-altered.

    Issue: If you insert a row/column, that row/column and everything after it will be "different."
    The driver for this project is an Excel File that has 26 (or more) worksheets - each one can have a completely different layout. Thus, I cannot simply look in one column for an "identifier" to see if it has changed and if so, find it on the page. This Excel file is sent to another person, updated, and sent back.

    Anyone have another idea of how I can do this?
    (Note: I am using GemBox.Spreadsheet to access Excel as it is much faster than using Excel Com Object - thus the code...)

    Some thoughts:
    Before sending the initial Excel file, could I run it through a program to "tag" each cell? Perhaps giving it a name/range or something else? Ideally this would be something not easily visible/changed by the other person (thus not using the comments).

    Dim ef As New ExcelFile 
                    Dim ef2 As New ExcelFile 
                    Dim ws As ExcelWorksheet = ef.Worksheets(0) 
                    Dim ws2 As ExcelWorksheet = ef2.Worksheets(0) 
                    Dim sheetCount As Integer = 0 
                    For Each sheet In ef.Worksheets 
                            ws2 = ef2.Worksheets(sheetCount) 
                            Dim rowCount As Integer = 0 
                            For Each row In sheet.Rows 
                                    Dim columnCount As Integer = 0 
                                    For Each cell In row.AllocatedCells 
                                            Dim wsCell, ws2Cell As String 
                                            wsCell = cell.value 
                                            ws2Cell = ws2.Cells(rowCount, columnCount).Value 
                                            If wsCell <> ws2Cell Then 
                                                    ws2.Cells(rowCount, columnCount).Style.FillPattern.SetPattern(FillPatternStyle.ThinHorizontalCrosshatch, Color.Green, Color.Yellow) 
                                            End If 
                                            columnCount += 1 
                                    rowCount += 1 
                            sheetCount += 1 
    Last edited by a moderator: Jun 9, 2010

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice