Connection Property

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 Microsoft Excel to connect to an ODBC data source; a URL that enables Microsoft Excel to connect to a Web data source; the path to and file name of a text file, or the path to and file name of a file that specifies a database or Web query. Read/write Variant.

Remarks

Setting the Connection property doesn’t 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.

For more information about the connection string syntax, see the Add method of the QueryTables collection and the Add method of the PivotCaches collection.

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
		

This example supplies new ODBC connection information for the first query table on the first worksheet.

Worksheets(1).QueryTables(1) _
    .Connection:="ODBC;DSN=96SalesData;UID=Rep21;PWD=NUyHwYQI;"
		

This example specifies a text file.

Worksheets(1).QueryTables(1) _
    Connection := "TEXT;C:\My Documents\19980331.txt"