updating a pivot tables connection through code

[star1984]

Hi,

Firstly I am not very familiar with VBA but I am trying to learn.

I have a piece of code that several Excel workbooks call. This updates the various connections with a centrally stored connection string SQLConn.

I have the following code for updaing query tables and pivot tables. The query table part always works, however the pivot table part sometimes works and sometimes doesn't. Sometimes the code will crash as a pivottable will have no pivotcache or connection property. Does anyone know why this would be? Is there one way to access a pivot tables connection property? please help

For Each Worksheet In callingworkbook.Worksheets
itemcount = 0
For Each Item In Worksheet.QueryTables
itemcount = itemcount + 1
Worksheet.QueryTables.Item(itemcount).Connection = SQLConn
Next

itemcount = 0
For Each PivotTable In Worksheet.PivotTables
itemcount = itemcount + 1
Worksheet.PivotTables(itemcount).PivotCache.Connection = SQLConn
Next
Next

 

[star1984]

I replace my pivotcache.connection code with the following and it seems to work for everything. May not be the best way of doing (I really don't know!) but all I know is it works

For Each PivotTable In Worksheet.PivotTables
callingworkbook.Connections(itemcount).ODBCConnection.SavePassword = True
callingworkbook.Connections(itemcount).ODBCConnection.Connection = SQLConn
callingworkbook.Connections(itemcount).ODBCConnection.SavePassword = False
Next