locked
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.

    Thanks,

    Bob

    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
    Answerer