PivotCache.refresh not working

[navin]

Hi,

In one of the macros, i am trying to update the connection properties
at workbook_open event but getting error "Application-Defined or
Object-Defined error" when code comes to pt.PivotCache.Refresh. Below
is the code:

Public Sub updateProperties()
Dim sh As Worksheet, qy As QueryTable
Dim pt As PivotTable, pc As PivotCache
Dim OldPath As String, NewPath As String
NewPath = Sheets("Instructions").Range("C130")

For Each pt In ws.PivotTables
pt.PivotCache.Connection = _
Application.Substitute(LCase(pt.PivotCache.Connection),
_
LCase(pt.PivotCache.Connection), LCase(NewPath))
pt.PivotCache.Refresh
Next pt
Next ws
End Sub

I have searched for a solution without any luck. Please help.

Thanks,
Navin