Forum Discussion

mnichola30's avatar
mnichola30
Occasional Contributor
15 years ago

Excel Spreadsheet Validation

The web site that I am testing contains a feature that allows a user to export search results into an Excel spreadsheet.  Each downloaded Excel spreadsheet contains a unique name.  Is there a way to automate spreadsheets with dynamic names?  I would like to create a dataset to compare each exported Excel spreadsheet.  Is this possible?    I've attached a sample copy of a spreadsheet. 



Thanks for your help!

8 Replies

  • I dont think it's possible. You may try low level recording and replay, but if the coordinates changed, replay will fail. But there is a workaround, using VBscript to access a static Excel file. Please check vbscript Excel Object tutorial. 
  • Hi Melissa,


    I recommend that you refer to this page of our web site. It contains an example demonstrating how to compare Excel files.

  • mnichola30's avatar
    mnichola30
    Occasional Contributor
    Allen,



    Thanks for providing the link.  There is a section that discusses script extensions and an accompanying link to download necessary files.  The download does not contain a .TCX contain needed for script extension packages.  Do you know where I can find one that works with Excel spreadsheets?  



    Thanks,



    Melissa 
  • mnichola30's avatar
    mnichola30
    Occasional Contributor
    I am using the following code to compare two Excel files:




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

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


    oExcel = .OleObject("Excel.Application") aSheets = .OleObject("Scripting.Dictionary")

     


    fileName = "<C:\Test238>"

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

    fileName = "<C:\Test>"

    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



    I keep receiving the attached error message.



  • Hi Melissa,


    You do not need to use the '<' and the '>' symbols to specify the path to Excel files to be compared. Here's the corrected line that you need to use:



    fileName = "C:\Test238.xls"

  • I am also using the same code. 



    However getting following error:



    An exception occurred in the "Unit1" unit at line 94:

    Microsoft JScript runtime error

    'ExcelObj' is undefined





    Please help us.



    regards

    Arun
  • ananddave123's avatar
    ananddave123
    Occasional Contributor
    Third line is not required.



    oExcel = .OleObject("Excel.Application") aSheets = .OleObject("Scripting.Dictionary")
  • ananddave123's avatar
    ananddave123
    Occasional Contributor
    For dynamic filename add sysdate and time before the filename.



    Now & filename.xls