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
ModelConnection class variables can be set using the WorkbookConnection.ModelConnection procedure.
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
Returns a CalculatedMembers object that represents the calculated members in the model connection.
Dim cmsCalculatedMembers As CalculatedMembers Set cmsCalculatedMembers = ActiveWorkbook.Connections(1).ModelConnection.CalculatedMembers
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.
Returns or sets one of the XlCmdType enumeration constants.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