Skip to main content

PivotCache.Connection property

Table of contents
  1. Syntax
  2. Remarks
  3. Example

Returns or sets a string that contains one of the following:

  • OLE DB settings that enable Microsoft Excel to connect to an OLE DB data source
  • ODBC settings that enable Excel to connect to an ODBC data source
  • A URL that enables Excel to connect to a web data source
  • The path to and file name of a text file
  • The path to and file name of a file that specifies a database or web query

Read/write Variant.



expression An expression that returns a PivotCache object.


When using an offline cube file, set the UseLocalConnection property to True and use the LocalConnection property instead of the Connection property.

Alternatively, you may choose to access a data source directly by using the Microsoft ActiveX Data Objects (ADO) library instead.


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.

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
    .Connection = "OLEDB;Provider=MSOLAP;Location=srvdata;Initial Catalog=National"
    .MaintainConnection = True
    .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

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>