This documentation is archived and is not being maintained.

MaintainConnection Property [Excel 2003 VBA Language Reference]

Office 2003

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.


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.


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