Script Code not opening .xlsx files

Hi Sumedha,

Add the following line after 'var oSheets;' - this can help you see an error message if it is displayed:

oExcel.Visible = true; 

For more information, refer to the 'OleObject Property' help topic. If this suggestion does not help, please let us know which version of Excel you are using and send us the file you are trying to open.

ExcelComp("C:\\Bur64.xlsx","C:\\tcfiles\\burster\\Excel\\Bur64.xlsx");

function ExcelComp(fileName1,fileName2)

{

var oExcel = Sys.OleObject("Excel.Application");

var aSheets = Sys.OleObject("Scripting.Dictionary");

oExcel.Visible = true;

var aInfo1 = createInfo(oExcel, fileName1, aSheets);

// var fileName = "C:\\Temp\\book1.xls";

var aInfo2 = createInfo(oExcel, fileName2, aSheets);

var oResult = compare(aInfo1, aInfo2);

try

{

if (oResult.length > 0)

  {Log.Warning(oResult);}

else

  {Log.Message("The books are the same.");}

}

catch(e)

{

Log.Error("Exception occured",e.description);

return null ;

}

oExcel.Quit();

}

{

var oSheets;

oExcel.Workbooks.Close();

try{

oExcel.Workbooks.Open(fileName); //Exception occurs at this point when it tries to find a non-existing excel file

}

catch(e)

{

Log.Error("Exception occured",e.description);

return null ;

}

oSheets = oExcel.Worksheets;

sheetCount = oSheets.Count;

if (aSheets.Count == 0)

{

for (var sheetId = 1; sheetId < sheetCount + 1; sheetId++)

{

var oSheet = oSheets.Item(sheetId);

aSheets.Add(oSheet.Name, oSheet.Name);

}

}

var vSheets = Sys.OleObject("Scripting.Dictionary");

for (var sheetId = 1; sheetId < sheetCount + 1; sheetId++)

{

var oSheet = oSheets.Item(sheetId);

if (aSheets.Item(oSheet.Name) == null)

{

continue;

}

var vSheet = Sys.OleObject("Scripting.Dictionary");

var rowCount = oSheet.UsedRange.Rows.Count;

for (var rowId = 1; rowId < rowCount + 1; rowId++)

{

var rowObj = oSheet.Rows.Item(rowId);

var vRow = Sys.OleObject("Scripting.Dictionary");

var cellCount = rowObj.Cells.Count;

for (var cellId = 1; cellId < cellCount + 1; cellId++)

{

var cellObj = rowObj.Cells.Item(cellId);

vRow.Add(cellObj.Column, cellObj.Text);

}

vSheet.Add(rowId, vRow);

}

vSheets.Add(oSheet.Name, vSheet);

}

return vSheets;

}

{

var strCompare = "";

try

{

if (aInfo1.Count != aInfo1.Count)

{

strCompare = strCompare + "Wrong sheet count!"

return strCompare;

}

}

catch(e)

{

Log.Error("Exception occured",e.description);

return null;

}

var vKeys = aInfo1.Keys().toArray();

var sheetCount = aInfo1.Count;

for (var sheetId = 0; sheetId < sheetCount; sheetId++)

{

if (!aInfo2.Exists(vKeys[sheetId]))

{

strCompare = strCompare + "Sheet names are different!";

return strCompare;

}

var oSheet1 = aInfo1.Item(vKeys[sheetId]);

var oSheet2 = aInfo2.Item(vKeys[sheetId]);

var rowCount1 = oSheet1.Count;

var rowCount2 = oSheet2.Count;

if (rowCount1 != rowCount2)

{

strCompare = strCompare + "Wrong used row count in the '" + vKeys[sheetId] + "' sheet."

return strCompare;

}

for (var rowId = 0; rowId < rowCount1; rowId++)

{

var rowObj1Key = oSheet1.Keys().toArray()[rowId];

if (!oSheet2.Exists(rowObj1Key))

{

strCompare = strCompare + "The '" + rowObj1Key + "' row key was not found.";

return strCompare;

}

var oRow1 = oSheet1.Item(rowObj1Key);

var oRow2 = oSheet2.Item(rowObj1Key);

var cellCount = oRow1.Count;

for (var cellId = 0; cellId < cellCount; cellId++)

{

var celObj1Key = oRow1.Keys().toArray()[cellId]

if (!oRow2.Exists(celObj1Key))

{

strCompare = strCompare + "The '" + celObj1Key + "' cell was not found." + "\r\n";

return strCompare;

}

var oCell1 = oRow1.Item(celObj1Key);

var oCell2 = oRow2.Item(celObj1Key);

if (!SameText(oCell1, oCell2))

{

strCompare = strCompare + "The " + vKeys[sheetId] + ":\r\n The [" + rowId + ", " + cellId + "] cell was changed." + "\r\n" + " The '" + oCell1 + "' value." + "\r\n" + " The '" + oCell2 + "' value." + "\r\n";

}

}

}

}

return strCompare;

}

Hi,

Replace the following line in the createInfo function:

  var rowObj = oSheet.Rows.Item(rowId); 

with the line below:

  var rowObj = oSheet.UsedRange.Rows.Item(rowId);

Tell us whether it works.