Programming Excel with VBA and .NET

Name

pivotcache.LocalConnection [= setting]

Synopsis

For OLAP pivot caches, True uses a local cube file and False uses a remote OLAP provider. Default is False. The following code saves an OLAP pivot table as a local cube file, then uses that data source offline:

Sub UseOLAPOffline(  )
    Dim pc As PivotCache, pt As PivotTable, fname As String
    ' Run earlier example to create pivot table.
    CreateOLAPPivotTable
    ' Get pivot table.
    Set pt = ActiveSheet.PivotTables(1)
    ' Get the pivot cache.
    Set pc = pt.PivotCache
    ' Save local cube file.
    fname = ActiveWorkbook.Path & "\" & pt.Name & ".cub"
    pt.CreateCubeFile fname
    ' Take cache offline.
    pc.LocalConnection = "OLEDB;Provider=MSOLAP;Data Source=" & fname
    pc.UseLocalConnection = True
End Sub