Class WorksheetDataConnection (Excel VBA)

Used to import data into the data model from data on the worksheet such as ranges and tables.

Item

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

Dim wdc as WorksheetDataConnection
Set wdc = ActiveWorkbook.Connections(1).WorksheetDataConnection

WorksheetDataConnection class variables can be set using the WorkbookConnection.WorksheetDataConnection procedure.

CommandText

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

ActiveWorkbook.Connections(1).WorksheetDataConnection.CommandText =

CommandType

Returns or sets one of the XlCmdType enumeration constants. For a WorksheetDataConnection object, this type will be set to xlCmdExcel.

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.
ActiveWorkbook.Connections(1).WorksheetDataConnection.CommandType = xlCmdCube

Connection

Returns the internal connection string to the object in Excel.

Dim strConnection As String
strConnection = ActiveWorkbook.Connections(1).WorksheetDataConnection.Connection