query source code

[Mel]

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;PageTimeout=5;SafeT
ransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"
pc.Connection = str
End Sub


Thanks in advance.

 

[Dick Kusleika]

Mel

Something like this

Sub ChangeSQL()

Dim OldPath as String
Dim NewPath as String
Dim sh as Worksheet
Dim pt as PivotTable

OldPath = "C:\MyOldPath"
NewPath = "C:\MyNewPath"

For Each sh in ThisWorkbook.Worksheets
For Each pt in sh.PivotTables
pt.PivotCache.CommandText = Replace( _
pt.PivotCache.CommandText,OldPath, _
NewPath)
Next pt
Next sh

End Sub

If you have pivot tables that are not external data, then you may have to
check for that. Try this out and let me know if it needs modification.