Export to Excel Automation Activity with Excel Pivot Tables - file locked

Toad Data Point & Toad Intelligence Central
1

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:

Sub macro_name()
With Sheets(“Sheet name”).PivotTables(1)
.PivotCache.Refresh
End With
End Sub

I set this macro to run after export.

2

Thats interesting. Was the automation script using multiple activities to populate multiple worksheets?

3

I have 3 Export activities set up in parallel, each exporting to multiple sheets.