hi good day to all, i have this code to compare two excel sheets and show their differences in the 3rd sheet but the code is in vba format and i want to convert this to vb.net. i am asking for help regarding this problem. im new here and i appreciate your help thank you.
here is the code: -
Option Explicit
-
Dim miMaxColumns As Integer
-
Sub CompareSheets()
-
Dim bChanged As Boolean, baChanged() As Boolean
-
Dim iColEnd As Integer, iCol As Integer, iCol1 As Integer, iCol2 As Integer
-
Dim lRow1 As Long, lRow2 As Long, lReportRow As Long
-
Dim objDictOld As Object, objDictNew As Object
-
Dim vKeys As Variant, vKey As Variant
-
Dim vaInput() As Variant, vaOutput() As Variant, vaOutput2() As Variant
-
Dim vaInputOld As Variant, vaInputNew As Variant
-
Dim wsOld As Worksheet, wsNew As Worksheet, wsReport As Worksheet
-
-
-
Set wsOld = Sheets("Sheet1")
-
miMaxColumns = wsOld.Cells(1, Columns.Count).End(xlToLeft).Column
-
Set objDictOld = PopulateDictionary(WS:=wsOld)
-
Set wsNew = Sheets("Sheet2")
-
Set objDictNew = PopulateDictionary(WS:=wsNew)
-
-
Set wsReport = Sheets("Sheet3")
-
-
With wsReport
-
.Cells.ClearFormats
-
.Cells.ClearContents
-
End With
-
-
wsOld.Range("A1:" & wsOld.Cells(1, miMaxColumns).Address).Copy
-
wsReport.Range("B1").PasteSpecial xlPasteValues
-
Application.CutCopyMode = False
-
-
lReportRow = 1
-
vKeys = objDictOld.Keys
-
For Each vKey In vKeys
-
ReDim vaInputOld(1 To 1, 1 To miMaxColumns)
-
vaInputOld = objDictOld.Item(vKey)
-
If objDictNew.exists(vKey) Then
-
ReDim vaInputNew(1 To 1, 1 To miMaxColumns)
-
vaInputNew = objDictNew.Item(vKey)
-
ReDim vaOutput(1 To 1, 1 To miMaxColumns + 1)
-
ReDim vaOutput2(1 To 1, 1 To miMaxColumns + 1)
-
ReDim baChanged(1 To miMaxColumns)
-
bChanged = False
-
For iCol = 1 To miMaxColumns
-
vaOutput(1, iCol + 1) = vaInputOld(1, iCol)
-
If vaInputOld(1, iCol) <> vaInputNew(1, iCol) Then
-
vaOutput2(1, iCol + 1) = vaInputNew(1, iCol)
-
baChanged(iCol) = True
-
bChanged = True
-
End If
-
Next iCol
-
If bChanged Then
-
lReportRow = lReportRow + 1
-
For iCol = 1 To UBound(baChanged)
-
If baChanged(iCol) Then
-
With wsReport
-
.Range(.Cells(lReportRow, iCol + 1).Address, _
-
.Cells(lReportRow + 1, iCol + 1).Address).Interior.Color = vbYellow
-
End With
-
End If
-
Next iCol
-
-
vaOutput(1, 1) = "Changed"
-
With wsReport
-
.Range(.Cells(lReportRow, 1).Address, _
-
.Cells(lReportRow, miMaxColumns + 1).Address).Value = vaOutput
-
lReportRow = lReportRow + 1
-
.Range(.Cells(lReportRow, 1).Address, _
-
.Cells(lReportRow, miMaxColumns + 1).Address).Value = vaOutput2
-
End With
-
End If
-
objDictOld.Remove vKey
-
objDictNew.Remove vKey
-
Else
-
ReDim vaOutput(1 To 1, 1 To miMaxColumns + 1)
-
vaOutput(1, 1) = "Deleted"
-
For iCol = 1 To miMaxColumns
-
vaOutput(1, iCol + 1) = vaInputOld(1, iCol)
-
Next iCol
-
-
lReportRow = lReportRow + 1
-
With wsReport
-
.Range(.Cells(lReportRow, 1).Address, .Cells(lReportRow, miMaxColumns + 1).Address).Value = vaOutput
-
'-- Set the row to light grey
-
.Range(.Cells(lReportRow, 2).Address, .Cells(lReportRow, miMaxColumns + 1).Address).Interior.ColorIndex = 15
-
End With
-
End If
-
Next vKey
-
-
If objDictNew.Count <> 0 Then
-
vKeys = objDictNew.Keys
-
For Each vKey In vKeys
-
ReDim vaOutput2(1 To 1, 1 To miMaxColumns + 1)
-
vaInputNew = objDictNew.Item(vKey)
-
vaOutput2(1, 1) = "Inserted"
-
For iCol = 1 To miMaxColumns
-
vaOutput2(1, iCol + 1) = vaInputNew(1, iCol)
-
Next iCol
-
lReportRow = lReportRow + 1
-
With wsReport
-
.Range(.Cells(lReportRow, 1).Address, .Cells(lReportRow, miMaxColumns + 1).Address).Value = vaOutput2
-
'-- Set the row to light green
-
.Range(.Cells(lReportRow, 2).Address, .Cells(lReportRow, miMaxColumns + 1).Address).Interior.ColorIndex = 4
-
End With
-
Next vKey
-
End If
-
-
objDictOld.RemoveAll
-
Set objDictOld = Nothing
-
objDictNew.RemoveAll
-
Set objDictNew = Nothing
-
End Sub
-
Private Function PopulateDictionary(ByRef WS As Worksheet) As Object
-
Dim lRowEnd As Long, lRow As Long
-
Dim rCur As Range
-
Dim sKey As String
-
-
Set PopulateDictionary = Nothing
-
Set PopulateDictionary = CreateObject("Scripting.Dictionary")
-
lRowEnd = WS.Cells(Rows.Count, "A").End(xlUp).Row
-
For lRow = 2 To lRowEnd
-
sKey = Trim$(LCase$(CStr(WS.Range("A" & lRow).Value)))
-
On Error Resume Next
-
PopulateDictionary.Add key:=sKey, Item:=WS.Range(WS.Cells(lRow, 1).Address, _
-
WS.Cells(lRow, miMaxColumns).Address).Value
-
On Error GoTo 0
-
Next lRow
-
End Function
-
-
0 1215 Sign in to post your reply or Sign up for a free account.
Similar topics
by: rama |
last post by:
I need to convert excel file to txt file programatically which may
contain 60000 records
i want to convert it into txt file and upload it into unix server
thanks in advance
rama
|
by: Arvind P Rangan |
last post by:
Hi,
Trying to convert excel file data to base64 for encryption.
Can anyone help me on this.
Thanks
Arvind
|
by: karups |
last post by:
Hi
when i convert Excel file to dataset using the following code, i find
that, some col. such as
Col1
------
404
403
NOT
222
|
by: subashini Thiyagarajan |
last post by:
i want to convert excel into pdf file,i had lot ofoptions to convert all free trials i tried but nothing is supporting hyperlinks can any one help me with the useful URL
thanks
|
by: payelit21 |
last post by:
Hi Friends,
can any body help me to convert excel cell from date to general and passinfg value to min value and max value of axis in chart through VB.
Thanks
Payel
|
by: binny |
last post by:
i use jet engine ,and oledb database but with this i get four errors which r compile time errores,plz help me to solve these errors
i wrote this programe bt it is not working .
using System.Web;...
|
by: lyne_asp |
last post by:
Please help me to convert excel date to asp date.
Here is my code
objConnEx.open "Provider=Microsoft.Jet.OLEDB.4.0;Data...
|
by: kamill |
last post by:
I need to convert excel file into PDF file using PHP, how can i do it. I am able to generate a pdf file.
Is there any solution to export excel data into mysql database?
I am waiting for help.
|
by: Raymond Chiu |
last post by:
Dear all,
In my vb.net program, I am using excel object to create excel file and
stored in a folder. How can it be converted to be pdf file automatically
using dotnet coding or others?
Actually...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |