Change pivot cache data source

[Jon von der Heyden]

Hi All

I have a workbook that contains several macros. One slices and dices data from another workbook. The next one will upload that table to an access database. Then another macro to pass parameters to a query in that access database.

The workbook then contains several pivot tables from that very same query table. Works a treat!

I would like to make it a little more bullet proof by adding in functionaility to change where the pivot cache collects its' data from. I still want it to read that database and that very same query, but I would like to be able to change the location of the access database just in case someone moves it to a different directory. So now the directory is C:\Workflow.mdb, but what happends if it moves to H:\Workflow.mdb.

The workbook only has the one pivot cache so I am wondering if I can change its source location in one hit without having to pass the entire SQL string through again.

Can anyone give me some suggestions please?

 

[Jon von der Heyden]

Mmm if you say so. But this whole project is born out of the fact that 2003 cannot hold more than 65536 records and I am dealing with circa 300K. And at first I was writing it directly to the pivot cache but the cache alone was exceeding 200MB. When I tested the same on 2007 and placed the records in a sheet the size was considerably less. So now I am storing all records in an Access table and loading 'filtered' record sets to a pivot cache simply to manage the capacity. Whilst in Excel 2003 the model was manageable at 200MB, the reports couldn't be distributed on email.

I've put alot of effort into making it bullet proof but I know that someone, somewhere is going to break it regardless! All this effort for a model that's sole pupose is to monitor outstanding workflows and drive billing documents for workflow management... Overkill!!!

 

[RoryA]

Could you not do the pivoting in Access too?

 

[Jon von der Heyden]

I'm pivoting in Excel because they make up the reporting pack that gets distributed (emailed). The reviewers need that drill in facility; i.e. double click and it produces a list of records. I also have a bunch of fluffy charts that they like to see. And strictly speaking I'm not allowed to use access, but then why do they include it as part of standard laptop build?!

It's working nicely, and I've littered it with error trapping and a log output file. So at least if (when) it goes wrong I will have the full audit trail and should be able to work out a fix. Like I said..."Over kill!!!"

 

[Punxsu]

Hi Everyone,

I am having issues with some pivot tables created from the same cache. When I create calculated items in one, it is shared with the rest of the pivot tables. I have tried to implement the code change suggested by RoryUK and implemented by Jon von der Heyden but the pivot tables still show the same cache. I will be grateful if any help is offered.

Thanks.

Gilbert