一簿中多表创建透视表的练习

OKJSJSF

上面二段代码，都不能在连接属性对话框定义选项中生成有效的命令文本，只有一个表名与锁号。看来自己对SQL连接技术真是一窍不通。

OKJSJSF

看来我只能把自动生成的SQL命令文本放在剪贴板中，用手工的方法操作，在创建时粘贴进连接属性对话框的文本框中。

OKJSJSF

特向家人求助，文件如下： SQLunion.rar (20.46 KB, 下载次数: 0)

2022-6-4 15:56 上传

点击文件名下载附件

OKJSJSF

如果把存放透视表的“合并”表另存在一个新工作簿，就可以对其它一个或多个工作簿的一个或多个表合并创建一个透视表，也就是“连接”能成功、有效。为什么全部在一个工作簿中就不行？如何把数据放进“缓存”是关键吧？表达式该怎么改？

OKJSJSF

完工：Sub 创建一簿多表数据透视表()
    Dim sh As Worksheet, r As Integer, sql As String, cnn As New ADODB.Connection, rst As New ADODB.Recordset, pc As PivotCache, pt As PivotTable
    For Each sh In ThisWorkbook.Worksheets
        With sh
            If .Name <> "合并" Then
                r = .Range("a5").End(xlDown).Row
                If sql <> "" Then sql = sql & " union all"
                sql = sql & " select 姓名, 证件号, 电话, """ & .Name & """ as 表名 from [" & .Name & "$A5:C" & r & "]"
            End If
        End With
    Next
    For r = 1 To ThisWorkbook.Connections.Count
        ThisWorkbook.Connections(1).Delete
    Next
    For Each pt In Worksheets(3).PivotTables
        pt.TableRange2.Clear
    Next
    MsgBox sql, , "自动编辑的SQL命令文本："
   
    cnn.Open "Provider=Microsoft.ace.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
    rst.Open sql, cnn
    Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal)
    Set pc.Recordset = rst
    Set pt = pc.CreatePivotTable(Worksheets(3).Range("a1"), TableName:="pt1")
    MsgBox "透视表创建完成。"
End Sub

OKJSJSF

这技术不抄袭别人的，我怎么搞得出来？啊！

OKJSJSF

上述ADO方法有一个问题，连接属性对话框中命令文本等都是不能变动的灰色。

OKJSJSF

终于了解了一桩心事。

OKJSJSF

现在终于知道，无表记录集就是【ADO记录集】，它可以作为创建透视表的数据源，当然，可以作为多表合并（查询）的数据源。

OKJSJSF

OKJSJSF 发表于 2022-6-5 20:21
完工：Sub 创建一簿多表数据透视表()
    Dim sh As Worksheet, r As Integer, sql As String, cnn As New ...

本代码修改后可以用于同一文件夹中，合并多个只含一个工作表的工作簿的数据，创建透视表。但今天又发现一个问题，合并多个含多个工作表的工作簿时，无法创建透视表，提示运行错误1004定义了过多的字段。待解决。