Many PivotTable source to change - VBA Error 1004

Karl001
Karl001 used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Found my problem.
I wrote the wrong path

thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial