Forum Discussion

Ivett_K's avatar
Ivett_K
Occasional Contributor
4 years ago

How to compare two Excel workbook's cell values

Hi,

 

We are trying to compare two Excel workbooks by specifying which cells should be compared as the column orders don't match in the files. We aren't a very technical team and tried to run the code below, but we get the following error (see screencapture attached too):

 

'TypeError: xfile1.Cells is not a function'

 

function CompareExcel()
{
let Excel = getActiveXObject("Excel.Application");

let xlfile1 = "S:\\Software\\mytilney.xlsx";
let xlfile2 = "S:\\Software\\plan.xlsx";

Excel.Workbooks.Open(xlfile1);
Excel.Workbooks.Open(xlfile2);

//let xsheet1 = xlfile1.Sheets("Sheet1");
//let xsheet2 = xlfile2.Sheets("Sheet1");

let RowCount = Excel.ActiveSheet.UsedRange.Rows.Count;
let ColumnCount = Excel.ActiveSheet.UsedRange.Columns.Count;
let Cells = Excel.ActiveSheet.UsedRange.Cells.Select;


for (let i = 1; i <= RowCount; i++) {

if (xlfile1.Cells(i,1).Value === xlfile2.Cells(i,1).Value) {
Log.Message("The files are the same.");

}};

Excel.Quit();
}

 

Is there anyone who could help is fix this this error please? Any help would be much appreciated.

 

We used the following documentation to produce this code:

 

https://support.smartbear.com/testcomplete/docs/testing-with/advanced/working-with-external-data-sources/excel/working-via-com.html

 

https://community.smartbear.com/t5/TestComplete-Desktop-Testing/Comparing-2-xls-files-with-the-specific-column/td-p/133603

 

Thanks,

 

Ivett

5 Replies

Sort By
  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    4 years ago

    So, you are calling xfile1.Cells.... but xlfile1 is a string... there is not a "Cells" function on that object.

    What you need to do is declare another variable to contain the workbook object.   Something like

     

    let xlWorkbook1 = Excel.Workbooks.Open(xlfile1)
let xlWorkbook2 = Excel.Workbooks.Open(xlfile2)

     

    Then you would call

     

     

    if (xlWorkbook1.Cells(i,1).Value === xlWorkbook2.Cells(i,1).Value) {
Log.Message("The files are the same.");

}};
    • Ivett_K's avatar
      Ivett_K
      Occasional Contributor
      4 years ago

      Hi Robert,

       

      Thank you for your quick reply. I changed the code to this:

       

      function CompareExcel()
      {
      let Excel = getActiveXObject("Excel.Application");

      let xlfile1 = "S:\\Software\\mytilney.xlsx";
      let xlfile2 = "S:\\Software\\plan.xlsx";

      let RowCount = Excel.ActiveSheet.UsedRange.Rows.Count;

      let xlWorkbook1 = Excel.Workbooks.Open(xlfile1);
      let xlWorkbook2 = Excel.Workbooks.Open(xlfile2);


      for (let i = 1; i <= RowCount; i++) {

      if (xlWorkbook1.Cells(i,1).Value === xlWorkbook2.Cells(i,1).Value) {
      Log.Message("The files are the same."); }
      };

      Excel.Quit();
      }

       

      But when I run it I'm still getting the same error:

       

      'TypeError: xlWorkbook1.Cells is not a function' 

       

      Thanks,

       

      Ivett