Pivot table force nightly refresh, so users can manipulate Pivots instantly? RRS feed

  • Question

  • Hello

    I am attempting to write a vba macro within a xlsxm file which contains pivot tables only (no cache, or data page etc)

    The data is linked via a .mdb file. The aim is to open and refresh the pivots contained in the xlsm file nightly after the .mdb data source has refreshed. At which point the users can manipulate the pivot tables without the "refresh on open" gui option with is currently setup.

    The whole point is to allow users to open the .xlsm file, and then edit/change drop downs in the pivot and have data refreshed instantly(since it was done last night ).

    Code runs and refreshes fine then save, but once i open re-open the file, hoping to adjust paramaters, i always get prompted

    "The PivotTable report was saved without the underlying data. Use the refresh Data Command to update the report."

    In a seperate test case, I tried removing via gui the "refresh data when opening the file" option, to replicate via gui the same strategy as my code, but even after refreshing and saving this way, i still get the prompt?

    Is the functionality im looking for possible?

    Thanks for the help

    Dim Sheet As Worksheet, Pivot As PivotTable
    For Each Sheet In ThisWorkbook.Worksheets
       For Each Pivot In Sheet.PivotTables

    Friday, October 2, 2015 6:29 PM

All replies

  • in vba, "Pivot.SaveData = True" would save the data with the workbook, which prevents the popup, but then my 1mb file jumps to 20mb....which is not a desired outcome.

    • Proposed as answer by ryguy72 Saturday, October 10, 2015 2:12 PM
    Friday, October 2, 2015 7:51 PM
  • The pivot table either has to refresh to get data or the data has to be saved in the file.

    One possibility is to summarize the data as much as possible in the database so the amount of data to save is much less than your 20Mb. Then you can save the data in the workbook.

    Rod Gill
    Author of the one and only Project VBA Book

    • Proposed as answer by ryguy72 Saturday, October 10, 2015 2:12 PM
    Friday, October 2, 2015 10:13 PM