Class ModelConnection (Excel VBA)

Contains information for the new Model Connection Type introduced in Excel 2013 to interact with the integrated data model. To use a ModelConnection class variable it first needs to be instantiated, for example

Dim mcn as ModelConnection
Set mcn = ActiveWorkbook.Connections(1).ModelConnection

ADOConnection

The ADOConnection adoconnection is used to create an open connection to a data source. Enables add-ins, such as Powerview, to create a direct connection to the engine and hence the data model. Read-only ADOConnection adoconnection.

Dim adoADOConnection As ADOConnection
adoADOConnection = ActiveWorkbook.Connections(1).ModelConnection.ADOConnection

CalculatedMembers

Returns a CalculatedMembers object that represents the calculated members in the model connection.

Dim cmsCalculatedMembers As CalculatedMembers
Set cmsCalculatedMembers = ActiveWorkbook.Connections(1).ModelConnection.CalculatedMembers

CommandText

Returns or sets the command string for the specified data source. Read/write Variant.

The command text is either a table name (of a model table) or a DAX expression evaluating to a table.

ActiveWorkbook.Connections(1).ModelConnection.CommandText =

CommandType

Returns or sets one of the XlCmdType enumeration constants. Possible return values are xlCmdCube - Contains a cube name for an OLAP data source, xlCmdDAX - Contains a Data Analysis Expressions (DAX) formula, xlCmdDefault - Contains command text that the OLE DB provider understands, xlCmdExcel - Contains an Excel formula, xlCmdList - Contains a pointer to list data, xlCmdSql - Contains an SQL statement, xlCmdTable - Contains a table name for accessing OLE DB data sources, xlCmdTableCollection - Contains the name of a table collection.

For a ModelConnection object, this type will be set to either xlCmdTable or xlCmdDAX. The isolated connection ThisWorkbookDataModel to the data model will be of type xlCmdCube.

ActiveWorkbook.Connections(1).ModelConnection.CommandType = xlCmdCube