Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
query source code
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
query source code
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. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Mel" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change data source for MS Query | Excel Discussion (Misc queries) | |||
Data Source for MS Query | Excel Discussion (Misc queries) | |||
changing query source | Excel Worksheet Functions | |||
How to find a query source | Excel Worksheet Functions | |||
Changing Query Source Location? | Excel Programming |