Excel - Open/Create workbook

Declaration

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

Related Topics   

Description

Opens an existing Microsoft Excel workbook or creates a new workbook and establishes a unique session name in which subsequent Excel activities can link to. A session is exceptionally useful as a means of organizing and encapsulating a large collection of activities required to accomplish a particular task/operation. If creating a new workbook, its appearance can be based on an existing workbook template or according to your Microsoft Excel preferences currently set.

IMPORTANT: Automate Desktop's Excel activities rely on the Microsoft Excel engine to perform their work, therefore, MS Excel must be licensed and installed on the system to ensure proper functionality. As an alternative, there might be an equivalent OpenDocument Spreadsheet (ODS) activity that you can use. Unlike the Excel activities, ODS activities enable creation and management of spreadsheets without requiring Microsoft Excel to be installed on the system.

Practical usage

Commonly used to create a new Excel session in which other Excel-related activities can link 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.

Parameters

Session

Property Type Required Default Markup Description
Get document by Text (options) Yes (Empty)
  • CONNECTBY="open_workbook"
  • CONNECTBY="create_workbook"
 Indicates where the source Excel document originates from. The available options are:
  • Open (default) - The source is an existing Excel document open 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.
NOTE: 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 file name of the Excel document in which to open. If the Get document by parameter is set to Create, specifies the path and file name that the newly created Excel document will be saved to.
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 file name 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 selected, the saved workbook will overwrite any workbook with the same name that exists in the destination. If disabled (default), an existing workbook with the same name will not be overwritten, however, an error will occur during runtime as a result of matching file names. This parameter is active only if the On completion parameter is set to Save as.
Application is invisible Yes/No No No VISIBLE="NO" If selected, specifies that the Excel window will not be displayed and all processing will occur in the background. This option should be disabled during task construction and debugging but can be selected for production. The workbook is visible by default.

Description

Error Causes

On Error

Example

NOTE:
  • Copy and paste the sample AML code below directly into the Task Builder Steps Panel.
  • To successfully run the sample code, update parameters containing user credentials, files, file paths, or other information specific to the task to match your environment.

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.

Copy 
<AMEXCEL SESSION="ExcelSession1" WORKBOOK="C:\Temp\myExcelDoc.xlsx " />
<AMEXCEL ACTIVITY="get_cell" SESSION="ExcelSession1" ACTION="rangebyposition" RESULTDATASET="mydataset" STARTCELLROW="1" STARTCELLCOLUMN="1" ENDCELLROW="3" ENDCELLCOLUMN="1" />
<AMLOOP ACTIVITY="dataset" DATASET="mydataset" />
<AMSHOWDIALOG>The value of row %mydataset.CurrentRow%, Column A is %mydataset.Column1%</AMSHOWDIALOG>
<AMLOOP ACTIVITY="end" />
<AMEXCEL ACTIVITY="close_workbook" SESSION="ExcelSession1" SAVETYPE="do_not_save" />