Optimizing Excel Operation for Allevo Multi

BUFFER_MULTI

NOTE:

The following considerations apply basically only to Excel format XLS, the standard file format until Office version 2003. Excel there has different limits with regard to processing worksheets and data. In our experience, the constant BUFFER_MULTI is no longer required when working with XLSM format.

To create several worksheets in one Excel file means an additional load for the Excel memory. This load is maintained throughout the whole Excel session, slowing down the processing speed or even leading to a complete break-down of the process. The memory is only emptied when the Excel file is closed.

The capacity of the Excel memory (HEAP) depends on the Excel version, and on the Office environment used.

Depending on the number of worksheets created for a certain object group (e.g. cost centers) it is possible that the memory “overflows” and that processing is stopped (critical number). Experience shows that this critical number is reached from between 25 and 30 worksheets (depending on the size of the Allevo planning layout). This effect has nothing to do with the Allevo program itself but is related to the Office environment used.

Allevo is offering three processing variants that can be controlled via the BUFFER_MULTI constant. All these procedures are working with a periodical closing down and re-opening of the Excel file, which is the only way to empty the memory.

When you are using one of the following processing variants, please note that depending on the security level selected in Excel (via Excel - Macro - Security) the prompt for confirmation of the macro may appear several times in succession and must be confirmed accordingly when Allevo is being opened.

§ Intermediate storage of the file when the actual MultiFile is being created: Value from = I Value to = 10
In this example, the Excel file is saved (closed) after object 10, and then re-opened in the background.

§ Intermediate storage and final storage of the file when the actual MultiFile is being created: Value from = J Value to = 10
Intermediate storage is done after the 10th object. A final storage is done when processing is completed for all objects.

§ Final storage of the file when the actual MultiFile is being created: Value from = Z Value to = 10
Final storage is done as soon as Value to is exceeded. This method only applies if the total number of objects per group does not exceed the critical number.

NOTE: When using Allevo Multi please be aware of Excel’s (factual) capacity limits. The easiest way to cope with this problem is to limit the number of cost centers (best < 20) for each individual cost center group.