There appears to be another bug with Excel when it comes to Export Templates.
The previous issue where the Export Wizard would hang when opening an Excel Macro workbook created with Excel 2010 has been fixed - I’ve confirmed this with TDP 18.104.22.1682, so that’s awesome.
This issue is somewhat different and annoyingly hard to nail down. I’ve only observed it consistently with Excel Macro files that have more than one (1) Pivot table. None of my Excel Macro files that only have one (1) Pivot table seem to experience the problem.
The issue is this - occasionally, it appears that after (or during; can’t really tell which) the export of data from an export template to an Excel 2010 Macro Workbook that has more than one (1) Pivot table, either TDA (observed in 3.1) or TDP (observed in 22.214.171.1242) or Excel fails to properly finish the execution of the macros or close the file.
This has been observed by an orphan Excel process and a corresponding lock file in the directory with the Excel Macro Workbook template file.
The macros in question aren’t complicated - one simply clears the existing data while preserving the formatting of the header on the “source” worksheet; another formats the data (font, size, auto-sizes columns/rows), and the last simply hides the Pivot table field listing. It should be noted that these exact same Macros are used in the files that only have one (1) Pivot table, but which have no problem.
Generally I can get this to work once or twice, maybe even three times. Eventually, however, during the execution of the Export Template I’ll get a message that says (paraphrasing) “Cannot continue… destination file blah.xlsm is currently in use by another process.” I’ve double and triple-checked so many times at this point that I’m blind. No other process is in use of that file up until the Export Template process starts. It seems like it opens the file, and then “forgets” which Excel process (even if there is only one running - the one it started) it was using and can’t continue.
Anyone else run into this?
I’ve fudged around this in the past by exporting the raw data and making the EU do their own pivot, but I’ve got a Senior VP who needs a daily report with two pivot tables so I can’t quite do that any more.