Edit

Share via

PivotCache.MaintainConnection property (Excel)

  • Article

True if the connection to the specified data source is maintained after the refresh and until the workbook is closed. The default value is True. Read/write Boolean.

Syntax

expression.MaintainConnection

expression A variable that represents a PivotCache object.

Remarks

You can set the MaintainConnection property only if the QueryType property of the query table or PivotTable cache is set to xlOLEDBQuery.

If you anticipate frequent queries to a server, setting this property to True might improve performance by reducing reconnection time. Setting the property to False causes an open connection to be closed.

Example

This example creates a new PivotTable cache based on an OLAP provider, and then it creates a new PivotTable report based on the cache at cell A3 on the active worksheet. The example terminates the connection after the initial refresh.

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal) 
 .Connection = _ 
 "OLEDB;Provider=MSOLAP;Location=srvdata;Initial Catalog=National" 
 .MaintainConnection = False 
 .CreatePivotTable TableDestination:=Range("A3"), _ 
 TableName:= "PivotTable1" 
End With 
With ActiveSheet.PivotTables("PivotTable1") 
 .SmallGrid = False 
 .PivotCache.RefreshPeriod = 0 
 With .CubeFields("[state]") 
 .Orientation = xlColumnField 
 .Position = 0 
 End With 
 With .CubeFields("[Measures].[Count Of au_id]") 
 .Orientation = xlDataField 
 .Position = 0 
 End With 
End With

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.