Pivot table with no data source - update pivotcache using disconnected recordset

[BellyGas]

Is it possible to have a pivot table with neither a data table behind it or an Excel data connection....but instead update the pivotcache using an SQL statement run on the remote database, setting the pivotcache to the recordset like so:

Please Login or Register  to view this content.

The code above works fine until I remove the Excel data connection. I don't want a connection in the workbook, I don't want users to be able to refresh themselves, I just want to be able to update the pivotcache when the automated script runs.

However, Excel won't let me. Pivot tables just whinge as soon as they don't have a permanent data source. Why? If I'm using a disconnected recordset as the data source....why do I need to have a permanent connection in Excel to the actual data source?

[CK76]

I believe it's because when you remove connection, it also removes data model used to create pivottable.

I haven't done it myself, but you may be able to use .PivotCache.Create method to create temporary Pivot table on a new sheet.

Then using PivotTable.CacheIndex property to assign temporary Pivot table's cache to replace existing one's.

[CK76]

Did a small test. You need PivotTable.ChangeConnection instead of .CacheIndex.
https://msdn.microsoft.com/en-us/lib.../ff194492.aspx

Unfortunately, doing so have removed all value fields from pivottables in my test. But then it could be due to some other factor.

If I find anything more I'll let you know.

[xlnitwit]

Hi,

That recordset is not disconnected as you have not cleared its connection. To the best of my recollection you should not have to refresh a pivotcache after assigning a recordset to it.

[BellyGas]

The cnt.close part comes later in the macro. Does that not clear the connection?

[BellyGas]

According to MS, the .changeconnection command requires a 'WorkbookConnection', you can't use a recordset.

[BellyGas]

This is the code I've come up with for users to test whether an SQL statement they've just written is working:

Please Login or Register  to view this content.

[xlnitwit]

The cnt.close part comes later in the macro. Does that not clear the connection?

No, it doesn't. You need to set the recordset's connection property to Nothing.

[BellyGas]

ahha! Ta, I'll let ya know how I get on.