How do I access an Excel workbook on the SUT?

Eggplant Functional
#1

I need to access 2 cells in an Excel spreadsheet using Eggplant.

The spreadsheet is here - C:\Users\John\Documents\Dates,xlsx
The workbook is called Current
The cells I want are A2 and B2

When I try this:
set MyExcelFile to (“C:\Users\335339\Documents\Dates.xlsx”)
Set Worksheet1 to MyExcelFile.Worksheet(“Current”)

Eggplant tells me that there is no such file or directory for the Worksheet1 setting
If I do a “put MyExcelFile” after the first line it correctly responds with the right path and filename.

What am I missing?
Many thanks - Prunice

#2

Dear Prunic,
you are missing Type of the sheet: see the format as below.

set Inputdatadb to (type: excel, file: ResourcePath(Inputdatadb.xlsx))

#3

There are two different options for accessing data from Excel via SenseTalk. You can find more on them here. In short, if you plan to work with individual cells, you will want to begin with the Workbook function.

set MyExcelFile to Workbook(ResourcePath("TestCases.xlsx")) // Sets MyExcelFile as a reference variable for the TestCases.xlsx Excel file
set worksheet1 to MyExcelFile.Worksheet("Customer Budget") // Sets the worksheet named "Customer Budget" to worksheet1

I prefer treating Excel files as a database, and that is the syntax that @Rajat1981 is referencing:
set myExcelDB to {type:"excel", file:"C:\Projects\TestFiles\States.xlsx", name:"Counties"} -- Set the specified variable, myExcelDB, to store the contents of the referenced Excel file and establish a SenseTalk connection. Specify a worksheet named "Counties".

#6

I was meaning an excel file on the SUT, not on my Eggplant machine.

#7

File, folder, and database functions all run on the Developer(Agent) side of the two-system model. Here is how I would work through the manipulation of an Excel file visible on the SUT but not on the Agent:
Option 1 - Move the Excel file from its current location on the SUT into the Resources folder or some other location that is accessible from the Agent machine. This is the preferred method as it will make all of the Excel-native and the database functions available. Also, file interactions typically run faster and more reliably against the Agent. Moving the files can be done using Command Line (all OS) or File Explorer (Win). Alternately, you can use mapped letter drives or symbolic links to reach networked locations from the Agent.
Option 2 - Manipulate the Excel file directly on the SUT. This is the preferred method only when you have a brief, limited interaction with the file and are using the Excel app directly. The best example of this would be to open the file in Excel for the purpose of triggering a macro. In this case, you would treat Excel as the Application Under Test and code your script. If you need to use this method, your code might look like this:

//Presumes that code for opening Excel and opening the target file is already complete;
Click image:"Excel_NameBox"
TypeText "A2", Return
TypeText myA2_Value,Tabkey
TypeText myB2_Value, Tabkey
TypeText ControlKey,"s"
TypeText OptionKey, F4

Hope this helps,
Dave

#8

Hi Dave, this is exactly what I wanted, many thanks!!