OLEDBConnection.Connection Property (Excel)

Returns or sets a string that contains OLE DB settings that enable Microsoft Excel to connect to an OLE DB data source. Read/write Variant.

Version Information

Version Added: Excel 2007

Syntax

expression .Connection

expression A variable that represents an OLEDBConnection object.

Remarks

Setting the Connection property does not immediately initiate the connection to the data source. You must use the Refresh method to make the connection and retrieve the data. When using an offline cube file, set the UseLocalConnection property to True and use the LocalConnection property instead of the Connection property.

Example

This example creates a PivotTable cache based on an OLAP provider, and then it creates a 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 

See Also

Concepts

OLEDBConnection Object

OLEDBConnection Object Members