Here's some code to get you started:
This code could be attached to a button on the worksheet:
Private Sub cmdUpdatePT_Sql_Click()
Dim pvtSlsPT As PivotTable
Dim strNewslsSql As String
Set pvtSlsPT = PivotTables("My_Pivot_Table")
strNewslsSql = [a cel reference].Value
'Call the process that changes the SQL
UpdatePivotQrySource pvtPT:=pvtSlsPT, strNewSql:=strNewslsSql
Set pvtSlsPT = Nothing
End Sub
This procedure is called by the above code
Sub UpdatePivotQrySource(pvtPT As PivotTable, strNewSql As String)
'pvtPT As PivotTable 'Pivot Table object to receive the
new SQL code
Dim strRestoreCommandSetting As String 'Holds the current Command string for
pvtPT
Dim strRestoreConnSetting As String 'Holds the current Connection string
for pvtPT
Dim strTempConnection As String 'Holds the working copy of the
connction string
'If the Connection is "ODBC"...
'->Temporarily change it to "OLEDB" (otherwise processing fails)
'->Effect the changes
'->Revert the connection back to "ODBC"
With pvtPT.PivotCache
'Read the connection string from pvtPT
strRestoreConnSetting = .Connection
strTempConnection = strRestoreConnSetting
strRestoreCommandSetting = .CommandText
'Write the changes to the appropriate Pivot Table setting
On Error GoTo err_Handler
strTempConnection = Replace(strRestoreConnSetting, "ODBC", "OLEDB",
1, 1)
.Connection = strTempConnection
.CommandText = strNewSql
'Restore the original connection string
.Connection = strRestoreConnSetting
On Error GoTo err_Handler
'Refresh the data
pvtPT.RefreshTable
End With
Set pvtPT = Nothing
MsgBox "Pivot table updated successfully"
Exit Sub
err_Handler:
'The update failed
application.ScreenUpdating = True
On Error GoTo err_Handler2
With pvtPT.PivotCache
'If the Connection string begins with "ODBC"
'it must be temporarily change to "OLEDB" to allow the CommandText
changes
'...then reset back to "ODBC"
strTempConnection = Replace(strRestoreConnSetting, "ODBC", "OLEDB", 1,
1)
.Connection = strTempConnection
'Set the CommandText to the pre-adjustement value
.CommandText = strRestoreCommandSetting
'Set the Connection to the pre-adjustement value
.Connection = strRestoreConnSetting
End With
Set pvtPT = Nothing
MsgBox _
Title:="UPDATE ERROR", _
Prompt:="The changes could not be implemented. Check for proper
syntax." & vbCr & vbCr _
& "The previous settings have been restored.", _
Buttons:=vbOKOnly + vbCritical
Exit Sub
err_Handler2:
MsgBox _
Title:="UPDATE ERROR", _
Prompt:="The changes could not be implemented and " & vbCr _
& "errors occurred in attempting to restore previous settings." &
vbCr & vbCr _
& "You may need to close and reopen this workbook.", _
Buttons:=vbOKOnly + vbCritical
End Sub
I hope that helps.
***********
Regards,
Ron
XL2002, WinXP
