ee)b) = PROJECT Compare Two Excel Files re Igor Krupitsky 19 Jan 2021 CPOL How to compare two Excel Files using VBS In this post, you will see how to drag and drop two Excel files to compare. Download source code - 24.3 KB Introduction Drag and drop two Excel files to compare. @ chess -[Link] Chess - [Link] 1B} [Link] (2) bxceComparevbs {8 EecaRonComparaiee Tow The changes will be highlighted in yellow. Use [Link] to compare cells.eee 1 2 3 4 5 6 Ey " 2 3 If your Excel files might have new rows or columns, use [Link]. Using the Code [Link] compares rows, column and cells. You can modify the code to exclude some worksheets or to save the file at the end. The script might take a long time to run depending on the size of the files. VBScript Const sFirstColbata = "Calendar" Set fs0 = Createdbject("[Link]") Dim sFilePathi, sFilePath2 ‘If [Link] = 2 thensFilePath = [Link] (0) sFilePath2 = [Link](1) Else MsgBox("Please drag and drop two excel files.") Wscript quit End If If [Link](sFilepath1) = False Then MsgBox "File 1 is missing: " & sFilePath1 Wscript quit End If If [Link](sFilePath2) = False Then MsgBox "File 2 is missing: " & sFilePath2 Wscript quit End If Dim sMissingsheets: sMissingsheets Din iDiffcell: iviffcell = @ Dim ADiffRow: iDiFfROW = 0 Din ADiFfCol: iiFFCol = 0 Dim oExcel: Set oExcel = CreateObject("Excel. Application") ofxcel. Visible = True o€[Link] = false Set oWorkBook1 = o£[Link](sFilePath1) Set oWorkBook? = oFxcel Workbooks open(sFilePath2) For Each oSheet in [Link] Tf SheetExists(oWorkBook2, [Link]) = False Then if sMissingSheets <> "" Then sMissingSheets = sMissingSheets & ", sMissingSheets = sMissingSheets & [Link] Else oSheet Activate Set oSheet2 = [Link]([Link]) Set rs = GetExcelRecordset(oSheet) Set rs2 = GetExcelRecordset (oSheet2) CompareCells oSheet, rs, oSheet2, rs2 CompareCells oSheet2, rs2, oSheet, rs End If Next For Each oSheet in [Link] If SheetExists(oWorkBook1, [Link]) = False Then if sMissingSheets <> "" Then sMissingSheets = sMissingSheets & sMissingSheets = sMissingSheets & [Link] End If Next Dim spiff: sbiff if iDiffcell <> @ Then SDiff = sDiff & iDiffCell & " cell differences.” End If if 4DiffROW <> @ Then if sDiff <> "" Then sDiff = sDiff & SDiff = sDiff & iDIffROW & " row differences.End If if 4DiFFCOl <> @ Then if 4DiffCol <> "" Then sDiff = sDiff &” SDiff = sDiff & iDiffcol & " colunn differences. End If If sMissingSheets <> "" Then Lf sDIff <> "" Then sDEff = sDLfF & SDiff = Diff & "Missing Worksheets: " & sMissingSheets & "." End If Lf sDiff = "" Then MsgBox “Files match" Else MsgBox “Found " & sDiff End If Sub CompareCells(oSheet, rs, oSheet2, rs2) ResetRs rs ResetRs rs2 Dim oColDiff: Set oColDiff = CreateObject (Scripting. Dictionary") Dim col: Set col = GetColDif#(oSheet, oSheet2) Dim iRow, iRow2 While rs.£0F = False Row = rs("RowNumber").Value sFirstCol = rs("c1").value & If sFirstCol <> [Link] & sFirstcol @"'" If [Link] = @ Then oSheet Rows (iRow & " ADiffRow = iDiffRow + 1 & iRow).[Link] = RGB(219, 255, 2) ElseIf [Link] = 1 Then iRow2 = rs2("RowNumber"). Value For iCol = 1 to [Link] - 1 icol2 = icol If col.£xists(icol) Then iol2 = col(icol) End If If iCol2 = -1 Then ‘Col not found If [Link](iCol) = False Then oSheet .Columns(iCol).[Link] oColDiFF(iCol) = True End If RGB(219, 255, 51) ElseIf iCol >= [Link] Or iCol2 >= [Link] Then ‘out of range ElseIf rs(icol).Value & "" <> rs2(iCol2).Value &[Link](iRow, iCol ).[Link] = 65535 iDiffcell = ipiffcell + 1 End If Next End If End If [Link] Wend If [Link] > @ Then ADiffCol = ADIfFCol + [Link] End If End Sub Sub ResetRs(rs) [Link] If [Link] > @ Then [Link] End If End sub Function GetColDiff(oSheet, oSheet2) Dim oRet: Set oRet = CreateObject("[Link]") Dim oCols: Set oCols = GetExcelColumns(oSheet) Dim oCols2: Set oCols2 = GetExcelColumns(oSheet2) Dim iCol: icol = @ For Each skey In [Link] Col = oCols(skey) Tf [Link](skey) Then Tf i€ol <> oCols2(skey) Then oRet(iCol) = oCols2(skey) ‘Col 1 => 2 (column was moved for 1 to 2 End If Else oRet(iCol) = -1 ‘Col not found End If Next Set GetColDiff = oRet End Function Function GetexcelColunns(osheet) Dim oCols: Set oCols = Createdbject("[Link]") Din iHeaderRow: iHeaderRow = 1 If sFirstColbata <> "" Then For i = 1 to 100 If [Link](i, 1).Value = sFirstColpata Then iKeaderRow = i -1 Exit For End If Next End If Dim iColCount: icolCount = GetLastcol (sheet) For i¢ol = 1 to iColCountsVal = oShet Cells(iHeaderRow, iCol).Value Tf sVal <> "" Then oCols(sval) = icol End If Next Set GetExcelcolumns = oCols End Function Function GetExcelRecordset (oSheet) Dim icolCount: iColCount = GetLastcol(oSheet) Dim iRowsCount: iRowsCount = GetLastRowWithData(oSheet) Dim rs: Set rs= CreateObject (“ADODB. recordset [Link] "RowNunber", 3 ‘adInteger For i¢ol = 1 to iColCount [Link] " Next: & iCol, 208, -1 ‘adVarchar [Link] For iRow = 1 to iRowsCount [Link] For iCol = 1 to iColcount c" & iCol) = [Link](irow, iCol).Value Next Next [Link] Set GetexcelRecordset = rs End Function Function GetLastRowithData(oSheet) Dim iMaxRow: iMaxRow = [Link]. Rows .Count If iMaxow > 500 Then iMaxRow = [Link]("*", [Link](1, 1), -4163, , 1, 2)-Row End If Dim iRow, iCol For iRow = iMaxRow to 1 Step -1 For iCol = 1 to [Link] If Trim([Link](irow, icol).Value) <> GetLastRowwithbata = iRow Exit Function End If Next Next GetLastRowWithbata End Function Then Function GetLastCol(st) GetLastCol = [Link]( End Function » [Link](1, 1), , 2, 2, 2, False).Colunn Function SheetExists(oWorkBook, sName)on error resume next Dim oSheet: Set oSheet = [Link](sName) If [Link] = @ Then SheetExists = True Else SheetExists = False [Link] End If End Function [Link] compares cells. It is smaller and can be more easily understood. VBScript Set fso = Createdbject("Scripting. FileSystenObject") Dim sFilePath1, sFilePath2 If [Link] = 2 then sFilePathi = [Link](@) sFilePath2 = [Link](1) Else MsgBox("Please drag and drop two excel files. [Link] End Tf If [Link](sFilePath1) = False Then MsgBox "File 1 is missing: " & sFilePath [Link] End Tf If #[Link](sFilePath2) = False Then MsgBox "File 2 is missing: " & sFilePath2 [Link] End If Dim sMissingSheets: sMissingSheets Dim ipiffcount: ipiffcount = @ Dim o€xcel: Set oExcel = CreateObject("Excel Application") [Link] = True o€[Link] = false Set oWorkBook1 = o£[Link](sFilePath1) Set oWorkBook2 = [Link](sFilePath2) For Each oSheet in [Link] If SheetExists(oWorkBook2, [Link]) = False Then if sMissingSheets <> "" Then sMissingSheets = sMissingSheets & ", SMissingSheets = sMissingSheets & oSheet Name Else oSheet Activate Set oSheet2 = [Link](oSheet .Name) icolCount = GetLastCol (oSheet) iRowsCount = GetLastRowWithData(oSheet) For iRow = 1 to iRowsCount For iCol = 1 to iColCount If [Link](iRow, iCol).Value <> [Link](iRow, iCol).Value ThenOSheet .Cells(iRow, iCol).[Link] = 65535 [Link](iRow, iCol).[Link] = 65535 ibiffCount = iDiffCount + 1 End If Next Next End If Next For Each oSheet in [Link] If SheetExists(oWorkBook1, [Link]) = False Then Lf sMissingSheets <> "" Then SMissingSheets = sMissingSheets & SMissingSheets = sMissingSheets & [Link] End If Next If ipiffCount = @ Then MsgBox "Files match” Else MsgBox "Found " & iDiffCount & " differences” End Tf If sMissingSheets <> "" Then MsgBox "Missing Worksheets: " & sMissingSheets End TF Function GetLastRowMithData(oSheet) Dim iMaxRow: iMaxRow = [Link] If iMaxRow > 588 Then iMaxRow = [Link]("* End If [Link](1, 1), -4163, , 1, 2).Row Dim iRow, icol For iRow = iMaxRow to 1 Step -1 For iCol = 1 to [Link] .Count If Trim([Link](iRow, iCol).Value) <> GetLastRowWithData = iRow Exit Function End If Next Next GetLastRowhithData = 1 End Function Then Function GetLastCol(st) GetLastCol = [Link]("*", [Link](1, 1), , 2, 2, 2, False).Column End Function Function SheetExists(oWorkBook, sName) on error resume next Dim oSheet: Set oSheet = [Link](sName) If [Link] = @ Then SheetExists = True Else SheetExists = False [Link]End If End Function History * 18" November, 2020: Initial version © 1% December, 2020: Added [Link] License This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL) Written By Igor Krupitsky Web Developer B United States Igor is a business intelligence consultant working in Tampa, Florida. He has a BS in Finance from University of South Carolina and Masters in Information Management System from University of South Florida, He also has following professional certifications: MCSD, MCDBA, MCAD. Comments and Discussions & 10 messages have been posted for this article Visit [Link] to post and view comments on this article, or click here to get a print view with messages. Permalink Article Copyright 2020 by Igor Krupitsky Advertise Everything else Copyright © CodeProject, Privacy 1999-2023 Cookies Terms of Use Web01 2.8:2023-05-13:1