troubleshooting Question

Many PivotTable source to change - VBA Error 1004

Avatar of Karl001
Karl001Flag for Canada asked on
Microsoft ExcelVisual Basic ClassicVBA
1 Comment1 Solution135 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Karl001

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 1 Comment.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros