Excel 2007 VBA - Determine Refresh state of underlying data in pivot table RRS feed

  • Question

  • I have pivots linked to very large Access 2007 tables that take several minutes to update - particularly on the slower machines in the office.

    To contain the Excel file size, the "Save source data with file" option is de-selected

    The source data refreshes irregularly, and due to the nature of the source data, auto-update would not be useful (it would break more stuff than its convenience would solve)

    I have code to manipulate the filters on these pivots - but it fails without warning if the pivot isn't refreshed.  If you manually go to the pivots and select a pivot field to filter, the warning "The PivotTable report was saved without underlying data.  Use the Refresh Data command to update the report."  appears.

    How can I test this state in VBA?  

    I only want to execute a refresh if multiple conditions apply - this being one.



    Thursday, December 12, 2013 9:29 PM

All replies

  • Pivot Table has a property RefreshDate which returns Date and time when last refreshed. You can check that ...

    Best Regards,
    Asadulla Javed, Kolkata
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Friday, December 13, 2013 5:52 AM