Pivot table not refreshing
I've created some code to change the database used in a PivotCache. (Based
on snippets found in this group.) Based on the MsgBox statement at the end, it appears the database is changing, however a refresh of the table shows the old data. Where am I going wrong? I'm finding it difficult to locate good examples of changing SQL database. Sub ChangeDatabase() 'Declare our variables. Dim ptc As PivotCache, oldDB As String, newDB As String Set ptc = ActiveCell.PivotTable.PivotCache MsgBox "Connection: " & ptc.Connection ' Confirms the current settings 'Request the name of the old server/file name. oldDB = InputBox("Input the name of the old database or file path as listed in the Pivot Tables SQL string.") 'Request the name of the new server/file name. newDB = InputBox("Input the name of the new database or file path which you want the Pivot Table to point to.") 'Replace the ODBC information of whatever PivotTable is currently active. ptc.Connection = Application.Substitute(ptc.Connection, oldDB, newDB) ptc.Refresh MsgBox "Connection: " & ptc.Connection ' Shows new connection information End Sub Thanks - John |
Pivot table not refreshing
Sorry about the typos in previous message. It appears my spell checker was
"improving" my English. - John "John Michl" wrote in message ... I believed I figured out the problem but have not solved it. Though I was change the source of the data, I was not change the SQL statement which still referred to the original database. When trying to change that statement, I learned that I can't since when I set up this workbook, I copied one pivot table and pasted as others then changed some of the fields. Does anyone know how to change the SELECT statement in the SQL query under these circumstances? Thanks - John "John Michl" wrote in message ... I've created some code to change the database used in a PivotCache. (Based on snippets found in this group.) Based on the MsgBox statement at the end, it appears the database is changing, however a refresh of the table shows the old data. Where am I going wrong? I'm finding it difficult to locate good examples of changing SQL database. Sub ChangeDatabase() 'Declare our variables. Dim ptc As PivotCache, oldDB As String, newDB As String Set ptc = ActiveCell.PivotTable.PivotCache MsgBox "Connection: " & ptc.Connection ' Confirms the current settings 'Request the name of the old server/file name. oldDB = InputBox("Input the name of the old database or file path as listed in the Pivot Tables SQL string.") 'Request the name of the new server/file name. newDB = InputBox("Input the name of the new database or file path which you want the Pivot Table to point to.") 'Replace the ODBC information of whatever PivotTable is currently active. ptc.Connection = Application.Substitute(ptc.Connection, oldDB, newDB) ptc.Refresh MsgBox "Connection: " & ptc.Connection ' Shows new connection information End Sub Thanks - John |
All times are GMT +1. The time now is 10:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com