(Covers: Introduction, Use, Technical Comments)

Quite a number of users have in the past approached us asking whether we could provide a facility whereby they could carry out an analysis on a large file of existing case sizes to see whether their current palletisation of those cases was optimal, hence identifying those cases for which palletisation could be improved. The inefficient cases could then be analysed individually using PALLETMANAGER Palletise to determine appropriate optimum stacking plans for each case.

This release of PALLETMANAGER includes in the installation folder 2 spreadsheet files containing links to the PALLETMANAGER calculation routines and enables you to use two special functions PACK1210(a,b) and PACK(A,B,C,D) which between them (as described below), allow you to calculate the optimum number of cases / pallet layer on any pallet size and hence the optimum number of cases / pallet. As the routines link to PALLETMANAGER they MUST be run on a spreadsheet located in the PALLETMANAGER installation folder.

The routines were developed for the latest Excel version (Office 365, v16-32bit) and the file PMSPREADSHEET2.XLSM (which is located in and must be run in the PALLETMANAGER install folder) is designed to work with this version of Excel but is also intended to work with earlier versions of Excel (32 bit) - 2007, 2010, 2013 as well as 2016 (v16). In addition we have supplied a .XLS version which works fine with Office 2000 (and we believe other Excel versions from 2000-2007).

17.2 Using the Additional Functions.

When the appropriate spreadsheet file is opened please ensure that you do Enable Macros so that the functions are available to you. The spreadsheet file can be saved with another name but MUST be saved away in the PALLETMANAGER installation folder UNLESS YOU CREATE A SPECIAL FOLDER CONTAINING COPIES OF SPECIFIC PALLETMANAGER files as detailed at the end of Section 17.3.

PLEASE BE AWARE that the two spreadsheet files referenced above will be over-written when installing / re-installing PALLETMANAGER and thus you MUST save you own spreadsheet files with a different name to the supplied files.

As you will be aware Excel comes with a large number of inbuilt functions. The Excel files detailed above add additional macros to link to PALLETMANAGER calculation routines and add two additional functions to Excel. These are:

PACK1210(a,b) which calculates the maximum number of cases of size a*b which can be fitted onto a single layer on a 1200*1000 pallet

PACK(a,b,c,d) which calculates the maximum number of cases of size a*b which can be fitted onto a single layer on a pallet of size c*d.

Thus in the example below cell E16 contains the formula =PACK1210(A17,B17)

Which returns the maximum number of cases of size 350*100 which can form a layer on a 1200*1000 pallet

Likewise cell E18 contains the formula =PACK(A17,B17,A16,B16) which returns the same result.

Obviously this latter function can be used to calculate optimum packing on (say) a 1200*800 pallet etc.

Values for pallet size and case size can be integer (350) or real (e.g. 350.6).

AS THE FUNCTIONS LINK TO PALLETMANAGER THE SPREADSHEET (or copies thereof) MUST BE PLACED IN THE PALLETMANAGER folder. PLEASE BE AWARE that the two spreadsheet files referenced above will be over-written when installing / re-installing PALLETMANAGER and thus you MUST save you own spreadsheet files with a different name to the supplied files.

Given the number of cases that can form a single layer one can obviously determine the number of layers high for the pallet stack given the loading height and the product height.

Thus a user might copy data into the spreadsheet of case length, width and height for a large number of case designs (with perhaps the current number of cases packed / pallet) and then use the new routines to identify those products for which the current load plan is sub-optimal. PALLETMANAGER can then be used in the normal fashion to identify suitable optimal load plans.

17.3 Technical Issues.

As described earlier Excel has (at the time of writing) been subject to 7 version updates since the year 2000, and to complicate matters further many of these releases may be installed in either a 32 bit form or in a few instances a 64 bit form, further expanding the number of different configurations. We have developed our PALLETMANAGER link routines using initially the latest version of Excel (Office 365 - v16 - 32 bit) and subsequently for Office 2000 version of Excel (v9). We have also tested on Office 2010 (32 bit) and Office 2013 (32 bit). The file PMSPREADSHEET2.XLSM is designed for use with Excel 2007 (v12) and onwards whilst the file PMSPREADSHEET.XLS is intended primarily for earlier versions of Excel. At this time we are not supporting 64 bit versions of Excel as the 64 bit version does (as documented by Microsoft) have some limitations and compatibility issues and is really only required for those working with enterprise-scale Excel workbooks with complex calculations and many pivot tables.

A few technical tips are given below:

• If you browse Excel files within file explorer and double click on one of these to load the file into Excel then in some situations you may find the file opens as read-only. It is suggested that you always load files from WITHIN Excel using File / Open.

• Please ensure that you do Enable Macros if Excel prompts you for this.

• If you receive an error message 'Compile error: The code in this project must be updated for use on 64 bit systems' then we are afraid you must be using a 64 bit version of Excel (see above).

• If you try to load the .XLSM version into an old Excel version then even it loads it may well not function and may well also be set by Excel as read only. Users of older versions of Excel should use the .XLS version.

•  

We will be happy to try to assist users in trying to get these routines working on other versions of Excel if required. 

IF YOU WISH TO RUN OR STORE SPREADSHEETS IN AN ALTERNATE FOLDER then you should create that folder and COPY specific files from the fully functioning PALLETMANAGER folder to that specially created folder. These files are:

LICENCE.PM

MATHFUNC.DLL

PACKFUNC.XLS

PMSPREADSHEET.XLS

PMSPREADSHEET2.XLSM

SA0421.EXE

and SALFLIBC.DLL