Changing the PivotTable CommandText

[mmogul]

I am attempting to programmatically change the CommandText (sql statement) for a pivot table. My code works if all pivot tables in the workbook have a unique ConnectionName. But if more than one pivot tables use the same ConnectionName, I get an error:

Application defined or object defined error.
Object variable or with block variable not set.

In the code shown below, I have 4 worksheets. Worksheet 1 has 2 pivot tables using unique ConnectionNames. Worksheet 2-4 each have one pivottable and they share the same Connection Name.

Here is my code. Appreciate any help.

Dim ws as worksheet
Dim sConnections as string
Dim pvt as pivottable


For Each ws In ThisWorkbook.Worksheets
    For Each pvt In ws.PivotTables
        sConnectionName = pvt.PivotCache.WorkbookConnection
        '***
        '  Change query CommandText
        Select Case sConnectionName
            Case "Connection3":
                pvt.PivotCache.CommandText = "Select * FROM qryTypesWithMapping ;"
            Case "Connection5":
                pvt.PivotCache.CommandText = "Select * FROM qryTypesWithMapping_Drivers ;"
           
            Case "Connection2":
                pvt.PivotCache.CommandText = "Select * FROM [qry_RandD_Region] ;"
            Case Else:
                MsgBox "The connection name was not found"
        End Select
        
    Next pvt
Next ws

 

[kjv1611]

Here's just a thought, and not sure off-hand if it's even possible. What about programatically changing the 2nd table or pivot table in each sheet, and then going back and trying this process? Sounds silly, but maybe it'll work?



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57