The Workbook Connection Object and the Connections Collection

New to Excel 2007 is an object and collection designed to manage all external data connections in a workbook. Each time you create any one of the built-in objects that Excel uses to manage external data, including QueryTables, ListObjects, and PivotCaches, you are also creating a new instance of a WorkbookConnection object. All of the WorkbookConnection objects in a given workbook are contained in the Workbook.Connections collection for that workbook.

You can also create a standalone WorkbookConnection object, one that is not associated with any external data container. The eventual intent for this feature is to allow you to create query tables, list objects, and all other external data containers using a WorkbookConnection object as the data source. However, as of Excel 2007, this feature has only been implemented for PivotCache objects. See Chapter 7 for more details on PivotCache objects.

You can create a new WorkbookConnection object using the Add or AddFromFile methods of the Workbook.Connections collection. The following example creates a new WorkbookConnection using the Add method:

Sub CreateNewConnection()

Dim objWBConnect As WorkbookConnection

Set objWBConnect = ThisWorkbook.Connections.Add( _ Name:="New Connection", _ Description:="My New Connection Demo", _

ConnectionString:="OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=C:\Files\Northwind 2007.accdb", _ CommandText:="SELECT [First Name], [Last Name] FROM Customers", _ lCmdtype:=xlCmdSql)

End Sub

After a WorkbookConnection object has been created, it is persisted when the workbook is saved. It will then be available any time the workbook is open, so there is no need to re-create it.

You can also use the Workbook.Connections collection to iterate through all the WorkbookConnection objects in a workbook and examine or modify their properties. The next example populates a worksheet with a list of all WorkbookConnection objects in the current workbook, and their type and their connection string if applicable:

Sub ExamineWorkbookConnections()

Dim lOffset As Long

Dim objWBConnect As WorkbookConnection

Sheet8.UsedRange.Clear With Sheet8.Range("A1:C1")

.Value = Array("Connection Name", "Connection Type", "Connection String") .EntireColumn.AutoFit End With

For Each objWBConnect In ThisWorkbook.Connections lOffset = lOffset + 1

Sheet8.Range("A1").Offset(lOffset, 0).Value = objWBConnect.Name Sheet8.Range("A1").Offset(lOffset, 1).Value = objWBConnect.Type If objWBConnect.Type = xlConnectionTypeODBC Then

Sheet8.Range("A1").Offset(lOffset, 2).Value = _ objWBConnect.ODBCConnection.Connection Elself objWBConnect.Type = xlConnectionTypeOLEDB Then Sheet8.Range("A1").Offset(lOffset, 2).Value = _ objWBConnect.OLEDBConnection.Connection

Else

Sheet8.Range("A1").Offset(lOffset, 2).Value = "Not Applicable" End If Next objWBConnect

End Sub

Note that WorkbookConnection objects based on ODBC and OLE DB have additional child connection objects, the ODBCConnection object and OLEDBConnection object. These objects maintain the connection information required by ODBC and OLE DB.

+2 -5

Responses

  • Woody
    Hey,<br />I found your page quite useful however I've been trying to uses the ranges property of the workbookconnection object to return (i assume) the location it is used in the workbook (similar to when you look at connections dialog box in excel).<br />Are you able to put an example of this in your existing code?<br />thanks in advance.
    8 years ago

Post a comment