相较于普通的数据透视表，PowerPivot具备如下优势：
- 处理海量数据
- 使用多表关系建模
- 支持强大的DAX公式
- 性能优化
- 支持更多统计方式，例如：排重计数（Distnct Count）
如果PowerPivot是基于Excel数据模型的，那么将需要使用VBA创建数据连接，并添加到数据模型中，但是这个过程并不像创建普通数据透视表的数据连接那么简单，需要一些小技巧。
示例文件中的数据表如下图所示。
示例代码如下。
Sub CreateConn(ByVal sConName As String, _ByVal Wb As String, _ByVal Sht As String)Dim sRef As StringsRef = Workbooks(Wb).Sheets(Sht).Range("a1").CurrentRegion.Address(False, False, , True)Debug.Print sRefWorkbooks(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。
