Dynamic column name for excel update

Eggplant Functional
, , ,
1

I am trying to update the excel with a handler like below:

to update_excel with whichXLSht , which_column, which_value
      Set xlFile to "my file path"
      Set xlSheet to table whichXLSht of {type:"excel", file: xlFile, writeable: yes}

	Set curr_rec  to the record of xlSheet where "colA" is "SAMPLE"
       -- At this point, I can see all other column's values properly in the watch window

     --now updating like this
     -- Both these below lines are not working as expected. It is just adding a new column in the 
     --name of "which_column" but before executing these lines, my which_column variable is
     --having a valid already existing column in the excel sheet
     set which_column of curr_rec to which_value
     set curr_rec's which_column  to which_value
End update_excel

No idea what’s wrong here. Any help would be appreciated.

2

Try evaluating the parameter value like this:

set (which_column) of curr_rec to which_value

See this page for more info. Property Lists | EPF Docs

2 Likes
3

Hey @scsvel,

Your script will not update the excel record. it only updates the variable in Sensetalk. You need to use a reference or write the record back to the excel file. Here is another thread which has a couple of useful links for you.
References

put update_excel ("Report","Test ID", 2039)
## after first run the "Test ID" is 2040 
to update_excel with whichXLSht , which_column, which_value
	Set xlFile to resourcepath("ExcelTest.xlsx")
	Set xlSheet to table whichXLSht of {type:"excel", file: xlFile, writeable: yes}

	Set curr_rec  to the record of xlSheet where (which_column) is (which_value) by reference
	-- At this point, I can see all other column's values properly in the watch window
	
	--now updating like this
	-- Both these below lines are not working as expected. It is just adding a new column in the 
	--name of "which_column" but before executing these lines, my which_column variable is
	--having a valid already existing column in the excel sheet
	set myNewValue to which_value+1
	set (which_column) of curr_rec to myNewValue
	put curr_rec
	put -
	put the record of xlSheet where (which_column) is (myNewValue)
	#set curr_rec's (which_column)  to which_value
End update_excel

my example Excel sheet for reference

Test ID	Date	Time	Duration	Model/Suite Name	Test Case Name	Test Case Description	Status
2039	2023-08-23	06:41:01	00:00:25	rb1051751	RBTest	Short RB test	PASSED