Excel - Create/Open workbook

Declaration

<AMEXCEL SESSION="text" CONNECTBY="text (options)" 
WORKBOOK="text" VISIBLE="YES/NO" OVERWRITE="YES/NO" 
TEMPLATENAME="text" />

Description: Opens or creates a Microsoft Excel workbook and establishes a unique session name in which subsequent Excel activities can link to. >A session is particularly useful as a means of organizing and encapsulating a collection of activities required to accomplish a particular operation.

IMPORTANT: Excel activities rely on Microsoft's Excel engine to perform their work,therefore, MS Excel must be licensed and installed on the system in order for these activities to function properly.

Practical Usage

Commonly used to create a new session in which other activities can be linked to. Sessions typically represent a combination of steps that are used to complete a specific process. Creating sessions enables you to build and organize more customized tasks. Additionally, sessions can eliminate redundant entry of identical parameters that may be required by several activities.

Session Parameters

Property

Type

Required

Default

Markup

Description

Get document by

Text (options)

Yes

(Empty)

  1. CONNECTBY="open_workbook"

  2. CONNECTBY="create_workbook"

Indicates where the source Excel document should originate from. The available options are:

  • Open (default) - The source will be an existing Excel document which will be opened at runtime.

  • Create - The source will be a newly created Excel document.

Session

Text

Yes

ExcelSess1

SESSION="ExcelSess1"

The session name to be created. This allows subsequent Excel activities to be linked to this session. Numerous sessions can exist within a single task allowing several Excel documents to be open simultaneously. The default session name is Excelsess1.

N font-weight: bold;">OTE: To end a session, use the Excel - Close workbook activity.

File name

Text

Yes

(Empty)

WORKBOOK="C:\Temp\file.xls"

If the Get document by parameter is set to Open, specifies the path and filename of the Excel document in which to open. If the Get document by parameter is set to Create, specifies the path and filename that the newly created Excel document will be saved to.

Application is invisible

Yes/No

No

No

VISIBLE="NO"

If set to YES, specifies that the Excel window will not be displayed and all processing will occur in the background. This option should be set to NO during task construction and debugging but can be set to YES for production. The workbook is visible by default.

Password to open (optional)

Text

No

(Empty)

OPENPASSWORD="encrypted"

The password required to open the Excel document if it is password protected. This parameter is active only if the Get document by parameter is set to Open.

Password to modify (optional)

Text

No

(Empty)

MODIFYPASSWORD="encrypted"

The password required to modify the Excel document if it is password protected. This parameter is active only if the Get document by parameter is set to Open.

Create from template

Text

No

No

WORKBOOKTEMPLATE="C:\Temp\file.xls"

If enabled, specifies the path and filename of a template file that already has preferred formatting in which to base the new workbook on. A template can include formatting, styles,  standardized text such as page headers and row and column labels, formulas, macros and custom toolbars. This parameter is active only if the Get document by parameter is set to Create. It is disabled by default.

Overwrite if workbook already exists

Yes/No

No

No

OVERWRITE="yes"

If set to YES, the newly created workbook will overwrite an existing workbook with the same name, If set to NO, an error will occur during runtime as a result of a matching filename. This parameter is active only if the Get document by parameter is set to Create.

Description tab - A custom description can be provided on the Description tab to convey additional information or share special notes about a task step.

Error Causes tab - Specify how this step should behave upon the occurrence of an error. (Refer to Task Builder > Error Causes Tab for details.)

On Error tab - Specify what AWE should do if this step encounters an error as defined on the Error Causes tab. (Refer to Task Builder > On Error Tab for details.)

Example

The sample AML code below can be copied and pasted directly into the Steps panel of the Task Builder.

Description - This sample task opens an existing spreadsheet, retrieves the data contained in the first 3 rows of column A and populates a dataset with the results. The data is then looped through. A message box is contained inside the loop to display the value of the current row. In order for this task to work, make sure to edit the properties of the 'Open Workbook' action (step 1) to point to a valid Excel file that contains data in the first 3 rows and columns.

<AMEXCELOPENWORKBOOK WORKBOOK="C:\Temp\myexceldocument.xlsx" />
<AMEXCELCELLSTODATASET RESULTDATASET="mydataset" 
STARTCELLROW="1" STARTCELLCOLUMN="1" ENDCELLROW="3" 
ENDCELLCOLUMN="1" />
<AMLOOP TYPE="DATASET" DATASET="mydataset">
<AMSHOWDIALOG>The value of row %mydataset.CurrentRow%, 
column A is %mydataset.A%.
</AMSHOWDIALOG>
</AMLOOP>
<AMEXCELCLOSEWORKBOOK SAVE="NO" />