I needed to redirect my pivot table worksheets to a
database in another location. I tried the code suggested
by the newsgroup and was successful. However, all of the
pivot tables have underlying queries. The source path is
located in every one of those queries. In order to
refresh my data from the new location, I had to "get data"
to open the query, click on SQL, and modify the SQL
statement to change the path (for each pivot table!). In
this situation, there were only 10 or so pivot tables.

However, I have another workbook that has many, many pivot
tables. It would be insane to change each one this way.
What code can I use to change the path in all of the
underlying queries? Note that each query is not saved, it
is buried somewhere behind each pivot table. I know there
is a way to do this.

For your info, here is the code I used to change the
source of the mdb:

Public Sub ResetPivotTableConnection()
Dim pc As PivotCache
Dim pt As PivotTable
Dim str As String
Set pt = Sheet2.PivotTables("PivotTable2")
Set pc = ThisWorkbook.PivotCaches(pt.CacheIndex)
'Debug.Print pc.Connection
str = "ODBC;DSN=MS Access Database;"
str = str
& "ODBC;DBQ=c:\....Summ_Data.mdb;DefaultDir=c:\....; Driver=
{Driver do Microsoft Access (*.mdb)};DriverId=25;FIL=MS
Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeou t=5;SafeT
ransactions=0;Threads=3;UID=admin;UserCommitSync=Y es;"
pc.Connection = str
End Sub


Thanks in advance.