VBA快速创建Excel中数据模型的数据连接

相较于普通的数据透视表，PowerPivot具备如下优势：

• 处理海量数据
• 使用多表关系建模
• 支持强大的DAX公式
• 性能优化
• 支持更多统计方式，例如：排重计数（Distnct Count）

如果PowerPivot是基于Excel数据模型的，那么将需要使用VBA创建数据连接，并添加到数据模型中，但是这个过程并不像创建普通数据透视表的数据连接那么简单，需要一些小技巧。

示例文件中的数据表如下图所示。

示例代码如下。

Sub CreateConn(ByVal sConName As String, _
                ByVal Wb As String, _
                ByVal Sht As String)
    Dim sRef As String
    sRef = Workbooks(Wb).Sheets(Sht).Range("a1").CurrentRegion.Address(False, False, , True)
    Debug.Print sRef
    Workbooks(Wb).Connections.Add2 Name:=sConName, Description:="", _
        ConnectionString:="WORKSHEET;" & Split(sRef, "!")(0), _
        CommandText:=Split(sRef, "]")(1), _
        lcmdtype:=XlCmdType.xlCmdExcel, _
        createmodelconnection:=True, importrelationships:=False
End Sub

【代码解析】
第1~3行代码声明Sub过程，其中3个参数为

• sConName为数据连接的名称
• Wb为工作簿名称
• Sht为工作表名称

第5行代码获取工作表中数据表的引用地址，其参数含义

• 第1个参数RowAbsolute，设置为False，指定行引用不使用绝对引用
• 第2个参数ColumnAbsolute，设置为False，指定列引用不使用绝对引用
• 第4个参数External，设置为True，即使用外部引用格式
  在本示例中其返回值为[Book1.xlsm]Sheet1!A1:C4。

第7~11行代码用于创建数据连接，其他参数为

• 参数Name用于指定数据连接的名称
• 参数ConnectionString用于指定连接字符串，参数值为WORKSHEET;[Book1.xlsm]Sheet1。
• 参数CommandText用于指定命令字符串，参数值为Sheet1!A1:C4。
• 参数lcmdtype用于指定连接类型，参数值为XlCmdType.xlCmdExcel常量。

如下代码将调用CreateConn创建数据连接并添加到数据模型中。

Sub Demo()
    Call CreateConn("DemoConn", ThisWorkbook.Name, "Sheet1")
End Sub

运行后的结果如下图所示，后续可以创建基于数据模型的PowerPivot。