How to modify the sql statement on a pivot table using VBA- XL2000
 

Hi,
I need help modifying the sql statemnet for a pivot table. I create a pivot
table using ODBC connection. I created fine and even the refresh work fine.
But when I need to modify the query to add new conditions to the sql
statement, the pivot pivot still refreshes with the old slq. It does get
change.
refrsh code:

' odbc connection and sql statement would be he:

Sub RefreshPivotTables()
'This routine calls the refresh method for each sheet's pivot table
Dim sSheet As String
Application.ScreenUpdating = False
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets(sSheet)
Set ptCache = wbBook.PivotCaches(1)
Set ptTable = wsSheet.PivotTables(sSheet)
ptTable.RefreshTable
Application.ScreenUpdating = True
End Sub

Any help would be appreciated. Thanks.

How to modify the sql statement on a pivot table using VBA- XL2000
 

I don't know if you are trying to modify the SQL in a macro or manually. You
can edit the SQL manually by going to the worksheet where the query is
located then go to menu

Data - Import external Data - Edit query.

You can find the Query Name by look at the Define Names in the Worksheet menu

Insert - Names - Define and select the query name

You can modify the SQL statment from a macro knowing the Query Name that you
get from the Define Menu above.

Set objQryTbl = Sheets("Sheet2").QueryTables("ABC") where ABC is the
Define Name

The SQL will be

MySQL = objQryTbl.commandtext




"Carlos" wrote:

Hi,
I need help modifying the sql statemnet for a pivot table. I create a pivot
table using ODBC connection. I created fine and even the refresh work fine.
But when I need to modify the query to add new conditions to the sql
statement, the pivot pivot still refreshes with the old slq. It does get
change.
refrsh code:

' odbc connection and sql statement would be he:

Sub RefreshPivotTables()
'This routine calls the refresh method for each sheet's pivot table
Dim sSheet As String
Application.ScreenUpdating = False
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets(sSheet)
Set ptCache = wbBook.PivotCaches(1)
Set ptTable = wsSheet.PivotTables(sSheet)
ptTable.RefreshTable
Application.ScreenUpdating = True
End Sub

Any help would be appreciated. Thanks.