Working with excel rows and columns

Eggplant Functional
#1

Hi everyone,

If anybody can help with providing a solution to this (hopefully simple) request.

I am able to read text on the SUT and determine if it is in a column of a resource (xlsx). What I cannot figure out though is this for example;

The keyword I have found using readtext is also located in my resource excel file (xlsx) at column A row 4. I would then like to reply to that word using typetext but with the word adjacent to it in the excel file such as column B row 4.

So basically im looking for a word on the screen that is within my spreadsheet in column A, and then reply with the corresponding word in the row but on column B.

Regards,

#2

How about using SenseTalk’s built in function to read Excel spreadsheet?

#3

first find and Click in column A row 4,then run the following script:
TypeText Tab
TypeText ControlKey, “a”
TypeText ControlKey, “c”
return the remoteClipBoard

#4

Hi Dan,
The format of the Excel will define how best to approach this problem. As @Keiichi_Yamamoto mentioned, SenseTalk has native functionality for working with Excel either as a database or working cell by cell. I prefer the former, but it only applies if your column headers (Row 1) represent fields (e.g. FirstName, LastName, MI), and each row represents a record. If that is NOT the case, you will reference the sheet by cell. Which format is your data in?
Thanks,
Dave

#5

Thank you all for the input, I went with the approach of using the cell by cell function. The code I ended up successfully using is shown below but if anybody can see anyway to improve it then by all means let me know.

set MyExcelFile to Workbook(ResourcePath(“responses_replies.xlsx”))
set worksheet to Worksheet(MyExcelFile, 1)
put cellRange(worksheet, “A1:A4”) into question

put readText([“ReadRoom_A”, “ReadRoom_B”] into B
put the last word of B into C
log C

if the first item of question contains C then
typetext cell(worksheet, “B1”)
end if
if the second item of question contains C then
typetext cell(worksheet, “B2”)
end if
if the third item of question contains C then
typetext cell(worksheet, “B3”)
end if
if the fourth item of question contains C then
typetext cell(worksheet, “B4”)
end if

…and so on

#6

I think you could replace all of your if statements with something along these lines:

put cellRange(worksheet, "B") into B

put the index number of the item containing C in question into questionNumber
put item questionNumber of B into reply
typeText reply