My code is quite a bit slimmer:
// Assumption is that your spreadsheet is formatted with a header row which contains your per patient variables e.g. MRN, FirstName, LastName
// ZephyrID is a script number in our repository. Our Excel files incorporate the script ID into the name of the file. Presumes Excel files are stored in your Resource folder.
put "data_" & global ZephyrID & ".xlsx" into datafilename
// Set the specified variable, myScriptData, to store the contents of the Excel file
set myScriptData to (type:"excel", file:ResourcePath(datafilename))
// This fetches all records from the myScriptData variable and places them into the testpatients variable. Note this variable name is plural to distinguish it from the record-by-record variable. Plural is not necessary, but different variable name is.
put the records of myScriptData into global testpatients
// counts records in Excel file. Used only for verifying that entire file was read.
put the number of records of myScriptData into global recordcount
//Call each record one at a time and perform a scripted activity. In this case log the patient’s MRN. Note the two variables: testpatient and testpatients
repeat with each item global testpatient of global testpatients
log global testpatient.mrn
– This is the secret sauce. .mrn represents the mrn column of the spreadsheet. You can call the value of any column in the record by the variable you define in the header.
end repeat
– No EOF or incrementing necessary. When reading from an Excel database, the script is aware of which record is last.