excel files comparison

Tommy Garrox

15y3.2k

Hello,
im writing a solution for my boss, but i am struggled with a piece of the solution.. ill explain.

i have 2 excell sheets (file1.xls, file2.xls).
both files contain 2 columns, the first collumn contains variableNames, the 2nd collumn contains the value(data)(numeric).
the 1st collumns of the 2 files are exactly the same.

now i want to write a script that compares the 2 files, then write all the differences into a 3rd file (file3.xls).
somehow i cant imagine a way how to accomplish this.

the nicest thing is a code that i can implement in a new excell sheet(compare.xls) but it may also be a visualStudio script or vb6 or anything else vb (except ASP).

here is an example of how the files look:

thnx in advance,
Tommy

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tommy Garrox15y ago
thats why i stated: "im very sure that the filepath of File2.xls is correct."
i got one directory that contains the 3 files (File1.xls, File2.xls, Vergelijking.xls).
i already launched "msgbox(activeworkbook.path & "File2.xls") to check on the filepath. it was very correct.

no in the last piece of code i have a few lines commented out, if i use them, im gonna compare 2 sheets of the same workbook.
that works like a charm. so the only thing that doesnt work is opening that 2nd workbook.
and i just know its possible!
Suthish Nair15y ago
Might problem occurs due to multiple excel sheets got opened in memory. Or, the sheet you trying to access does not exists.
Suthish Nair15y ago
might helps, refer

Tommy Garrox15y ago

this is what i have now:

Sub VergelijkSheets(ws1 As Worksheet, ws2 As Worksheet)
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
  Application.ScreenUpdating = False
  Application.StatusBar = "Bezig met bouwen van rapportage..."
    Set rptWB = Workbooks.Add(ActiveWorkbook.Path & "\Vergelijking.xls")
  Application.DisplayAlerts = False
  While Worksheets.Count > 1
    Worksheets(2).Delete
  Wend
  Application.DisplayAlerts = True
  With ws1.UsedRange
    lr1 = .Rows.Count
    lc1 = .Columns.Count
  End With
  With ws2.UsedRange
    lr2 = .Rows.Count
    lc2 = .Columns.Count
  End With
  maxR = lr1
  maxC = lc1
  If maxR < lr2 Then maxR = lr2
  If maxC < lc2 Then maxC = lc2
  DiffCount = 0
  For c = 1 To maxC
    Application.StatusBar = "Cellen vergelijken... " & Format(c / maxC, "0 %") & "..."
    For r = 1 To maxR
      cf1 = ""
      cf2 = ""
      On Error Resume Next
      cf1 = ws1.Cells(r, c).FormulaLocal
      cf2 = ws2.Cells(r, c).FormulaLocal
      On Error GoTo 0
      If cf1 <> cf2 Then
        DiffCount = DiffCount + 1
        Cells(r, c).Formula = "'" & cf1 & " <> " & cf2
      End If
    Next r
  Next c
  Application.StatusBar = "Rapport layout opmaken..."
  With Range(Cells(1, 1), Cells(maxR, maxC))
    .Interior.ColorIndex = 19
    With .Borders(xlEdgeTop)
      .LineStyle = xlContinuous
      .Weight = xlHairline
    End With
    With .Borders(xlEdgeRight)
      .LineStyle = xlContinuous
      .Weight = xlHairline
    End With
    With .Borders(xlEdgeLeft)
      .LineStyle = xlContinuous
      .Weight = xlHairline
    End With
    With .Borders(xlEdgeBottom)
      .LineStyle = xlContinuous
      .Weight = xlHairline
    End With
    On Error Resume Next
    With .Borders(xlInsideHorizontal)
      .LineStyle = xlContinuous
      .Weight = xlHairline
    End With
    With .Borders(xlInsideVertical)
      .LineStyle = xlContinuous
      .Weight = xlHairline
    End With
    On Error GoTo 0
  End With
  Columns("A:IV").ColumnWidth = 20
  rptWB.Saved = True
  If DiffCount = 0 Then
    rptWB.Close False
  End If
  Set rptWB = Nothing
  Application.StatusBar = False
  Application.ScreenUpdating = True
  MsgBox DiffCount & " Cellen Verschillen in Values!", vbInformation, _
    "Compare " & ws1.Name & " with " & ws2.Name
End Sub

Sub StartVergelijking()
  ' Vergelijk 2 sheets in het actieve bestand
  'VergelijkSheets Worksheets("Sheet1"), Worksheets("Sheet2")
  ' Vergelijk een sheet in het actieve bestand met een sheet uit een ander bestand
    VergelijkSheets ActiveWorkbook.Worksheets("Sheet1"), _
    Workbooks(ActiveWorkbook.Path & "\File2.xls").Worksheets("Sheet1")

  End Sub

now i get the following error: Run-time error '9': Subscript out of range i cant get it to work. File2.xls is in the same directory as the active workbook.

when i click on debug, the last sub gets pointed out (yellow bg).

im very sure that the filepath of File2.xls is correct.