cancel
Showing results for 
Search instead for 
Did you mean: 

How to compare two Excel workbook's cell values

Highlighted
Occasional Contributor

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-sou...

 

https://community.smartbear.com/t5/TestComplete-Desktop-Testing/Comparing-2-xls-files-with-the-speci...

 

Thanks,

 

Ivett

5 REPLIES 5
Community Hero

Re: How to compare two Excel workbook's cell values

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.");

}};

Robert Martin
[Hall of Fame]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available
Highlighted
Occasional Contributor

Re: How to compare two Excel workbook's cell values

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

Highlighted
Community Hero

Re: How to compare two Excel workbook's cell values

It MIGHT be due to this.

 

https://support.smartbear.com/testcomplete/docs/scripting/specifics/javascript.html#indexedprops


Robert Martin
[Hall of Fame]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available
Highlighted
Community Manager

Re: How to compare two Excel workbook's cell values

Thank you for helping tristaanogre

 

@Ivett_K , did you manage to solve this issue?

If you update to the latest TestComplete version (14.50), you can try using the new Excel support to implement this task. It does not even require Excel to be installed on the machine.


Sonya Mihaljova
Community and Education Specialist

Learn SmartBear products in a fun and easy way and prove your knowledge!
>>Participate in the TechCorner Challenge today
Highlighted
Occasional Contributor

Re: How to compare two Excel workbook's cell values

Hi Sonya,

 

Thank you for your message.

 

We decided the leave comparing excel files for now as we did further analysis and came to the conclusion it would be far too complicated as the formatting is is completely different in the files we wanted to compare.

 

Thanks,

 

Ivett

New Here?
Join us and watch the welcome video:
Announcements
Join TechCorner Challenge!
Want a fun and easy way to learn TestComplete? Try solving weekly TechCorner challenges and get into the Leaderboard!


Challenge Status

Compare images using the Region Checkpoint

See replies!

Converting UTC TimeDate in an Excel file

See replies!

Compare HTML table with Excel file and correct data in Excel file

Participate!

How to execute remote test and obtain results via Test Runner REST API

Participate!
Top Kudoed Authors