Skip to Content
1
Feb 03, 2021 at 08:59 AM

How do I delete a range in excel using variables in SAP iRPA?

73 Views Last edit Feb 03, 2021 at 11:26 AM 2 rev

Hi,

I'm trying to delete a range in excel in SAP iRPA using variables as range, but not able to do the same as it comes back with the below error:

'Step GLOBAL.newWorkflow.stDeleteRange: Error, [ctx.excel.sheet.deleteRange] Failed to deleteRange in excel workbook.Expected ';''

Below is my code. Any help is appreciated. Thanks.

// ----------------------------------------------------------------
//   Step: stDeleteRange
// ----------------------------------------------------------------
GLOBAL.step({ stDeleteRange: function(ev, sc, st) {
	var rootData = sc.data;
	ctx.workflow('newWorkflow', 'e5fa96cc-a744-43de-898d-a989a30c5263') ;
	
	var ExcelFile = "C:\\Users\\suvendub\\Test_DeleteRange.xlsx";
	if (ctx.fso.file.exist(ExcelFile))
	{
		ctx.excel.file.open(ExcelFile);
		ctx.log("File Opened:: " +ExcelFile);
		var oObject = ctx.excel.file.open(ExcelFile, false);
		var BookList = null;
		var retryCount = 0;
		while ((BookList == null) && (retryCount < 3)) 
		{
			try
			{
				retryCount++;
				BookList = ctx.excel.sheet.getList();
				ctx.log("BookList:: " +BookList);
			}
			catch (error)
			{
				ctx.log("Unable to read excel. Retrying after 2 seconds... " + retryCount);
				ctx.sleep(2000);
			}
		}
		var Sheet1 = BookList[0];
		ctx.excel.sheet.activate(Sheet1);

		var index = 2;
	
		// Deleting the records exceeding the number of days specified
		while((ctx.excel.sheet.getCell(index, 1)) != null) 
		{
			ctx.log("Data:: " +ctx.excel.sheet.getCell(index, 8));
			if(ctx.excel.sheet.getCell(index, 8) > 5)
			{
				ctx.log("index: " +index); // Returns 4
				var cellEnd = ctx.excel.sheet.getCell(index, 5);
				cellEnd = Number(cellEnd) + Number(index);
				ctx.log("cellEnd: " +cellEnd); //Returns 6
				//delete the record
				var str = "'" +Number(index) +":" +cellEnd +"'";
				ctx.log("str: " +str); // Returns '4:6'
				ctx.excel.sheet.deleteRange(str); // This line produces ERROR
				index++
			}
			else
			{
				index++;
			}
		}
		ctx.excel.file.save(oObject);
		ctx.excel.file.close(oObject, true);
		ctx.excel.end();
	}
	sc.endStep(); // end Scenario
	return;
}});