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!

 

[sheppard26]

Rory, THANKS AGAIN


I was getting runtime error 424 because of the code

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

so I just replaced workbook with activeworkbook

But now I have error 1004 relating to code (if I run the whole code in the module window) or error 424, when running this line of code in the immediate window

PT.CacheIndex = PTCache.Index

It appears to be looking for an object

Aargh, this is going to be the end of me!

 

[RoryA]

Did you add code to create a pivot table off the new cache? It won't work without it as the new cache doesn't get an index until it has a table attached.

 

[sheppard26]

Hi Rory,

I was working on it all day yesterday and can't understand where I am going wrong.

I recorded a macro creating a pivot using the existing pivots data: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'[3.Endur Data Accrual_2011 01.xls]Endur €'!C1:C64").CreatePivotTable _
TableDestination:="'[1.EUR Accrual Workings_2011 02_pivot.xls]Sheet6'!R11C7" _
, TableName:="PivotTable7", DefaultVersion:=xlPivotTableVersion10
ActiveWorkbook.ShowPivotTableFieldList = True

Obviously this code adds a cache and doesn't use the PTCache created earlier.

So I've modified it to show the following


ActiveWorkbook.PivotTables.Add(PivotCache:=PTCache, _
TableDestination:=range("A7") _
, TableName:="PivotTable7")

I get a compile error, "Expected:=" I can't see where it is missing. Surely the code makes sense? I am adding a pivot table using pivotcache PTCache specifying destination and name... why is it busted??

EEK! Sorry, still learning sooooooooo very trying at the moment!
Thanks for your help!

 

[RoryA]

If you are not returning anything from a method, you don't use parentheses:

ActiveWorkbook.PivotTables.Add PivotCache:=PTCache, _
TableDestination:=range("A7") _
, TableName:="PivotTable7"

 

[sheppard26]

Hi Rory,

So I have finally had more time to work on this...

I decided to start over... I created a cache (from the file ref in Cell B7) and then a pivot from that cache. This is going to be the 'Mother Cache' for all pivots in this workbook.

Everything runs smoothly BUT

I want the cache to refresh everytime cell B7 is changed. I have used the change event for this to happen. When cell B7 is updated the code does run because it takes some time to complete, but the pivot table does not update as expected. i.e after running the code (below) the source of the pivot is still showing as what was in cell B7 initially when creating the pivot. Any ideas as to what may be going wrong?

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo Handler
Application.EnableEvents = False
If Not Intersect(Target, Range("B7")) Is Nothing Then
Worksheets("Spot Sales").PivotTables("Endur Source €").PivotCache.Refresh
End If
Application.EnableEvents = True
Handler:
Application.EnableEvents = True
End Sub

 

[RoryA]

How are you altering the cache to use the new data in B7?

 

[sheppard26]

I'm not... I assumed that the cache created to create this pivot would just refresh on cell change, i.e file name change in this cell.

Do I now need to create a new pivot cache everytime cell B7 is changed and then set it to this pivot table's source?

 

[RoryA]

There is no way of linking a cache to a filename in a cell so you would have to either edit the cache's connection or create a new one.

 

[sheppard26]

Thanks for the reply

If you can't link the cache to a filename in a cell why is it possible to creat a cache from the filename in the cell?

I can't create a new cache everytime the cell is changed so how do you suggest that I edit the connection?

Thanks Again!!!

 

[RoryA]

Because you can create it using the value in the cell at that time, but it's not linked in any way to the cell. The code just reads the value that is in there when it runs and uses that value as the file path.
You need to edit the SourceData property of the pivotcache to reflect the new workbook/sheet/range as applicable.
Running:

msgbox activesheet.pivottables(1).pivotcache.sourcedata

will give you an idea of the format required.