Class WorkbookConnection (Excel VBA)

A connection is a set of information needed to obtain data from an external data source other than a Microsoft Excel workbook.

The main procedures of class WorkbookConnection are Connections.AddFromFile and Delete


To use a WorkbookConnection class variable it first needs to be instantiated, for example

Dim wbcon as WorkbookConnection
Set wbcon = ActiveWorkbook.Connections(Index:=1)

The following procedures can be used to set variables of type WorkbookConnection: Workbook.Connections, Connections.Item, Connections.Add2, Connections.AddFromFile, Model.AddConnection, Model.CreateModelWorkbookConnection, Model.DataModelConnection, ModelTable.SourceWorkbookConnection, PivotCache.WorkbookConnection, QueryTable.WorkbookConnection, SlicerCache.WorkbookConnection, TableObject.WorkbookConnection and XmlMap.WorkbookConnection

For Each

Here is an example of processing the WorkbookConnection items in a collection.

Dim wbconConnection As WorkbookConnection
For Each wbconConnection In ActiveWorkbook.Connections
Next wbconConnection


The following procedures in this class have been grouped together and are described on a separate theme page

Model with its procedures InModel, ModelConnection and ModelTables


These are the main methods of the WorkbookConnection class

Connections.AddFromFile - Adds a connection from the specified file.

Dim strFilename As String: strFilename = 
Dim wbconAddFromFile As WorkbookConnection
Set wbconAddFromFile = ActiveWorkbook.Connections.AddFromFile(Filename:=strFilename)

Delete - Deletes a workbook connection.


Other Methods

Connections.Item - This method creates a connection item.

Refresh - Refreshes a workbook connection.


Connections.Count returns the number of objects in the collection.

Connections.Parent returns the parent object for the specified object. Read-only.

DataFeedConnection returns a DataFeedConnection object that contains the data and functionality needed to connect to data feeds.

Description returns or sets a brief description for a WorkbookConnection object.

Name returns or sets the name of the WorkbookConnection object.

ODBCConnection returns the ODBC connection details for the specified WorkbookConnection object.

OLEDBConnection returns the OLEDB connection details for the specified WorkbookConnection object.

Parent returns the parent object for the specified object. Read-only.

Ranges returns the range of objects for the specified WorkbookConnection object.

RefreshWithRefreshAll determines if the connection should be refreshed when Refresh All is executed.

TextConnection returns a TextConnection object that contains the information on a query to a text file.

Type returns the workbook connection type.

WorksheetDataConnection returns an object that contains information for a connection from the PowerPivot Model to data within the workbook, such as a range, named range, or table.