Solved

How can I load 2 excel files, compare specific columns and change bakcground color where cell values differ?

Posted on 2008-06-19
15
272 Views
Last Modified: 2013-11-26
I am developing a WinForm VB 2005 app in which users can load 2 Excel files (let's call them XLS1 and XLS2). Both XLS are loaded into DataGridViews and them I can compare values, columns, etc.
 But now I was asked to open 2 XLS files, compare specific columns, row by row and then update the 2nd XLS (XLS2) by changing the background color of cell where values have no match in the first XLS (XLS1).

Any ideas?
0
Comment
Question by:vercesi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 3
15 Comments
 
LVL 19

Expert Comment

by:elimesika
ID: 21822243
HI

Here is a macro you can use , you should only change the active_sheet in both sub and set the ranges for the proper columns.
Sub MakeTestData()
Dim active_sheet As Worksheet
 
    Set active_sheet = ActiveSheet
    active_sheet.Names.Add Name:="First", RefersToR1C1:="=R1C1:R746C2"
    active_sheet.Names.Add Name:="Second", RefersToR1C1:="=R1C3:R746C4"
End Sub
 
Sub MarkDifferences()
Dim active_sheet As Worksheet
Dim name1 As String
Dim name2 As String
Dim First As Range
Dim Second As Range
Dim cells1 As Collection
Dim cells2 As Collection
Dim cell1 As Range
Dim cell2 As Range
Dim key As String
Dim no_match As Boolean
 
    Set active_sheet = ActiveSheet
'    name1 = InputBox$("First Range Name:", "First Range", "")
name1 = "First"
    If Len(name1) = 0 Then Exit Sub
    Set First = active_sheet.Range(name1)
 
'    name2 = InputBox$("Second Range Name:", "Second Range", "")
name2 = "Second"
    If Len(name2) = 0 Then Exit Sub
    Set Second = active_sheet.Range(name2)
 
    ' Make normal collections holding the cells.
    Set cells1 = New Collection
    For Each cell1 In First.Cells
        key = cell1.Row - First.Row & "," & cell1.Column - First.Column
        cells1.Add cell1, key
    Next cell1
 
    Set cells2 = New Collection
    For Each cell2 In Second.Cells
        key = cell2.Row - Second.Row & "," & cell2.Column - Second.Column
        cells2.Add cell2, key
    Next cell2
 
    ' Examine the cells in the first collection.
    For Each cell1 In cells1
        On Error Resume Next
        Err.Clear
        key = cell1.Row - First.Row & "," & cell1.Column - First.Column
        Set cell2 = cells2(key)
        If Err.Number <> 0 Then
            ' The second cell is missing.
            no_match = True
        ElseIf cell1.Text <> cell2.Text Then
            ' The cells don't match.
            no_match = True
        Else
            no_match = False
        End If
 
        ' If the cells don't match, color cell1.
        If no_match Then
            With cell1.Interior
                .ColorIndex = 35
                .Pattern = xlSolid
            End With
        Else
            With cell1.Interior
                .ColorIndex = xlNone
            End With
        End If
    Next cell1
 
    ' Examine the cells in the second collection.
    For Each cell2 In cells2
        On Error Resume Next
        Err.Clear
        key = cell2.Row - Second.Row & "," & cell2.Column - Second.Column
        Set cell1 = cells1(key)
        If Err.Number <> 0 Then
            ' The second cell is missing.
            no_match = True
        ElseIf cell2.Text <> cell1.Text Then
            ' The cells don't match.
            no_match = True
        Else
            no_match = False
        End If
 
        ' If the cells don't match, color cell2.
        If no_match Then
            With cell2.Interior
                .ColorIndex = 35
                .Pattern = xlSolid
            End With
        Else
            With cell2.Interior
                .ColorIndex = xlNone
            End With
        End If
    Next cell2
End Sub

Open in new window

0
 
LVL 53

Expert Comment

by:Dhaest
ID: 21822281
Here is already a start.
Private Function comparefiles(ByVal OrigFile As String, ByVal CompareFile As String) As Integer
 
        Dim RetVal As Integer = 0
 
        Dim origBook As Excel.Workbook = m_ExcelApp.Workbooks.Open(OrigFile)
        Dim compareBook As Excel.Workbook = m_ExcelApp.Workbooks.Open(CompareFile)
 
 
        Dim origSheet As Excel.Worksheet = origBook.Sheets.Item(1)
        Dim compareSheet As Excel.Worksheet = compareBook.Sheets.Item(1)
 
 
        'for each column in the work book
        For i As Integer = 1 To MAX_COL
            'for each row in the work book
            For j As Integer = 1 To MAX_ROW
                    'get the values to compare
                    if testSheet.Cells(j, i).Value <> compareSheet.Cells(j, i).Value
 
                    end if
                End If
            Next
        Next
 
        origBook.Close(Savechanges:=False)
        compareBook.Close(Savechanges:=True)
 
    End Function

Open in new window

0
 
LVL 1

Author Comment

by:vercesi
ID: 21823149
I'm trying to use your solution Dhaest. Perhaps I should elaborate a bit more on what I need.
Using your function as a starting point I can load both Excel files (do I need to add any reference such as Microsoft Excel 11.0?), assuming both Excel files have the same structure, column wise.

Having compared each cell, how can I change the background color in cell where values do not match?
I can do this using DataGridViews, but I need to update the 2nd Excel file. Usually I use OleDB to update an Excel file, but this does not allows me to control cell styles, only contents.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:vercesi
ID: 21823205
Sorry. One other thing.

My Excel files have multiple worksheets. In your code, you open the first Worksheet. How can I specify which worksheet to use?
0
 
LVL 19

Expert Comment

by:elimesika
ID: 21823223
HI

Your have also the color handling in my code ...
If no_match Then
            With cell2.Interior
                .ColorIndex = 35
                .Pattern = xlSolid
            End With
        Else
            With cell2.Interior
                .ColorIndex = xlNone
            End With
        End If

Open in new window

0
 
LVL 1

Author Comment

by:vercesi
ID: 21823529
elimesika, you are correct but I do not wish to create a macro. I need my code to run in a VB 2005 app. Can you help me "translate" your code into that?
0
 
LVL 19

Expert Comment

by:elimesika
ID: 21823590
HI , hope that I will have time for that during the weekend (not sure ....)

Good Luck
0
 
LVL 1

Author Comment

by:vercesi
ID: 21823657
Thanks for your time. I'm kind in a hurry and needed that 'till tomorrow... I'm raising points to 250...
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 21823751
I'll take a look at it tomorrow to optimize (and add more comments) of the starting code. I'm leaving work right now and this evening I'll be watching the european footbal championchips ...
0
 
LVL 1

Author Comment

by:vercesi
ID: 21823852
Thank you. I'll be trying to figure it out and if I do, I'll still assign the points since I'm using your code as base.
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 21823971
I'll check on it as soon as I can tomorrow. If you've got problems, just post them together here so I'll try to answer them all at once :)
0
 
LVL 53

Accepted Solution

by:
Dhaest earned 250 total points
ID: 21829084
As promised, here you find a complete working example !

Add a reference to Microsoft.Office.Interop.Excel

You can call it like this:
        comparefiles("c:\temp\origBook.xls", "c:\temp\copybook.xls")

The cells that are different are colored red in the second excel (copybook.xls)
    Private Sub comparefiles(ByVal OrigFile As String, ByVal CompareFile As String)
        Try
            ' If you use another language as english, you'll get an error if you don't change the current culture
            Dim oldCulture As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture
            System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")
 
 
            ' Open the excel-application and both workbooks
            Dim m_ExcelApp As New Microsoft.Office.Interop.Excel.Application
            Dim origBook As Microsoft.Office.Interop.Excel.Workbook = m_ExcelApp.Workbooks.Open(OrigFile)
            Dim compareBook As Microsoft.Office.Interop.Excel.Workbook = m_ExcelApp.Workbooks.Open(CompareFile)
 
            ' Open the sheet that contains the data
            Dim origSheet As Microsoft.Office.Interop.Excel.Worksheet = origBook.Sheets.Item(1)
            Dim compareSheet As Microsoft.Office.Interop.Excel.Worksheet = compareBook.Sheets.Item(1)
 
            'for each column in the work book - COMPARE VALUES
            For i As Integer = 1 To origSheet.Range("A1").SpecialCells(11).Column
                'for each row in the work book
                For j As Integer = 1 To origSheet.Range("A1").SpecialCells(11).Row
                    'get the values to compare
                    If origSheet.Cells(j, i).Value <> compareSheet.Cells(j, i).Value Then
                        'compareSheet.Cells(j, i) = "X"
                        compareSheet.Range(compareSheet.Cells(j, i), compareSheet.Cells(j, i)).Interior.ColorIndex = 3
                    End If
 
                Next
            Next
 
            ' If there are more rows in the new file
            If origSheet.Range("A1").SpecialCells(11).Row < compareSheet.Range("A1").SpecialCells(11).Row Then
                For i As Integer = origSheet.Range("A1").SpecialCells(11).Row + 1 To compareSheet.Range("A1").SpecialCells(11).Row
                    'for each row in the work book
                    For j As Integer = 1 To origSheet.Range("A1").SpecialCells(11).Column
                        compareSheet.Range(compareSheet.Cells(i, j), compareSheet.Cells(i, j)).Interior.ColorIndex = 3
                    Next
                Next
            End If
            ' If there are more columns in the new file
            If origSheet.Range("A1").SpecialCells(11).Column < compareSheet.Range("A1").SpecialCells(11).Column Then
                For i As Integer = origSheet.Range("A1").SpecialCells(11).Column + 1 To compareSheet.Range("A1").SpecialCells(11).Column
                    'for each row in the work book
                    For j As Integer = 1 To origSheet.Range("A1").SpecialCells(11).Column
                        compareSheet.Range(compareSheet.Cells(j, i), compareSheet.Cells(j, i)).Interior.ColorIndex = 3
                    Next
                Next
            End If
 
            ' Close the workbooks, save the changes !
            origBook.Close(SaveChanges:=False)
            compareBook.Close(SaveChanges:=True)
 
            ' Clean up the objects
            origSheet = Nothing
            compareSheet = Nothing
            origBook = Nothing
            compareBook = Nothing
 
 
            m_ExcelApp.Quit()
            m_ExcelApp = Nothing
 
            ' Change the culture back
            System.Threading.Thread.CurrentThread.CurrentCulture = oldCulture
        Catch ex As Exception
        Finally
            ' Make sure that the excel-object will be removed from memory
            GC.Collect()
            GC.WaitForPendingFinalizers()
        End Try
 
    End Sub

Open in new window

0
 
LVL 1

Author Closing Comment

by:vercesi
ID: 31468758
Thanks for your time.
0
 
LVL 1

Author Comment

by:vercesi
ID: 21830751
I had to tweak it a little in order to cope with Excel files with multiple Worksheets but the solution provided is excellent!
0
 
LVL 1

Author Comment

by:vercesi
ID: 21832683
Hello again.

Still have a problem.

I cannot open a specific worksheet by it's name using your code.

What I have so far is this:
'INSTEAD OF THIS
Dim origSheet As Microsoft.Office.Interop.Excel.Worksheet = origBook.Sheets.Item(1)
 
'I WANT SOMETHING LIKE THIS
Dim origSheet As Microsoft.Office.Interop.Excel.Worksheet = origBook.Sheets.Item("SHEET_NAME")
 
'How can I achieve this?

Open in new window

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question