How to trigger the Random functionality in Excel and save the changes made on the Excel file

Problem: How to generate random numbers using Excel as a data source and save the changes in the excel file.

 

Descriptions: I have an Excel file that has a column for generating a certain number. I would then upload the excel file as part of my keyword test and make use of the records in the file for my tests. The Excel file alone is working fine and is generating random numbers everytime that it is opened. But when I upload it while running the keyword test, it is not generating random numbers but is instead reusing the number saved to it over and over again.

 

I have tried the approach on "Picking a random row from a dataset as part of a test" but is getting stuck at an error "The table does not contain a column with the specified name".

 

I also tried using a script (written below) and was somehow successful in triggering Excel to generate random number on every upload but I keep on getting the prompt to 'Save the changes' and since it is a Windows object, NHUIWind if I am not mistaken, I cannot map the button objects inside the prompt even after trying the approach on "Microsoft Excel Issue". 

 

function RefreshSheet ()

{

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

var ExcelPath = Project.Variables.File_directory + ("\\");

var ExcelFileName = Project.Variables.TempVar;

var ExcelFile = ExcelPath + ExcelFileName;

 

Excel.Workbooks.Open (ExcelFile);

Excel.ActiveSheet.UsedRange.Rows.Activate();

Excel.ActiveSheet.UsedRange.Rows.BorderAround();

Excel.Workbooks.Close();

}

 

Hoping someone will notice and will be able to help.

 

Note - I prefer using keywords and objects versus using script.