User Tools

Site Tools


Excel Workbook Preparation

Before starting to build an EASAP around a spreadsheet, there are a few preparation tasks that should be performed on the spreadsheet to minimize potential compatibility issues with EASA.


General Conventions

Choose a Suitable File Name

Once an Excel file is part of an EASAP, its file name should not be changed. Choose an appropriate, generic file name, avoiding the use of version numbers. An alternative way to use version numbering on spreadsheets could be to display the number in a cell.

Forbidden characters in file names are:

< > / | \ : “ * ? [ ]

Name All Cells to be Referenced

Cells that will be selected as inputs or outputs in range reference parameters should be given names. Additionally, cells in the first row or column of tabular data which are referenced in a READ EXCEL ACTION or WRITE EXCEL ACTION should be named.

Apply Proper Number Formats

In order for EASA to process input cells effectively, special attention should be paid to cell formatting in Excel. Please ensure that cells are formatted appropriately and take note of the following caveats:

  • Blank spaces should be formatted as General and should not have data validation.
  • Inputs without default values should not be formatted as General.
  • Text entry cells used solely as annotation may need to have a dummy formula added, referencing the cell to be identified as an input. For example “=text1”, where text1 is the name given to the cell.

Impose Consistent Use of Case

Any cell set by a macro and referenced by a CHOICE LIST must match the list of choices exactly, including case.

Protected Spreadsheets

EASA is capable of interacting with password protected spreadsheets (i.e. using Tools→General Options on the Save As form), provided that the password is known. If the password is known, then it can be entered into the Password: parameter of the protected SPREADSHEET or SHEET within EASAP Builder. If the password is not known to the author, then the protection must be removed prior to use with EASA.

In general if a workbook is run via an Excel Server there will be no need to protect the source file; we recommend any protection is removed from a workbook before integration with EASA.

In contrast, for an Excel Client EASAP the spreadsheet will need to be locked if it contains sensitive proprietary formulas, macros or data.


Eliminate Modal Forms

You should eliminate any pop-up modal forms that require an interactive response from a user under normal usage of the spreadsheet in Excel as a User will not see the spreadsheet itself and will not have the opportunity to click on any pop-up form. A common example is a password request set under Tools→Protection

Additionally, the following VBA calls will need to be re-implemented in EASA or removed altogether:

  • MsgBox
  • FileDialog
  • UserForm

One straightforward way to implement these changes while minimizing the changes to the original spreadsheet is to add a cell to the spreadsheet and set it to the string 'EASA' in the EASAP. Then an Author may write VBA code to disable macros or portions of macro code that include the above interactive functionality when the 'EASA' string is set, but the spreadsheet will function normally under normal interactive desktop usage where spreadsheet development, validation and testing is typically. Again, an Author will need to implement in the EASAP any essential functionality that is disabled during non-interactive use.

Remove arguments for a Macro, Macro must be 'Public'

Currently, the RUN MACRO ACTION cannot pass arguments to macros. To work around this limitation, you can remove arguments from the macro routine called and instead access the values previously sent as arguments by pointing to cell values somewhere in the spreadsheet. Then, the EASAP can supply new values by changing the values of the new cell ranges containing what used to be argument values with WRITE EXCEL ACTION. In addition, for RUN MACRO ACTION to work properly, the macro being run must be ‘Public’, which means it will be shown on the list of macros in Excel.

Macro Scope

Some macros work only if the workbook is open at a specific sheet. Since EASA opens the Excel process in the background, there's no way to manually select sheets. For these macros, it is necessary to adjust the VBA code so the correct sheet is selected every time the macro is run.

For example, if a macro is meant to be used on the sheet called Sheet_1, the following line should be added to the beginning of the macro's VBA code:

Sheets(Sheet_1).Activate

This way, when the macro is run, the specified sheet is focused and the rest of the macro can run as intended.


Avoid Overlapping Ranges

Certain functions of EASA related to Excel do not support overlapping ranges. Please insure:

  • Charts to be displayed do not have overlapping ranges
  • Ranges to be printed by EXPORT TO PDF functionality do not have overlapping ranges

Page Tools