I work a lot with exporting data to Excel files that contains pivot tables, and have been working through a few issues after upgrading to TDP 4.0. I wanted to share an additional issue that popped up and how I resolved it. Many of my Excel docs are macro-enabled. I came across 1 issue today where my automation script would hang and issue a message stating that the Excel file was locked. I realized that this was due to 1 of my pivot tables that had the option checked to refresh data when opening the file. I set my pivot tables to not refresh on opening the file, and changed to using a macro to refresh the pivot table cache. Here is the code I used:
With Sheets(“Sheet name”).PivotTables(1)
I set this macro to run after export.