VBA与数据库——写个类操作ADO_使用RecordSet创建透视表

xyj

发布于 2021-12-01 20:11:00

1.8K0

发布于 2021-12-01 20:11:00

文章被收录于专栏：VBA 学习VBA 学习

在Excel中，数据透视表是一个非常强大的工具，而且非常适合普通人使用，不需要有什么高深技巧，通过一些拖拽操作就能够完成较为复杂的数据汇总、分析等操作。

接触sql语句之后，发现数据透视表其实和sql语句的原理是一样的，不知道它的底层是不是就是使用了sql语句。

在Excel中使用vba来创建透视表可以用这样的语句：

ActiveWorkbook.PivotCaches.Add(xlDatabase, "数据源单元格地址").CreatePivotTable TableDestination:=Range("A3")

其中xlDatabase是枚举类型XlPivotTableSourceType 中的一个，它包含以下几项：

名称             值  说明
xlConsolidation  3  多重合并计算数据区域。
xlDatabase       1  Microsoft Excel 列表或数据库。
xlExternal       2  其他应用程序中的数据。
xlPivotTable  -4148  与另一数据透视表相同来源。
xlScenario       4  数据基于使用方案管理器创建的方案。

Excel本身并不是一个真正的数据库，在vba中使用ado更多的是操作外部的数据库，有时候是直接提取数据查看就可以，有时候还需要对数据进行一些汇总、计数等操作。

这时候就需要构建好sql语句来完成汇总、计数等操作，然后将数据读取到Excel中进行展示，如果需要查看的分类汇总条件改变了，就又得重新构建sql语句。

如果将数据源读取到透视表，再使用透视表的功能进行处理就可以简化sql语句的编写，也不需要再重新读取数据。

xlExternal指明的就是外部的数据源，可以通过sql语句读取出数据，然后使用这个数据来创建透视表，在CADO里面增加1个函数：

'rng            透视表的位置
Function ResultToPivotCache(StrSql As String, rng As Range) As Long
    Dim pc As PivotCache
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    
    On Error GoTo errHandle

    rst.Open StrSql, AdoConn, adOpenStatic, adLockReadOnly

    Set pc = rng.Parent.Parent.PivotCaches.Add(xlExternal)
    Set pc.Recordset = rst
    pc.CreatePivotTable rng
    ResultToPivotCache = RetCode.RetSucce
    
    Exit Function

errHandle:
    StrErr = Err.Description
    ResultToPivotCache = RetCode.RetErr
End Function

调用测试：