Karl001
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.