Avatar of Karl001
Karl001
Flag for Canada asked on

Many PivotTable source to change - VBA Error 1004

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 Sub


Public 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

Open in new window

Microsoft ExcelVisual Basic ClassicVBA

Avatar of undefined
Last Comment
Karl001

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Karl001

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Your help has saved me hundreds of hours of internet surfing.
fblack61