I have a couple Excel Workbooks with one serving as the local data entry Workbook and the other as the location for aggregating all the data that has been entered. The local Workbook also has a pivot table that uses an external data connection to the second Workbook as its data source.
The local Workbook has some VBA macros that are supposed to open the remote Workbook, write to or read from it, save it and close it. However, the act of refreshing the pivot table data locks the source file for editing and it stays locked until I close the Workbook with the pivot table. This prevents any macro that would edit the remote Workbook from working successfully (as it forced the macro to open the Workbook as read-only).
Is there a way to prevent the pivot table from locking the source file for any longer than it takes to refresh the data?
There are no other actual users accessing these Workbooks, and the data in the source workbook is only 20 rows and 11 columns worth and it is currently stored on the local drive (so it can't be that the connection is too slow or unavailable).
