Pivot Connections vs pivotcache.commandtext

[mmogul]

This is an Excel VBA issue.

I have a workbook with 5 pivot tables. There are 3 different Connection Names.

I need to update the underlying queries which retrieve data from an Access database, using the Pivotcache.commandtext function.

The queries I need to assign are different for each connection name. I would like to loop through the Conn.name and then based on the connection name, change to a specific query. For example, Connection A gets assigned Query1 and Connection B gets Query2.

The rough logic is something like this but I know this isn't correct:

For i = 1 to this workbook.connections.count
      Set conn = thisworkbook.connections(i)
      sConn = conn.name
      Select Case sConn
           Case a
                 Pivotcache.commandtext= new text
            Case b
                 Pivotcache.commandtext = different new text
      End select

Next I

I need some ideas on how to approach this issue.

Thanks

 

[lameid]

This is an Excel VBA issue.

Which is why it does not belong in the Access VBA forum... It should go in the generic VBA forum...
forum707

That said, I'm not sure what your issue is... Do you not know which PivotCache to change? I believe each pivot table object has a pivotcache property to specify the pivotcache. That may be easier than trying to identify the Pivot Cache name.