Cells to Dataset Action

Declaration

<AMEXCELCELLSTODATASET SESSIONNAME="text" RESULTDATASET="text" STARTCELLROW="text" STARTCELLCOLUMN="text" ENDCELLROW="text" ENDCELLCOLUMN="text" STARTCELLREF="text ENDCELLREF="text" WORKSHEET="text">

See Also

Create Workbook, Open Workbook, Close Workbook, Add Worksheet, Activate Worksheet, Get Cell, Set Cell,  Dataset to Cells

Description

Populates a dataset with a selected range of cells in an established Excel session.

Practical Usage

To retrieve the contents of a range of cells in a Microsoft Excel document that was previously created or opened using AutoMate's Excel actions and populate the values into an AutoMate dataset. This action is only useful in conjunction with the other Excel actions found in AutoMate.

IMPORTANT: The Excel actions in AutoMate use the Microsoft Excel automation engine to perform their work. Because of this, Microsoft Excel must be licensed and installed on the system to use the Excel actions.

Parameters

General Tab

Session Name

Text, Optional - Default "Default"
MARKUP: SESSION="YES"

Specifies the session name to identify which document should be changed from prior Excel steps. This allows several Excel files to be open simultaneously. If working with only one document, this value should not be changed from it's default value.

Create and Populate Dataset

Variable, Required
MARKUP: RESULTDATASET="datasetname"

Specifies name of the AutoMate dataset that should be populated with the contents of the desired cells at runtime.

Start Row

Text, Optional Default ""
MARKUP: STARTCELLROW="3"

Specifies the start row of the cells where the data should be retrieved. If specified, STARTCELLCOLUMN, ENDCELLCOLUMN and ENDCELLROW must also be specified.

End Row

Text, Optional Default ""
MARKUP: ENDCELLROW="3"

Specifies the end  row of the cells where the data should be retrieved. If specified,  STARTCELLCOLUMN, ENDCELLCOLUMN and STARTCELLROW must also be specified.

Start Column

Text, Optional Default ""
MARKUP: STARTCELLCOLUMN="2"

Specifies the start column of the cells where the data should be retrieved. If specified,  ENDCELLCOLUMN, STARTCELLROW and ENDCELLROW must also be specified.

End Column

Text, Optional Default ""
MARKUP: ENDCELLCOLUMN="2"

Specifies the end column of the cells where the data should be retrieved. If specified,  STARTCELLCOLUMN, STARTCELLROW and ENDCELLROW must also be specified.

Start Cell Reference

Text, Optional Default ""
MARKUP: STARTCELLREF="B3"

Specifies the start cell reference of the cells where the data should be retrieved. This is an alternate method to specifying the row and column of the desired cell.  If specified, STARTCELLCOLUMN, ENDCELLCOLUMN,  STARTCELLROW and ENDCELLROW are ignored. If specified,  ENDCELLREF must also be specified.

End Cell Reference

Text, Optional Default ""
MARKUP: ENDCELLREF="B3"

Specifies the end cell reference of the cells where the data should be retrieved. This is an alternate method to specifying the row and column of the desired cell.  If specified, STARTCELLCOLUMN, ENDCELLCOLUMN,  STARTCELLROW and ENDCELLROW are ignored. If specified,  STARTCELLREF must also be specified.

Worksheet Name

Text, Optional Default ""
MARKUP: WORKSHEET="My Worksheet Name"

Specifies the name of the new worksheet that the text will be retrieved from. If left blank or omitted, the current active worksheet will be used.

Notes

A variable field Dataset is generated

A dataset is a multiple column, multiple row container object. This action creates and populates a dataset, the fields contained within that dataset are determined by the query that was executed. For example if the following query is executed:

SELECT firstname, lastname, company from customer where city='Los Angeles';

Then the following data set would be generated:

datasetname
|--firstname

|--lastname

|--company


along with the standard fields included in every dataset

|--CurrentRow

|--TotalRows

|--TotalColumns

|--ExecutionDate

|--RowsAffected

|--SQLQuery

|--Datasource

A record (row) is created for each record (row) that is retrieved from the server. To access this data use the Loop Dataset Action <AMLOOPDATASET> to loop through the records, inside the loop you can extract the data from the field of your choice (from the current record) by using an embedded expression such as the one that follows:

%mydatasetname.firstname%

or you could combine two fields together like this:

%mydatasetname.firstname + " " + mydatasetname.lastname%

Embedded Expressions such as these can be used in any parameter in any action. So, to display the data in a message box the AML code would look like this:

 <AMMESSAGEBOX MESSAGETEXT="%mydatasetname.firstname%" WINDOWTITLE="The firstname of the current record is">

At runtime the text %mydatasetname.firstname% is replaced by the contents of the subject of the current record.

More on datasets

Custom Description

This action includes the Description tab for entering a custom step description.

More on setting custom step description

Standard Error Handling Options

This action also includes the standard Error Causes and On Error failure handling options/tabs.

More on Error Handling Options

Variables and Expressions

All text fields allow the use of expressions, which can be entered by surrounding the expression in percentage signs (example: %MYVARIABLE%, %Left('Text',2)%). To help construct these expressions, you can open Expression Builder from these fields by pressing F2.

More on variables
More on expressions

More on the expression builder

Example

NOTE: The code below can be copied and pasted directly into the Steps pane of the Task Builder.

<AMEXCELOPENWORKBOOK WORKBOOK="c:\excellfile.xls">

     <AMEXCELCELLSTODATASET RESULTDATASET="customers" STARTCELLREF="A1" ENDCELLREF="Z10">

<AMEXCELCLOSEWORKBOOK>