Excel file - Records are not getting updated/saved

I have the below code expecting to update the values in column C, but I can see only the first record getting updated as in screenshot, but not others. Is there anything that I should add here? Appreciate any inputs.

Set xlFile to ResourcePath("my_file.xlsx")
Set xlSht to table "Sheet1" of {type:"excel", file: xlFile, writeable:"yes"}

Repeat with each item current_record of the records of xlSht where ColumnA is not empty
      set current_record's ColumnC to "my updates here"
End repeat	

My excel sheet looks like this after the update:
image

Hi scsvel,

Try this

Set xlFile to ResourcePath("my_file.xlsx")
Set xlSht to table "Sheet1" of {type:"excel", file: xlFile, writeable:"yes"}

Repeat with each item current_record of the records of xlSht where ColumnA is not empty
	set current_record to the record of xlSht where _pk_ is current_record._pk_
	set current_record.ColumnC to "my updates here"
End repeat
1 Like

Hey @scsvel,

The reason is for what you are experiencing is that a repeat loop “detaches” from the record. If you want it to work you need to add by reference at the end of your repeat.
This is by design and intended but not very obvious.
References to Containers | EPF Docs (eggplantsoftware.com)

This suite and video might be useful. It was “eye opening for me” :slight_smile:
ReferencesDemo.suite GitHub
Keysight Eggplant SenseTalk References (youtube.com)

Set xlFile to ResourcePath("my_file.xlsx")
Set xlSht to table "Sheet1" of {type:"excel", file: xlFile, writeable:"yes"}

Repeat with each item current_record of the records of xlSht where ColumnA is not empty by reference
      set current_record's ColumnC to "my updates here"
End repeat

@Javi: Your code works because you create a direct reference within you first set command. Its not needed when you use the “by reference” and you can use the Iterator variable current_record.

Hope this is helpful and let me know in case you do have further questions.

Cheers,
Karsten

1 Like