By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,983 Members | 1,717 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,983 IT Pros & Developers. It's quick & easy.

Compare 2 excel worksheet, consolidate differences in 3rd sheet

P: 4
Hi All,

I would like some help regarding my code. I want a function that compares 2 excel sheets in a workbook and consolidate their differences in the 3rd sheet. Right now my code can compare the 2 sheets and highlight their differences. All i want in the 3rd sheet is to show what are the changes, e.g (added data, deleted data, changed data). Thanks in advance for helping me :D Here is my code :
Expand|Select|Wrap|Line Numbers
  1. Dim xlApp As New Excel.Application
  2.         Dim xlPreviousWorkBook, xlCurrentWorkbook, xlOutputWorkBook As Excel.Workbook
  3.         Dim xlPreviousWorkSheet, xlCurrentWorksheet, xlPreviousOutputWorksheet, xlCurrentOutputWorksheet, xlResultWorksheet As Excel.Worksheet
  4.         Dim xlPreviousRange, xlCurrentRange, xlPreviousDestRange, xlCurrentDestRange, xlResultRange As Excel.Range
  5.         Dim lastline As String
  6.  
  7.  
  8.  
  9.         ' Opens Previous Workbook. Change path and filename as applicable
  10.         xlPreviousWorkBook = xlApp.Workbooks.Open("C:\Users\Desktop\Previous Version\cola")
  11.  
  12.         'Opens Cuurent Workbook.  Change path and filename as applicable
  13.         xlCurrentWorkbook = xlApp.Workbooks.Open("C:\Users\Desktop\Current Version\cola2")
  14.  
  15.         ' Opens Destination Workbook. Change path and filename as applicable
  16.         xlOutputWorkBook = xlApp.Workbooks.Open("C:\Users\Desktop\Output File Location\Differences")
  17.  
  18.         ' Display Excel
  19.         xlApp.Visible = True
  20.  
  21.         ' Set the previous worksheet
  22.         xlPreviousWorkSheet = xlPreviousWorkBook.Sheets("Sheet1")
  23.         ' Set the current worksheet
  24.         xlCurrentWorksheet = xlCurrentWorkbook.Sheets("Sheet1")
  25.         ' Set the destination worksheet
  26.         xlPreviousOutputWorksheet = xlOutputWorkBook.Sheets("Sheet1")
  27.         xlCurrentOutputWorksheet = xlOutputWorkBook.Sheets("Sheet2")
  28.         xlResultWorksheet = xlOutputWorkBook.Sheets("Sheet3")
  29.  
  30.         ' Set the previous range
  31.         xlPreviousRange = xlPreviousWorkSheet.UsedRange
  32.         ' Set the current range
  33.         xlCurrentRange = xlCurrentWorksheet.UsedRange
  34.         ' Set the previous destination range
  35.         xlPreviousDestRange = xlPreviousOutputWorksheet.UsedRange
  36.         ' Set the current destination range
  37.         xlCurrentDestRange = xlCurrentOutputWorksheet.UsedRange
  38.         ' Set the result destination range
  39.         xlResultRange = xlResultWorksheet.UsedRange
  40.  
  41.         ' Copy and paste the range
  42.         xlPreviousRange.Copy(xlPreviousDestRange)
  43.         xlCurrentRange.Copy(xlCurrentDestRange)
  44.  
  45.         ' Compare sheet1 and sheet2 but only highlights the difference between the two sheets
  46.         Try
  47.             lastline = Math.Max(xlPreviousOutputWorksheet.UsedRange.Rows.Count, xlCurrentOutputWorksheet.UsedRange.Rows.Count)
  48.  
  49.             For row As Integer = 1 To lastline
  50.                 Dim lastcol As Integer = Math.Max(xlPreviousOutputWorksheet.UsedRange.Rows.Count, xlCurrentOutputWorksheet.UsedRange.Rows.Count)
  51.                 For column As Integer = 1 To lastcol
  52.  
  53.                     ' Highlights the difference on both sheets
  54.                     If xlPreviousOutputWorksheet.Cells(row, column).Value <> xlCurrentOutputWorksheet.Cells(row, column).Value Then
  55.  
  56.                         DirectCast(xlPreviousOutputWorksheet.Cells(row, column), Excel.Range).Interior.Color = 5296274
  57.                         DirectCast(xlCurrentOutputWorksheet.Cells(row, column), Excel.Range).Interior.Color = 5296274
  58.  
  59.                     End If
  60.  
  61.                 Next
  62.             Next
  63.  
  64.  ' Save and close excel apps
  65.             xlOutputWorkBook.Save()
  66.             xlOutputWorkBook.Close()
  67.             xlPreviousWorkBook.Close()
  68.             xlCurrentWorkbook.Close()
  69.             xlApp.Quit()
  70.  
  71.             MessageBox.Show("Finished Processing")
  72.  
  73.         Catch ex As Exception
  74.             MsgBox("ERROR")
  75.         End Try
  76.     End Sub
  77.  
  78.  
Jun 13 '14 #1
Share this Question
Share on Google+
3 Replies


100+
P: 299
When your program highlights a line, you should also just add that line to a list of strings and then loop through the list of strings and write each string to your new workbook.
Jun 13 '14 #2

P: 4
good day Luk3r. i will try to do what you are suggesting. thank you. i'll get back to you if there any progress on my code.
Jun 16 '14 #3

P: 4
hi again actually what im trying to do is to consolidate the difference in the 3rd sheet.

for example:

in the cells 3rd sheet it shows,

Added - present in sheet 2 but not in sheet 1

Deleted - present in sheet 1 but not in sheet 2

Changed - there is value on same cell in both sheet but the values are different
Jun 17 '14 #4

Post your reply

Sign in to post your reply or Sign up for a free account.