none
How to compare two worksheet cells and find the difference in color RRS feed

  • Question

  • I have two workbook.

    Each workbook will have different sheets.

    When both workbook sheets are the same, then it should check every individual cell of both the sheet.

    let us imagine, both the sheet start range A20:KJ20

    Sheet1 ==> A21 cell value is "12"

    Sheet2 ==> A21 cell value is "14".

    the above value is different, then it should show the difference in various color in "Sheet1"

    I have done some code below.

    But, worksheet not showing the range.

                                Dim varSheetCurr As Object
                                Dim varSheetPrev As Object
                                Dim strRangeToCheck As String
                                Dim iRow As Long
                                Dim iCol As Long
    
                                strRangeToCheck = "A21:IV65536"
                                ' If you know the data will only be in a smaller range, reduce the size of the ranges above.
                                Debug.Print(Now)
    
                              
                                varSheetCurr = Worksheets("Sheet2").Range(strRangeToCheck) 
                                varSheetPrev = Worksheets("Sheet2").Range(strRangeToCheck) ' or whatever your other sheet is.
                                Debug.Print(Now)
    
                                For iRow = LBound(varSheetCurr, 1) To UBound(varSheetCurr, 1)
                                    For iCol = LBound(varSheetCurr, 2) To UBound(varSheetCurr, 2)
                                        If varSheetCurr(iRow, iCol) = varSheetPrev(iRow, iCol) Then
                                            ' Cells are identical.
                                            ' Do nothing.
                                        Else
                                            ' Cells are different.
                                            ' Code goes here for whatever it is you want to do.
                                            Worksheets("Sheet1").Cells(iRow, iCol).Interior.Color = 255
                                        End If
                                    Next iCol
                                Next iRow

    What is the problem in the above code...?

    Friday, December 28, 2018 9:18 AM

