Pivot table Refresh on File Open
Posted on 2010-08-27
I am using Excel 2007. I have a Pivot Table that is getting External data from SQL Server via Microsoft Query. This uses a named connection and that connection has properties, including a Command Text Property. This contains the query string that pulling data in In my workbook on sheet1 which I named All_Data.
I have my pivot table on Sheet2 which I named Pivot. Source for my pivot table is Sheet1 – All_data and used dynamic range which I named Pivot_Range.
What I need is how to refresh my workbook on file open. I assume that when Sheet1- All_Data is refreshed that my pivot table on Sheet2- Pivot will be refreshed automatically.
I got some suggestion from
Private Sub Workbook_Open()
Right click on pivot table
click PivotTable options
click on the Data tab
check the box "Refresh data when opening file"
However they not working.
I tried this too:
And on first line I got Run time error ‘9’ Subscript Out of range.
How to got this working in this scenario.
And how to get this working in scenario when I have just one spreadsheet, that have just pivot table on in my case worksheet “Pivot” with out having worksheet All_Data which I use as source for my pivot table.