As answered in microsoft.public.excel.misc and
If you create the pivot table using the other Excel file as external
source, you won't have to open the source file to update the pivot table.
To change the source, right-click on a cell in the pivot table, and
choose PivotTable Wizard
Click the Back button until you reach Step 1
Choose External data source, and click Next
Click Get Data, and connect to the source Excel file and table.