Please help! Cannot open pivotTable source file....

[Bobby]

I have to change the pivot table source file name in Excel 2003:

From: 12 compilation rapports de fruits 2011-2012 .xls etc...

To : 12 compilation rapports de fruits 2012-2013 .xls etc...

I wrote this code:

Sub AllWorkbookPivots()
Dim pt As PivotTable
Dim ws As Worksheet
tmp = ""
tmp1 = ""

For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
tmp = pt.SourceData
tmp1 = Replace(tmp, "2011-2012", "2012-2013")
ws.PivotTableWizard SourceType:=xlDatabase, SourceData:=tmp1
ActiveWorkbook.ShowPivotTableFieldList = False
tmp = ""
tmp1 = ""
Next pt
Next ws
End Sub

Everything when fine!
I saved and closed the file. When I opened the file again that's where I got the
message: Cannot open pivotTable source file....

I looked up in DATA--> Pivot Table and Pivot Chart Report, did a BACK on the Wizard and the information had changed for the new info!

Forgot to mention that in the Workbook there are charts also. I am not to Kknowledgeable with PT.

Can someone explain me what I should to solve the situation?
help!!!

Thank's ahead

 

[Ben McClave]

Bobby,

I think that you may need to change the existing PivotTables' PivotCache, rather than using the Wizard method. Try this code to see if it works better for you:

For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
tmp = pt.SourceData
tmp1 = Replace(tmp, "2011-2012", "2012-2013")
pt.ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
tmp1, Version:=xlPivotTableVersion14)
tmp = ""
tmp1 = ""
Next pt
Next ws

 

[Bobby]

I have to change the pivot table source file name in Excel 2003:



From: 12 compilation rapports de fruits 2011-2012 .xls etc...



To : 12 compilation rapports de fruits 2012-2013 .xls etc...



I wrote this code:



Sub AllWorkbookPivots()

Dim pt As PivotTable

Dim ws As Worksheet

tmp = ""

tmp1 = ""



For Each ws In ActiveWorkbook.Worksheets

For Each pt In ws.PivotTables

tmp = pt.SourceData

tmp1 = Replace(tmp, "2011-2012", "2012-2013")

ws.PivotTableWizard SourceType:=xlDatabase, SourceData:=tmp1

ActiveWorkbook.ShowPivotTableFieldList = False

tmp = ""

tmp1 = ""

Next pt

Next ws

End Sub



Everything when fine!

I saved and closed the file. When I opened the file again that's where I got the

message: Cannot open pivotTable source file....



I looked up in DATA--> Pivot Table and Pivot Chart Report, did a BACK on the Wizard and the information had changed for the new info!



Forgot to mention that in the Workbook there are charts also. I am not to Kknowledgeable with PT.



Can someone explain me what I should to solve the situation?

help!!!



Thank's ahead

Hi Ben
I did try your proposition but I get the following error: Object doesn't support this property or method!
Any other idea?