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

OKJSJSF · 发表于 2021-5-30 00:11

OKJSJSF 发表于 2021-5-23 10:20
昨天在工作表名称上加上数字，就无法创建透视表，如上出错提示！怪！

把创建代码用在单位电脑上，又出现了这个错误提示，无法创建透视表，真是太难搞定了。

zdj9007 · 发表于 2021-6-1 11:30

这种代码好学吗，我想用一用呢！

OKJSJSF · 发表于 2021-6-2 22:54

zdj9007 发表于 2021-6-1 11:30
这种代码好学吗，我想用一用呢！

好学的，只是对象、方法、属性太多。
我的加载宏最新链接: https://pan.baidu.com/s/1LRhoycrtgkJPpT6q26AM7Q 提取码: wx3j 复制这段内容后打开百度网盘手机App，操作更方便哦

工程密码六个1

OKJSJSF · 发表于 2021-6-14 11:32

OKJSJSF 发表于 2021-4-24 23:49
多表创建透视表应该是以一个看不见的access联合查询数据表或文本文件作为缓存进行分类汇总的。该数据表或文 ...

通过对精粹中几个ADO示例的练习，发现这个通过SQL连接技术生成的看不见的表就是叫做无表记录集recordset的东西，它也可以直接通过工作表中的数据直接addnew生成。它象数组一样存在于内存中，有行列数，可以相互转换。

OKJSJSF · 发表于 2021-6-14 18:03

OKJSJSF 发表于 2021-6-14 11:32
通过对精粹中几个ADO示例的练习，发现这个通过SQL连接技术生成的看不见的表就是叫做无表记录集recordset ...

还有集合collection。集合不存在行列数，不存在数据类型，相当于把数组或记录集中的数据统统罗列在一起。数组存在行列数，可以不区分数据类型。记录集相当于按行或列区分了数据类型的数组。三者数据多少量可以说没有上限。

OKJSJSF · 发表于 2021-6-20 22:19

参照精粹练习连接技术。

excel通过连接导入数据练习.rar (61.43 KB, 下载次数: 1)

OKJSJSF · 发表于 2021-7-14 22:01

本帖最后由 OKJSJSF 于 2021-7-14 22:44 编辑

excel从access中查找数据.rar (901.37 KB, 下载次数: 0) 本文档中ADO语句如何改为后期绑定，我试了下没成功，特在本坛求助。

OKJSJSF · 发表于 2022-6-4 15:30

本帖最后由 OKJSJSF 于 2022-6-4 15:33 编辑

虽然同一文件夹中多工作簿多工作表或多工作簿各一工作表用VBA-SQL语句学到了创建合并透视表的办法，但在一个工作簿中用VBA-SQL语句为多个工作表创建合并透视表却至今未能创建成功。总是说连接已经失去。

OKJSJSF · 发表于 2022-6-4 15:37

下面的过程无法在一簿中为多表创建透视表。
Sub 按钮1_Click()
Dim sh As Worksheet, r As Integer, sql As String, pc As PivotCache, pt As PivotTable
For Each pt In Worksheets(3).PivotTables
      pt.TableRange2.Clear
Next
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 [" & ThisWorkbook.FullName & "].[" & .Name & "$A5:C" & r & "]"
         End If
      End With
Next
MsgBox sql, , "自动编辑的SQL命令文本："
' Debug.Print sql

Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlExternal)
With pc
      .Connection = Array("OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=YES"";")
      .CommandType = xlCmdSql
      .CommandText = sql
      .RefreshOnFileOpen = True
End With
Set pt = pc.CreatePivotTable(tabledestination:="合并!R1C1", tablename:="pt1")
Set sh = Nothing
Set pt = Nothing
Set pc = Nothing
MsgBox "透视表创建完毕。"
End Sub

OKJSJSF · 发表于 2022-6-4 15:37

加入连接语句也没用。
Sub 按钮1_Click()
Dim sh As Worksheet, r As Integer, sql As String, pc As PivotCache, pt As PivotTable
For r = 1 To ThisWorkbook.Connections.Count
      ThisWorkbook.Connections(1).Delete
Next
For Each pt In Worksheets(3).PivotTables
      pt.TableRange2.Clear
Next
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 [" & ThisWorkbook.FullName & "].[" & .Name & "$A5:C" & r & "]"
         End If
      End With
Next
MsgBox sql, , "自动编辑的SQL命令文本："
' Debug.Print sql

ThisWorkbook.Connections.AddFromFile ThisWorkbook.FullName
Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:=ThisWorkbook.Connections(1).Name, Version:=xlPivotTableVersion12)
With pc
      .Connection = Array("OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=YES"";")
      .CommandType = xlCmdSql
      .CommandText = sql
      .RefreshOnFileOpen = True
End With
Set pt = pc.CreatePivotTable(tabledestination:="合并!R1C1", tablename:="pt1")
Set sh = Nothing
Set pt = Nothing
Set pc = Nothing
MsgBox "透视表创建完毕。"
End Sub

		自动登录	找回密码
密码			免费注册

[求助] 一簿中多表创建透视表的练习