Skip to content

Latest commit

 

History

History
69 lines (49 loc) · 2.03 KB

Excel.PivotCache.Connection.md

File metadata and controls

69 lines (49 loc) · 2.03 KB
Raw
title keywords f1_keywords api_name ms.assetid ms.date ms.localizationpriority
PivotCache.Connection property (Excel)
vbaxl10.chm227074
vbaxl10.chm227074
Excel.PivotCache.Connection
5d4b07f2-dad9-4c90-ec92-094dac95a086
05/03/2019
medium

PivotCache.Connection property (Excel)

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.

Syntax

expression.Connection

expression An expression that returns a PivotCache object.

Remarks

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.

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.

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

[!includeSupport and feedback]