Forum Discussion

mesg2anil's avatar
mesg2anil
Regular Contributor
15 years ago

Excel File Comparing - Error

Hi,



I'm trying to compare two excel files using below code, I'm getting error and warning...




Sub Cmpare


 Cmpare

Set objExcel = CreateObject("Excel.Application")



'Extracting data from the onscreen application



Set iexplore = Sys.Process("iexplore", 2).Page("http://qaserver/Default.asp").document.frames.Frame("mainFrame").document.frames.Frame("ContentFrame").document.frames.Frame("ReportFrameIn4ReportViewer").document.frames.Frame("report").document.all.Item(76)


AgentName = aqObject.GetPropertyValue(iexplore, "innerText")


Log.Message("Agent Name:" &AgentName)


 


Set iexplore = Sys.Process("iexplore", 2).Page("http://qaserver/Default.asp").document.frames.Frame("mainFrame").document.frames.Frame("ContentFrame").document.frames.Frame("ReportFrameIn4ReportViewer").document.frames.Frame("report").document.all.Item(110)


TotAmt = aqObject.GetPropertyValue(iexplore, "innerText")


Log.Message("Total Amount:" &TotAmt)


 


Set iexplore = Sys.Process("iexplore", 2).Page("http://qaserver/Default.asp").document.frames.Frame("mainFrame").document.frames.Frame("ContentFrame").document.frames.Frame("ReportFrameIn4ReportViewer").document.frames.Frame("report").document.all.Item(112)


TotBal = aqObject.GetPropertyValue(iexplore, "innerText")


Log.Message("Balance Amount:" &TotBal)


 


objExcel.Visible = True


objExcel.Workbooks.Add


objExcel.Cells(1, 1).Value = AgentName


objExcel.Cells(1, 2).Value = TotAmt


objExcel.Cells(1, 3).Value = TotBal


 


objExcel.ActiveWorkbook.SaveAs("E:\ReportData1.xlsx")


objExcel.Quit


Delay 3000



'Compare two excel files to see any changes are there...



If Not Files.Compare("E:\ReportData1.xlsx", "E:\TestData.xlsx") Then


Log.Error("Both the files are NOT same")


Else


log.Message("Both the files are same")


End If


 



End Sub



Details:

I'm extracting data from the application and saving all the info to the excel file i.e. to E:\ReportData1.xlsx and comparing the same data copying it to  E:\TestData.xlsx, I still get the error "Both the files are NOT same" and one warning is shown as below

"The files "E:\ReportData1.xlsx" and "E:\TestData.xlsx" are not equal. HashValue = 319838130.The files "E:\ReportData1.xlsx" and "E:\TestData.xlsx" are not equal. HashValue = 319838130."



Question:

1. How to get rid of above error and warning messages?

2. If I want to use a file which is already stored in my system and I want to extract the data and input the details to that file? (in this case to file "E:\ReportData1.xlsx")



Please do the needful


 I'm extracting data from the application and saving all the info to the excel file i.e. to E:\ReportData1.xlsx and comparing the same data copying it to  E:\TestData.xlsx, I still get the error "Both the files are NOT same" and one warning is shown as below"The files "E:\ReportData1.xlsx" and "E:\TestData.xlsx" are not equal. HashValue = 319838130.The files "E:\ReportData1.xlsx" and "E:\TestData.xlsx" are not equal. HashValue = 319838130."Question:1. How to get rid of above error and warning messages?2. If I want to use a file which is already stored in my system and I want to extract the data and input the details to that file? (in this case to file "E:\ReportData1.xlsx")Please do the needful

7 Replies

  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    15 years ago
    Files.Compare does a binary file comparison (see http://smartbear.com/support/viewarticle/11074/).  If there are differences, even by a byte or so, then the files will not match.  There's a LOT of stuff that is stored within an Excel sheet that is not necessarily related to the data itself.  Additionally, keep in mind that what you're writing to the cells are probably being written as text but the TEstData.XLSX might have that data written as numerics.  This would cause a difference as well.



    If storing the data off in an Excel sheet is not critical to your application, you might want to investigate other ways of doing the comparison.



    For example, let's say you have an excel spreadsheet that contains multiple rows of data that you want to loop through and compare a row of data from that sheet to the values on screen.  You might wrap your entire code with a DDT.ExcelDriver and while loop.  See below... note that the code I've posted is pseudo-code and may not be executable as is...



    dim ExcelDriverObj

    Set ExcelDriverObj = DDT.ExcelDriver("E:\TestData.XLSX", "Sheet1", true)

    while (!ExcelDriverObj.EOF)

    //Do your code to get the AgentName

    if ExcelDriverObj.Value("AgentName") != AgentName

        Log.Message "The Agent Name doesn't match"

    //Do your code to get the total amount

    if ExcelDriverObj.Value("TotalAmount") != TotAmt

        Log.Message "The Total Amount doesn't match"

    //Do your code to get the total balance

    if ExcelDriverObj.Value("TotalBalance") != TotBal

        Log.Message "The Total Balance doesn't match"

    Call ExcelDriverObj.Next()

    WEnd





  • mesg2anil's avatar
    mesg2anil
    Regular Contributor
    15 years ago
    Hi Robert,



    Thank you very much for the code provided...

    I modified the script accordingly to my requirement and it is working now. ;-)
  • mesg2anil's avatar
    mesg2anil
    Regular Contributor
    15 years ago
    Hi,



    I have an issue understanding how files.compare or files.check work?



    Below code which I used, is exactly same as shown in the example of files.compare, but I still see the issue in the comparision.

    The files BIZDEVAgentPaymentSummary.xls and Downloads\BIZDEVAgentPaymentSummaryReport.xls are exactly same, I made a copy of first file and tried comparing, first time it showed me the files are same, then i made very small change for ex: in the first file if I had value "50" in one of the cell, I changed the 50 value to 51 in the second file and compared. This time also it showed me that both the files are same. I changed the value 51 to 50 from the second file, this time it is showing me that files are "not same".




    Sub a


     a

    PrecalculatedDifference = Files.CalculateHashValue("C:\Users\anil.yadrami\Downloads\BIZDEVAgentPaymentSummary.xls", "C:\Users\anil.yadrami\Downloads\BIZDEVAgentPaymentSummaryReport.xls")



    'Compare two excel files to see any changes are there...



    If Not Files.Compare("C:\Users\anil.yadrami\Downloads\BIZDEVAgentPaymentSummary.xls", "C:\Users\anil.yadrami\Downloads\BIZDEVAgentPaymentSummaryReport.xls", PrecalculatedDifference) Then


    Log.Error("Both the files are NOT same")


    Else


    log.Message("Both the files are same")


    End If


     



    End Sub



    The "PrecalculatedDifference" give some strange number, how to know what exactly that number is?

    How to do a simple excel file comparision? Can you provide me an example code please?


     The "PrecalculatedDifference" give some strange number, how to know what exactly that number is?How to do a simple excel file comparision? Can you provide me an example code please?
  • YMinaev's avatar
    YMinaev
    Icon for Staff rankStaff
    15 years ago
    Hi,



    Files.Compare compares files by hash. Files should be binary identical to be considered the same. Excel files contain service info besides the actual data, and Files.Compare in most case won't work for them. Use Robert's code or the Excel checkpoint extension.
  • mesg2anil's avatar
    mesg2anil
    Regular Contributor
    15 years ago
    Hi Jared,

    Thanks for the reply...

    do you have link for "Excel checkpoint extension"?