Forum Discussion

kandy1984's avatar
kandy1984
Contributor
12 years ago

Compare .xlsx file

Hi,

i need to compare 2 .xlsx files but the code that i am using (below) is working only for .xls file. May i know what else i need to insert to particulaly compare .xlsx file?



Sub CompareExcelFiles

Dim oExcel, aSheets, vSheet, fileName, aInfo1, aInfo2

Set oExcel = Sys.OleObject("Excel.Application")

Set aSheets = Sys.OleObject("Scripting.Dictionary")


fileName = "C:\Users\Test\Desktop\sequence_original.xlsx"

Set aInfo1 = ExcelObj.CreateInfo(oExcel, fileName, aSheets)



fileName = "C:\Users\Test\Desktop\sequence.xlsx"

Set aInfo2 = ExcelObj.CreateInfo(oExcel, fileName, aSheets)

oResult = ExcelObj.Compare(aInfo1, aInfo2)

If Len(oResult) > 0 Then

Log.Warning oResult

Else

Log.Message "The files are the same."

End If

oExcel.Quit

End Sub



thanks for helping!






7 Replies

  • gid_216's avatar
    gid_216
    Frequent Contributor
    12 years ago
    Hi Sudha,



    Is Ms-Office 2007 or higher is installed in your system because .xlsx is supported in MsOffice 2007 and higher.



    For this campare, better use the ExcelDrivers (I don't know your exact requirement in excel comparision). try below sample code.



    Sub ComapreExcels(strExcel1, strSheet1, strExcel2, strSheet2)

      Dim objExcel1, objExcel2, intRowCount1, intRowCount2, strErrorDescription, blnValidationStatus

      intRowCount1 = 0

      intRowCount2 = 0

      Set objExcel1 = DDT.ExcelDriver(strExcel1, strSheet1, True)

      Set objExcel2 = DDT.ExcelDriver(strExcel2, strSheet2, True)

      

      If objExcel1.ColumnCount <> objExcel2.ColumnCount Then

        LogReport "Fail"

        Exit Sub

      End If

      

      While Not objExcel1.EOF

        intRowCount1 = intRowCount1 + 1

        objExcel1.Next

      Wend

      While Not objExcel2.EOF

        intRowCount2 = intRowCount2 + 1

        objExcel2.Next

      Wend

      If intRowCount1 <> intRowCount2 Then

        LogReport "Fail"

        Exit Sub

      End If

      

      objExcel1.First

      objExcel2.First

      While Not objExcel1.EOF

        For intColumnCounter = 0 To objExcel1.ColumnCount - 1

          If objExcel1.Value(intColumnCounter) = objExcel2.Value(intColumnCounter) Then

            LogReport "Fail"

          End If

        Next

        objExcel1.Next

        objExcel2.Next

      Wend

      

      DDT.CloseDriver(objExcel1.Name)

      DDT.CloseDriver(objExcel2.Name)

    End Sub

  • TanyaYatskovska's avatar
    TanyaYatskovska
    Icon for Alumni rankAlumni
    12 years ago

    Hi Sudha,


     


    This sample works for me to compare .xlsx files. Do you get any error when executing your code? Can you post a screenshot of it here?

  • kandy1984's avatar
    kandy1984
    Contributor
    12 years ago
    Hi Tanya,

    Yes i have tried that code but it just hangs in there when executing. not doing anything after that.



    my aim is to pair 2 .xslx files and im using version 9.10.1894.7 of testcomplete.



    Thanks for helping.



    Sudha
  • paul_scroce's avatar
    paul_scroce
    Frequent Contributor
    12 years ago
    Could you create 2 new simple xslx files in Excel then try to compare them using the same code?  This might help determine if there is a probem with the format of the original files.
  • TanyaYatskovska's avatar
    TanyaYatskovska
    Icon for Alumni rankAlumni
    12 years ago

    Hi Sudha,


     


    Perhaps, the files contain a lot of data...


    Anyway, as far as I can see, only Excel 2003 is considered a supported version in the How To article. Maybe, there was a reason for this. I suggest that you contact our TestComplete Support team and ask them to check how the sample works in Office 2007, 2010, and 2013.


     

  • gid_216's avatar
    gid_216
    Frequent Contributor
    12 years ago
    Hi Sudha,



    Have you installed the script extension