Answers

  • Here is a very simple example of accessing WorkSheet in WorkSheets without addressing the Range.

    Option Strict On
    Option Infer Off
    
    Imports System.Runtime.InteropServices
    Imports Excel = Microsoft.Office.Interop.Excel
    
    Public Module OpenWorkBookSimple
        Public Sub OpenExcelSimple(ByVal FileName As String, ByVal SheetName As String)
    
            If IO.File.Exists(FileName) Then
    
                Dim Proceed As Boolean = False
    
                Dim xlApp As Excel.Application = Nothing
                Dim xlWorkBooks As Excel.Workbooks = Nothing
                Dim xlWorkBook As Excel.Workbook = Nothing
                Dim xlWorkSheet As Excel.Worksheet = Nothing
                Dim xlWorkSheets As Excel.Sheets = Nothing
                Dim xlCells As Excel.Range = Nothing
    
                xlApp = New Excel.Application
                xlApp.DisplayAlerts = False
                xlWorkBooks = xlApp.Workbooks
                xlWorkBook = xlWorkBooks.Open(FileName)
    
                xlApp.Visible = False
                xlWorkSheets = xlWorkBook.Sheets
    
                '
                ' For/Next finds our sheet
                '
                For x As Integer = 1 To xlWorkSheets.Count
                    xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
    
                    If xlWorkSheet.Name = SheetName Then
                        Proceed = True
                        Exit For
                    End If
    
                    Marshal.FinalReleaseComObject(xlWorkSheet)
                    xlWorkSheet = Nothing
    
                Next
                If Proceed Then
                    Dim sb As New Text.StringBuilder
    
                    Dim Cells As String() = {"A1", "B2", "B3", "B4"}
                    For Each cell As String In Cells
                        Try
                            xlCells = xlWorkSheet.Range(cell)
                            sb.AppendLine(String.Format("{0} = '{1}'", cell, xlCells.Value))
                        Catch ex As Exception
                            ReleaseExcelObject(xlCells)
                        End Try
                    Next
                    ' show sb.ToString()
                Else
                    ' sheet not found
                End If
    
                xlWorkBook.Close()
                xlApp.UserControl = True
                xlApp.Quit()
    
                ReleaseExcelObject(xlCells)
                ReleaseExcelObject(xlWorkSheets)
                ReleaseExcelObject(xlWorkSheet)
                ReleaseExcelObject(xlWorkBook)
                ReleaseExcelObject(xlWorkBooks)
                ReleaseExcelObject(xlApp)
            Else
                ' file does not exists
            End If
        End Sub
        Private Sub ReleaseExcelObject(ByVal excelObject As Object)
            Try
                If excelObject IsNot Nothing Then
                    Marshal.ReleaseComObject(excelObject)
                    excelObject = Nothing
                End If
            Catch ex As Exception
                excelObject = Nothing
            End Try
        End Sub
    End Module
    

    Example for working with a Range.

    Option Strict On
    Option Infer On
    
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports Microsoft.Office
    Imports System.Runtime.InteropServices
    Module ExcelDemoIteratingData
        ''' <summary>
        ''' Place all rows/columns into a DataTable.
        ''' Must know ahead of time how many columns there are which is not
        ''' shown here but instead I knew when creating this demo there are x rows
        ''' and x columns.
        ''' </summary>
        ''' <param name="FileName"></param>
        ''' <param name="SheetName"></param>
        ''' <remarks></remarks>
        Public Function OpenExcel_1(ByVal FileName As String, ByVal SheetName As String) As DataTable
            Dim dt As New DataTable
    
            If IO.File.Exists(FileName) Then
    
                Dim Proceed As Boolean = False
                Dim xlApp As Excel.Application = Nothing
                Dim xlWorkBooks As Excel.Workbooks = Nothing
                Dim xlWorkBook As Excel.Workbook = Nothing
                Dim xlWorkSheet As Excel.Worksheet = Nothing
                Dim xlWorkSheets As Excel.Sheets = Nothing
                Dim xlCells As Excel.Range = Nothing
    
                xlApp = New Excel.Application
                xlApp.DisplayAlerts = False
                xlWorkBooks = xlApp.Workbooks
                xlWorkBook = xlWorkBooks.Open(FileName)
    
                xlApp.Visible = False
    
                xlWorkSheets = xlWorkBook.Sheets
    
                '
                ' For/Next finds our sheet
                '
                For x As Integer = 1 To xlWorkSheets.Count
                    xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
    
                    If xlWorkSheet.Name = SheetName Then
                        Proceed = True
                        Exit For
                    End If
    
                    Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
                    xlWorkSheet = Nothing
    
                Next
                If Proceed Then
    
                    dt.Columns.AddRange(
                        New DataColumn() _
                        {
                            New DataColumn With {.ColumnName = "FirstName"},
                            New DataColumn With {.ColumnName = "LastName"},
                            New DataColumn With {.ColumnName = "Age", .DataType = GetType(Integer)}
                        }
                    )
    
                    Dim xlUsedRange = xlWorkSheet.UsedRange
    
                    Try
    
                        Dim ExcelArray(,) As Object = CType(xlUsedRange.Value(Excel.XlRangeValueDataType.xlRangeValueDefault), Object(,))
    
                        If ExcelArray IsNot Nothing Then
                            Console.WriteLine("Length: {0}", ExcelArray.Length)
    
                            ' Get bounds of the array.
                            Dim bound0 As Integer = ExcelArray.GetUpperBound(0)
                            Dim bound1 As Integer = ExcelArray.GetUpperBound(1)
    
                            Console.WriteLine("Dimension 0: {0}", bound0)
                            Console.WriteLine("Dimension 1: {0}", bound1)
    
                            ' Loop over all elements.
    
                            For j As Integer = 1 To bound0
    
                                If (ExcelArray(j, 1) IsNot Nothing) AndAlso (ExcelArray(j, 2) IsNot Nothing) Then
                                    dt.Rows.Add(New Object() _
                                        {
                                            ExcelArray(j, 1),
                                            ExcelArray(j, 2),
                                            ExcelArray(j, 3)
                                        }
                                    )
                                End If
                            Next
    
                        End If
                    Finally
                        ReleaseComObject(xlUsedRange)
                    End Try
    
                Else
                    MessageBox.Show(SheetName & " not found.")
                End If
    
                xlWorkBook.Close()
                xlApp.UserControl = True
                xlApp.Quit()
    
                ReleaseComObject(xlCells)
                ReleaseComObject(xlWorkSheets)
                ReleaseComObject(xlWorkSheet)
                ReleaseComObject(xlWorkBook)
                ReleaseComObject(xlWorkBooks)
                ReleaseComObject(xlApp)
            Else
                MessageBox.Show("'" & FileName & "' not located. Try one of the write examples first.")
            End If
    
            Return dt
    
        End Function
        Private Sub ReleaseComObject(ByVal sender As Object)
            Try
                If sender IsNot Nothing Then
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(sender)
                    sender = Nothing
                End If
            Catch ex As Exception
                sender = Nothing
            End Try
        End Sub
    End Module
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Gani tpt Thursday, January 3, 2019 4:19 AM
    Friday, December 28, 2018 12:06 PM
    Moderator

All replies

  • Hello,

    Take a look at the following code samples from Microsoft.

    https://code.msdn.microsoft.com/office/How-to-compare-cells-in-c3cb29d1


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, December 28, 2018 10:39 AM
    Moderator
  • my complete source code.

     Private Sub btnOK_Click(sender As Object, e As EventArgs) Handles btnOK.Click
            Dim wb1 As Workbook = New Workbook(_CurRevName)
            Dim wb2 As Workbook = New Workbook(_PreRevName)
    
    
            For Each wsht1 As Worksheet In wb1.Worksheets
                If wsht1.Name.StartsWith("TEM") And Not wsht1.Name.StartsWith("TE-RJ") Then
    
                    For Each wsht2 As Worksheet In wb2.Worksheets
                        If wsht2.Name.StartsWith("TEM") And Not wsht2.Name.StartsWith("TE-RJ") Then
                            If wsht1.Name = wsht2.Name Then
                                ' For each matching sheets in both of the documents, compare all the lines available in that particular sheet with respect to the previous & 
                                ' current revision of SLD
                                Dim varSheetCurr As Object
                                Dim varSheetPrev As Object
                                Dim strRangeToCheck As String
                                Dim iRow As Long
                                Dim iCol As Long
    
                                strRangeToCheck = "A21:IV65536"
                                ' If you know the data will only be in a smaller range, reduce the size of the ranges above.
                                Debug.Print(Now)
    
                                varSheetCurr = wb2.Worksheets()
                                varSheetPrev = Worksheets("Sheet2").Range(strRangeToCheck) ' or whatever your other sheet is.
                                Debug.Print(Now)
    
                                For iRow = LBound(varSheetCurr, 1) To UBound(varSheetCurr, 1)
                                    For iCol = LBound(varSheetCurr, 2) To UBound(varSheetCurr, 2)
                                        If varSheetCurr(iRow, iCol) = varSheetPrev(iRow, iCol) Then
                                            ' Cells are identical.
                                            ' Do nothing.
                                        Else
                                            ' Cells are different.
                                            ' Code goes here for whatever it is you want to do.
                                            Worksheets("Sheet1").Cells(iRow, iCol).Interior.Color = 255
                                        End If
                                    Next iCol
                                Next iRow
    
                            Else
    
                            End If
    
                        End If
                    Next
    
                End If
            Next
        End Sub

    Friday, December 28, 2018 10:46 AM
  • Thanks for your help.

    It is .net color.

    i am getting error in below line.

     varSheetPrev = Worksheets("Sheet2").Range(strRangeToCheck) ' or whatever your other sheet is.
                                Debug.Print(Now)

    Error : "Worksheets" is a type and cannot be used as an expression.

    I suppose to call "Sheet2" of workbook1 ===> 

    varSheetCurr = wb2.Worksheets() // This is not correct approach i hope so.

     varSheetCurr = wb2.Worksheets() // Suppose to call "Sheet2" of workbook1
     varSheetPrev = Worksheets("Sheet2").Range(strRangeToCheck)  // Suppose to call "Sheet2" of workbook2


    • Edited by Gani tpt Friday, December 28, 2018 11:38 AM Error mentioned
    Friday, December 28, 2018 11:35 AM
  • Here is a very simple example of accessing WorkSheet in WorkSheets without addressing the Range.

    Option Strict On
    Option Infer Off
    
    Imports System.Runtime.InteropServices
    Imports Excel = Microsoft.Office.Interop.Excel
    
    Public Module OpenWorkBookSimple
        Public Sub OpenExcelSimple(ByVal FileName As String, ByVal SheetName As String)
    
            If IO.File.Exists(FileName) Then
    
                Dim Proceed As Boolean = False
    
                Dim xlApp As Excel.Application = Nothing
                Dim xlWorkBooks As Excel.Workbooks = Nothing
                Dim xlWorkBook As Excel.Workbook = Nothing
                Dim xlWorkSheet As Excel.Worksheet = Nothing
                Dim xlWorkSheets As Excel.Sheets = Nothing
                Dim xlCells As Excel.Range = Nothing
    
                xlApp = New Excel.Application
                xlApp.DisplayAlerts = False
                xlWorkBooks = xlApp.Workbooks
                xlWorkBook = xlWorkBooks.Open(FileName)
    
                xlApp.Visible = False
                xlWorkSheets = xlWorkBook.Sheets
    
                '
                ' For/Next finds our sheet
                '
                For x As Integer = 1 To xlWorkSheets.Count
                    xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
    
                    If xlWorkSheet.Name = SheetName Then
                        Proceed = True
                        Exit For
                    End If
    
                    Marshal.FinalReleaseComObject(xlWorkSheet)
                    xlWorkSheet = Nothing
    
                Next
                If Proceed Then
                    Dim sb As New Text.StringBuilder
    
                    Dim Cells As String() = {"A1", "B2", "B3", "B4"}
                    For Each cell As String In Cells
                        Try
                            xlCells = xlWorkSheet.Range(cell)
                            sb.AppendLine(String.Format("{0} = '{1}'", cell, xlCells.Value))
                        Catch ex As Exception
                            ReleaseExcelObject(xlCells)
                        End Try
                    Next
                    ' show sb.ToString()
                Else
                    ' sheet not found
                End If
    
                xlWorkBook.Close()
                xlApp.UserControl = True
                xlApp.Quit()
    
                ReleaseExcelObject(xlCells)
                ReleaseExcelObject(xlWorkSheets)
                ReleaseExcelObject(xlWorkSheet)
                ReleaseExcelObject(xlWorkBook)
                ReleaseExcelObject(xlWorkBooks)
                ReleaseExcelObject(xlApp)
            Else
                ' file does not exists
            End If
        End Sub
        Private Sub ReleaseExcelObject(ByVal excelObject As Object)
            Try
                If excelObject IsNot Nothing Then
                    Marshal.ReleaseComObject(excelObject)
                    excelObject = Nothing
                End If
            Catch ex As Exception
                excelObject = Nothing
            End Try
        End Sub
    End Module
    

    Example for working with a Range.

    Option Strict On
    Option Infer On
    
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports Microsoft.Office
    Imports System.Runtime.InteropServices
    Module ExcelDemoIteratingData
        ''' <summary>
        ''' Place all rows/columns into a DataTable.
        ''' Must know ahead of time how many columns there are which is not
        ''' shown here but instead I knew when creating this demo there are x rows
        ''' and x columns.
        ''' </summary>
        ''' <param name="FileName"></param>
        ''' <param name="SheetName"></param>
        ''' <remarks></remarks>
        Public Function OpenExcel_1(ByVal FileName As String, ByVal SheetName As String) As DataTable
            Dim dt As New DataTable
    
            If IO.File.Exists(FileName) Then
    
                Dim Proceed As Boolean = False
                Dim xlApp As Excel.Application = Nothing
                Dim xlWorkBooks As Excel.Workbooks = Nothing
                Dim xlWorkBook As Excel.Workbook = Nothing
                Dim xlWorkSheet As Excel.Worksheet = Nothing
                Dim xlWorkSheets As Excel.Sheets = Nothing
                Dim xlCells As Excel.Range = Nothing
    
                xlApp = New Excel.Application
                xlApp.DisplayAlerts = False
                xlWorkBooks = xlApp.Workbooks
                xlWorkBook = xlWorkBooks.Open(FileName)
    
                xlApp.Visible = False
    
                xlWorkSheets = xlWorkBook.Sheets
    
                '
                ' For/Next finds our sheet
                '
                For x As Integer = 1 To xlWorkSheets.Count
                    xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
    
                    If xlWorkSheet.Name = SheetName Then
                        Proceed = True
                        Exit For
                    End If
    
                    Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
                    xlWorkSheet = Nothing
    
                Next
                If Proceed Then
    
                    dt.Columns.AddRange(
                        New DataColumn() _
                        {
                            New DataColumn With {.ColumnName = "FirstName"},
                            New DataColumn With {.ColumnName = "LastName"},
                            New DataColumn With {.ColumnName = "Age", .DataType = GetType(Integer)}
                        }
                    )
    
                    Dim xlUsedRange = xlWorkSheet.UsedRange
    
                    Try
    
                        Dim ExcelArray(,) As Object = CType(xlUsedRange.Value(Excel.XlRangeValueDataType.xlRangeValueDefault), Object(,))
    
                        If ExcelArray IsNot Nothing Then
                            Console.WriteLine("Length: {0}", ExcelArray.Length)
    
                            ' Get bounds of the array.
                            Dim bound0 As Integer = ExcelArray.GetUpperBound(0)
                            Dim bound1 As Integer = ExcelArray.GetUpperBound(1)
    
                            Console.WriteLine("Dimension 0: {0}", bound0)
                            Console.WriteLine("Dimension 1: {0}", bound1)
    
                            ' Loop over all elements.
    
                            For j As Integer = 1 To bound0
    
                                If (ExcelArray(j, 1) IsNot Nothing) AndAlso (ExcelArray(j, 2) IsNot Nothing) Then
                                    dt.Rows.Add(New Object() _
                                        {
                                            ExcelArray(j, 1),
                                            ExcelArray(j, 2),
                                            ExcelArray(j, 3)
                                        }
                                    )
                                End If
                            Next
    
                        End If
                    Finally
                        ReleaseComObject(xlUsedRange)
                    End Try
    
                Else
                    MessageBox.Show(SheetName & " not found.")
                End If
    
                xlWorkBook.Close()
                xlApp.UserControl = True
                xlApp.Quit()
    
                ReleaseComObject(xlCells)
                ReleaseComObject(xlWorkSheets)
                ReleaseComObject(xlWorkSheet)
                ReleaseComObject(xlWorkBook)
                ReleaseComObject(xlWorkBooks)
                ReleaseComObject(xlApp)
            Else
                MessageBox.Show("'" & FileName & "' not located. Try one of the write examples first.")
            End If
    
            Return dt
    
        End Function
        Private Sub ReleaseComObject(ByVal sender As Object)
            Try
                If sender IsNot Nothing Then
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(sender)
                    sender = Nothing
                End If
            Catch ex As Exception
                sender = Nothing
            End Try
        End Sub
    End Module
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Gani tpt Thursday, January 3, 2019 4:19 AM
    Friday, December 28, 2018 12:06 PM
    Moderator
  • Still i am getting Error. 

    I have attached screenshot for your reference.

    I am using below reference.

    Imports System.IO
    Imports Microsoft.Office.Interop.Excel

    Note : We have more than 400 columns every sheet to compare with cell.

    • Edited by Gani tpt Friday, December 28, 2018 1:29 PM multiple columns
    Friday, December 28, 2018 1:13 PM
  • Try using this pattern.

    Dim xl As New Microsoft.Office.Interop.Excel.Application()
    Dim workbook As Microsoft.Office.Interop.Excel.Workbook = xl.Workbooks.Open("C:\test.xlsx")
    Dim sheet As Microsoft.Office.Interop.Excel.Worksheet = workbook.Sheets(1)


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, December 28, 2018 1:45 PM
    Moderator
  • Thanks.. it's working....
    Thursday, January 3, 2019 4:20 AM