RunTime error 438 Can anyone see what is wrong with this code?

[sheppard26]

Hi,

I am trying to set the PivotCache SourceData to a value,which is a filename/path, in a specific cell within the workbook.

I have written the following code but receive a runtime error 438 when running it.

Dim Source As String
Dim PTCache As PivotCache
Source = ActiveWorkbook.Sheets("Macros").Range("B7").Value
Set PTCache = ActiveWorkbook.PivotCaches.create( _
SourceType:=xlDatabase, _
SourceData:=Source)


'Refresh Pivot Cache
Sheets("Spot Sales").PivotTables("Endur Source").PivotCache.Refresh

This Cache is specific to one of many pivottable within the workbook, I have not specified that this cache is relating to this pivottable, could this be the problem? All the other pivot tables are linked to this pivot in question.
I hope this makes sense.


PLEASE PLEASE help, this is driving me nuts!

 

[RoryA]

What exactly is in B7 on the Macros sheet?

 

[sheppard26]

Hi rorya,

B7 contains the file path, and range of the pivot data

"'G:\GAS\LON\Finance\KDRIVE\NG T&O_2011\2011_02\Netting_2011 02\Accrual_2011 02\[3.Endur Data Accrual_2011 02.xls]Endur €'!$A:$BL"

this changes on a monthly basis, so I would like to just be able to update this cell each month with the correct datasource and then have the cache for the pivot table "Endur Source" refreshed, in turn triggering a refresh of all the pivots in the sheet as they are already linked to this pivot.

Is this possible?

 

[RoryA]

Do you actually have the double quotes in there? You shouldn't but you will need to double up the initial apostrophe.

 

[sheppard26]

Hi,

Thanks for your response.

I didn't have the quotations in ther initially. I have just tried to run the code using the suggested contents in B7

i.e. ''G:\GAS\LON\Finance\KDRIVE\NG T&O_2011\2011_02\Netting_2011 02\Accrual_2011 02\[3.Endur Data Accrual_2011 02.xls]Endur €'!$A:$BL

and the same error has occurred...

 

[RoryA]

Are you running this in a version prior to 2007? If so, you need Pivotcaches.Add rather than Pivotcaches.Create

 

[sheppard26]

Hi Rory,

You are a genius!

The runtime error has now been corrected. However, the pivot does not appear to be updating with the new cache source. Is this because I have only 'set' the value for PTCache, do I now need to incorporate it into the last bit of code?


Sheets("Spot Sales").PivotTables("Endur Source").PivotCache.Refresh

I.e. link the PTCache into the pivottable in question.

Thanks for all your help so far!

 

[RoryA]

Currently you are creating a new cache which has no associated table. In order to link it to an existing table, you need to create a new pivot table from the new cache (just a dummy table - it can be deleted afterwards) and then set the CacheIndex property of your pivot table to the Index property of the new PivotCache. You could also just alter the SourceData property of the existing cache!

 

[sheppard26]

Hi Rory,

Thanks for your reply. I have no idea about CacheIndexes... and the literature I have to hand is pretty broad relating to this...

I've tried this, but obviously it doesn't work

Set PT = Workbook.Sheets("Spot Sales").PivotTables("Endur Source")

ActiveWorkbook.PT.CacheIndex = PTCache

If I created another pivot and then linked it and then deleted it again surely that would nullify the link?

 

[RoryA]

It would be:

Set PT = Workbook.Sheets("Spot Sales").PivotTables("Endur Source")
PT.CacheIndex = PTCache.Index

and no, deleting the other table would not nullify the link.