Save Workbook with Pivot table data snapshot

[ICCIIT]

I have a Excel Workbook with multiple Pivot tables, the data source is an ODBC connection back to an Access database table which is refreshed each month. The pivot data is not stored in the Workbook.

Each month I download & update the data in the database and then refresh the Excel workbook so that all pivots are updated.

What I need to do is then save a "monthly" version of the workbook containing a snapshot of the data relevant to that particular month (to enable drill down) and disconnect the ODBC to stop it from being accidentally refreshed by a user at a later date to the current data in the database.

Any suggestions appreciated.

Thanks

 

[SkipVought]

hi,

Copy each PT and then Edit > Paste Special -- VALUES

You can also Paste Special FORMATS.

Use your Macro Recorder to help with this recurring task.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ICCIIT]

Hi Skip
Thats ok for retaining the original PT summary view but if the user needs to drill down on a value, the underlying data cannot be be re-queried, this is why I was looking to save a snapshot of the data via the ODBC in to the workbook before archiving the file.

It looks like I will have to copy the data down in to the workbook from the start and not use ODBC connection.

 

[SkipVought]

It looks like I will have to copy the data down in to the workbook ...

Heavens NO!!!

Simply change the query FROM a Pivot Table result to a Simple TABLE result. THEN from THAT table, do your Pivot!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ICCIIT]

Skip
I think I am already doing what you suggest, the data is in an access flat/simple table. The ODBC connects to this table.

The table gets overwritten each month with the next set of data and therefore the historic data is lost to the PT reports in the Workbook but I need to retain a copy of the workbook each month.

This is why I think I need to actually put the data in the workbook but was not sure if there was a way to just "save as" and include the data and disconnect the ODBC.

Thanks

 

[SkipVought]

I think I am already doing what you suggest

I don't think so. If you can, "accidentally refreshed ... to the current data in the database" then your PT is directly connected!

I am suggesting an ISOLATION, where, for instance, Sheet1 contains a QueryTable that can refresh on demand and Sheet2 conatins the PT that uses the Table on Sheet1 as its source data. Sheet1 can be HIDDEN and unavailable to the users, so that YOU or YOUR CODE ONLY can refresh to the next period's data at the appropriate time.

When you save/acchive a copy of the workbook, the PT can still be used to manipulate the data in Sheet1, which is virtually FROZEN.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}