I have many PivotTable source to change.
The source is an access query. The access database was moved in a new path.
I used Kevin Jones code.
I got 1004 error (Application-defined or object-defined error
) on the first line of : Connection = Pivot.Connection
Do you have any idea what is the problem?
Thanks
Carol
Sub MyMacro() ConvertPivotReferences " S:\Data\Client", " S:\Client"End SubPublic Sub ConvertPivotReferences( _ ByVal OldPath As String, _ ByVal NewPath As String _ ) Dim Pivot As PivotCache Dim Connection As String Dim Position As Long Dim Count As Long For Each Pivot In ThisWorkbook.PivotCaches Connection = Pivot.Connection If InStr(Connection, OldPath) > 0 Then Connection = Replace(Connection, OldPath, NewPath) Count = Count + 1 End If Pivot.Connection = Connection Next MsgBox Count & " PivotTables changed."End Sub