vba利用外部数据源创建数据透视表1004错误

aodi2004 · 发表于 2023-10-1 16:17

想用外部数据源创建数据透视表，总出现1004错误。录制的宏也无法重新创建（把录制宏时创建的透视表删除后，再启动录制的宏，也是出同样的1004错误。困扰了我两个月，请大神们赐教！

3190496160 · 发表于 2023-10-2 09:07

既然用vba了，那就直接用vba实现透视表的效果，不行吗

飞天篮球猪 · 发表于 2023-10-2 11:14

SourceData:="连接名称"
换成 SourceData:=ThisWorkbook.Connections(1).Name试试。
如果不是连接，而是工作表里的range，可以试试：
set datarng=range()
set pvcache=ActiveWorkbook.PivotCaches.Add(xlDatabase, datarng)
以上供参考。

aodi2004 · 发表于 2023-10-2 20:58

3190496160 发表于 2023-10-2 09:07
既然用vba了，那就直接用vba实现透视表的效果，不行吗

直接用vba实现透视表结果的话，就不方便使用过程中的其他灵活变换，谢谢你的建议

aodi2004 · 发表于 2023-10-2 21:05

本帖最后由 aodi2004 于 2023-10-3 21:59 编辑

飞天篮球猪发表于 2023-10-2 11:14
SourceData:="连接名称"
换成 SourceData:=ThisWorkbook.Connections(1).Name试试。
如果不是连接，而是 ...

你讲的方法也试过，也不行。神奇的是如果创建透视表时，如果TableDestination设为空，即TableDestination:=""时，可以创建透视表，但是是在一张新增的sheet中创建,说明PivotCaches.create部分没有问题，问题出在pivotcache.CreatePivotTable TableDestination:="Sheet2!R1C1", TableName:="数据透视表66", DefaultVersion:=8 这部分。但这部分只有两个关键参数：TableDestination、TableName，这两个参数还有什么讲究？

tuujjjj · 发表于 2023-10-4 00:18

Sub 数据透视表样式()
Dim rng As Range
Set rng = Range("A1").CurrentRegion
Worksheets("Sheet1").PivotTableWizard SourceType:=xlDatabase, SourceData:=rng, _
TableDestination:=Range("G4"), TableName:="我的表1", _
RowGrand:=True, ColumnGrand:=True
'--------------------------------------------------------------Range("G4")是你要放透视表的位置
'--------------------------------------------------------------以上是建立透视表的样式，下面就可以录制宏来达到你的要求了
With ActiveSheet.PivotTables("我的表1").PivotFields("姓名")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("我的表1").PivotFields("课程")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("我的表1").AddDataField ActiveSheet.PivotTables("我的表1"). _
PivotFields("成绩"), "求和项:成绩", xlSum
End Sub

复制代码

试试吧

tuujjjj · 发表于 2023-10-4 00:57

本帖最后由 tuujjjj 于 2023-10-4 08:41 编辑

Sub ADO连接外部数据源建立透视表()
Dim sql As String
Dim strTable As String
strTable = ThisWorkbook.FullName '这里放你要连接的外部数据的全名比如（"E:\d\ded.xlsx")
Set Cnn = CreateObject("ADODB.Connection")
Dim xrng As Range
Cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;IMEX=2';Data Source=" & ThisWorkbook.FullName
'_____________________________________________________________这里以上是固定格式，这个是连接xlsx文件的
'在这写SQL语句导入外部数据
sql = "select * from [" & strTable & "].[Sheet1$] "
'___________________________________________________
Set objPivotCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal)
Set objPivotCache.Recordset = Cnn.Execute(sql) '执行SQL语句，将数据装入数据集
objPivotCache.CreatePivotTable TableDestination:=Range("G4"), TableName:="我的表1"
'--------------------------------------------------------------Range("G4")是你要放透视表的位置
'--------------------------------------------------------------以上是建立透视表的样式，下面就可以录制宏来达到你的要求了
With ActiveSheet.PivotTables("我的表1").PivotFields("姓名")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("我的表1").PivotFields("课程")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("我的表1").AddDataField ActiveSheet.PivotTables("我的表1"). _
PivotFields("成绩"), "求和项:成绩", xlSum
'__________________________________以下关闭数据库连接
Cnn.Close: Set Cnn = Nothing
End Sub

复制代码

忘记你是要连接外部数据建立透视表了

aodi2004 · 发表于 2023-10-4 10:16

tuujjjj 发表于 2023-10-4 00:18
试试吧

SourceType:=xlDatabase的情况下建透视表都正常，我是SourceType:=xlExternal，是利用外部数据源创建数据透视表出现问题。

tuujjjj · 发表于 2023-10-4 10:31

还有一个，没有审核完

tuujjjj · 发表于 2023-10-4 10:36

tuujjjj 发表于 2023-10-4 00:57
忘记你是要连接外部数据建立透视表了

这个符合你要求了吧

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

[求助] vba利用外部数据源创建数据透视表1004错误

评分