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.
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:
|< > / | \ : “ * ? [ ]|
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.
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:
Any cell set by a macro and referenced by a CHOICE LIST must match the list of choices exactly, including case.
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.
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:
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.
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.
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:
This way, when the macro is run, the specified sheet is focused and the rest of the macro can run as intended.
Certain functions of EASA related to Excel do not support overlapping ranges. Please insure: