Pivot table external source drive changes

[Joe C]

I did a search here but didn't find this question.

I am using pivotable with access queery as the source.

When I set it I cuse that external source browse tool, but it seems to only recognize the source if the dive is the same.
When I go downstairs to try on a users computer there dirive letter(K is different than my (H and it does not work.

 

[Joe C]

I got it to change, but when I go downstairs(different drive letter) and someone tries to refresh the file. It comes up with message that T:ABY/db1 does not exist.
I do the pivotcache connection check and it says it is pointed to the
UNC \\safps......... Is there another place I need to set for them to be able to refresh?

 

[Andrew Poulsom]

I'm not sure, but maybe this will help you:

http://www.contextures.com/xlPivotPlayPLUS01.html

 

[Joe C]

I tried this at home over the weekend and it worked. It seems I had to change a queery as well.

Again so many thanks.

 

[Joe C]

This is working fantastic now. Many thanks. In case anyone was hoping for an answer to about the size question.

The database queery has gottten quite big thus the pivot table was gretting huge, but most of the time I/user only wants recent data so what I did was add a quick macro in the worksheet with the pivot table, where the user can kinda set the range he will be working in.

When he/she runs the macro he sets a table in the databasae which attached to the queery the pivot table uses.

It allows me to control the amont of info in the pivottable data area without having to use an olap cube. Which with drive situation would have just completely fried my mind at this point.

Again many thanks.