Agree, is this not a Bug?. The main issue is that I have to rely on the user
specifying the same mdw as in the DNS and keeping consistency which is
clearly a problem.

I've also noticed that the PivotCache connection property (as reported in
the VBA watch) seems to truncate the end of the string when using long file
path names although it seems that "internally" things function properly. I
see the same issue in the SourceData property (array) of the PivotTable. Is
this just a reporting issue? I am concerned that the code that you propose
may not work in that case.

"Dick Kusleika" wrote:

FCS wrote:
Thanks for your reference. I have made some tests and it seems that the
mdw file name is stored in the PT. It can be seen in the PivotCahe
Connection property and it follows the format "SystemDB=<file". Any
idea on how can I force it to read again the entry from the DSN?


Yes, you can recreate the pivot table. That option stinks, I'm sure you'll
agree. You can change the location of the db in the pivottable. It doesn't
'read' it from the DSN, but it has the same effect.

Sub Changedbloc()

Const sOLDPATH As String = "C:\OldFolder\"
Const sNEWPATH AS String = "C:\NewFolder\"

With Sheet1.PivotTables(1).PivotCache
.Connection = Replace(.Connection, sOLDPATH, sNEWPATH)
.Refresh
End With
End Sub


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com