Using CVTDBFXL with Excel Pivot Tables

CoolSpools will not create pivot tables but it can be used to populate them as follows:

1. Use CoolSpools to create the 'base' spreadsheet.

CVTDBFXL FROMFILE(...)
TOSTMF('pivot.xlsx')
STMFOPT(*REPLACE)
EXCEL(*XLSX *MM *HMS Data)
Note: the sheet as been called 'Data'

2. Add a new sheet for your pivot table referring to the 'Data'

3. To refresh the 'Data' sheet use the following:

CVTDBFXL FROMFILE(...)
TOSTMF('pivot.xlsx')
STMFOPT(*RPLXLSSHT)
RPLXLSSHT(Data)
EXCEL(*XLSX *MM *HMS Data)

Note: You will need to create an environment variable to prevent the Data sheet name being appended with a number.

ADDENVVAR ENVVAR(SL_XLS_IGN_RPL_SHEETS)
VALUE(*YES)
LEVEL(*JOB)

ADDENVVAR ENVVAR(SL_XLS_IGN_RPL_SHEETS)
VALUE(*YES)
LEVEL(*SYS)

Note: Some users have experienced issues with recent releases of Excel reporting a corrupt workbook after using STMFOPT(*RPLXLSSHT).  This issue has been addressed in CoolSpools Version 7 - should you encounter the issue we recommend updating to the latest CoolSpools fix pack level.