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
267 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
  • 8
  • 4
  • 3
15 Comments
 
LVL 19

Expert Comment

by:elimesika
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

by:vercesi
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
HI , hope that I will have time for that during the weekend (not sure ....)

Good Luck
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

by:vercesi
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for your time.
0
 
LVL 1

Author Comment

by:vercesi
Comment Utility
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
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

771 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now