excel files comparison

Question

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

Suthish Nair · Answer

might helps refer

Suthish Nair · Answer

Might problem occurs due to multiple excel sheets got opened in memory. Or the sheet you trying to access does not exists.

Tommy Garrox · Answer

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!

Tommy Garrox · Answer

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 Then maxR = lr2 If maxC 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.