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

sheppard26

New Member
Joined
Sep 8, 2009
Messages
20
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!
 
Last edited:

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,133
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
What exactly is in B7 on the Macros sheet?
 

sheppard26

New Member
Joined
Sep 8, 2009
Messages
20
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,133
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Do you actually have the double quotes in there? You shouldn't but you will need to double up the initial apostrophe.
 

sheppard26

New Member
Joined
Sep 8, 2009
Messages
20

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,133
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Are you running this in a version prior to 2007? If so, you need Pivotcaches.Add rather than Pivotcaches.Create
 

sheppard26

New Member
Joined
Sep 8, 2009
Messages
20

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,133
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Sep 8, 2009
Messages
20
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,133
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It would be:
Code:
Set PT = Workbook.Sheets("Spot Sales").PivotTables("Endur Source")
PT.CacheIndex = PTCache.Index

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

Watch MrExcel Video

Forum statistics

Threads
1,123,088
Messages
5,599,664
Members
414,325
Latest member
kfg1287

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top