各位仁兄，快帮我看看这个数据透视表的代码错在哪了呀？

lindah · 发表于 2006-2-20 15:19

各位仁兄，快帮我看看这个数据透视表的代码错在哪了呀？老是出错！多谢了！

gmMRccyM.rar (65.84 KB, 下载次数: 29)

[此贴子已经被作者于2006-2-20 15:23:12编辑过]

shuyee · 发表于 2006-2-20 15:35

错在这句. With ptquantity.PivotFields("quantity")你的数据源是C1:C9,而quantity列是在G列,当然要出错.

lindah · 发表于 2006-2-20 15:43

在这里C1:C9不是代表第1列到第9列的意思吗？请多指教！

lindah · 发表于 2006-2-20 16:39

要下班了，自己顶一下！大家多帮忙呀，希望明天一早上班可以看到大虾们的回复，谢谢了:)

nawong · 发表于 2006-2-20 17:01

is this OK !

Sub nawong()
Dim ptquantity As PivotTable
Set ptquantity = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Sheet1'!A1:I1301").CreatePivotTable(TableDestination:="")

ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

ptquantity.SmallGrid = False

With ptquantity.PivotFields("doc number")
.Orientation = xlRowField
.Position = 1
End With
With ptquantity.PivotFields("quantity")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
End With

End Sub

nawong · 发表于 2006-2-20 17:18

You can try this ,too

Sub nawongB()
With X
Set X = CreateObject("adodb.connection")
.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;HDR=Yes';data source=" & ThisWorkbook.FullName
Sql = " select [doc number], sum(quantity) from [Sheet1$] group by [doc number]"
Sheets("Sheet2").[d5].CopyFromRecordset .Execute(Sql)
End With
End Sub

shuyee · 发表于 2006-2-21 08:44

C1:C9表示第三列的第1行到第9行,只有9个单元格.如果要取9列:[a1].resize(1,9).entirecolumn 注意这是个range.

再就是你这里最好只取数据区.比如[a1].currentregion

lindah · 发表于 2006-2-21 09:59

Dear nawong, 这个还是不行呀，上面提示说“不能取得PivotTable的PivotFields属性”，我操作的时候数据透视表倒是出来了，可里面没有相应的数据，都是空的！再请赐教，谢谢！

以下是引用[I]nawong[/I]在2006-2-20 17:01:06的发言：[BR]
is this OK !

Sub nawong()
Dim ptquantity As PivotTable
Set ptquantity = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Sheet1'!A1:I1301").CreatePivotTable(TableDestination:="")

ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

ptquantity.SmallGrid = False

With ptquantity.PivotFields("doc number")
.Orientation = xlRowField
.Position = 1
End With
With ptquantity.PivotFields("quantity")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
End With

End Sub

lindah · 发表于 2006-2-21 10:08

是这样写吗？ Dim ptquantity As PivotTable Set ptquantity = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ "'Sheet1.'![a1].currentregion").CreatePivotTable(TableDestination:="") ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ptquantity.SmallGrid = False With ptquantity.PivotFields("doc number") .Orientation = xlRowField .Position = 1 End With With ptquantity.PivotFields("quantity") .Orientation = xlDataField .Position = 1 .Function = xlSum End With 这样那上面提示说“引用无效”呀，请指示，多谢了！

以下是引用[I]nawong[/I]在2006-2-20 17:18:26的发言：[BR]
You can try this ,too
Sub nawongB()
With X
Set X = CreateObject("adodb.connection")
.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;HDR=Yes';data source=" & ThisWorkbook.FullName
Sql = " select [doc number], sum(quantity) from [Sheet1$] group by [doc number]"
Sheets("Sheet2").[d5].CopyFromRecordset .Execute(Sql)
End With
End Sub

lindah · 发表于 2006-2-21 10:10

是这样写吗 Dim ptquantity As PivotTable Set ptquantity = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ "'Sheet1.'![a1].currentregion").CreatePivotTable(TableDestination:="") ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ptquantity.SmallGrid = False With ptquantity.PivotFields("doc number") .Orientation = xlRowField .Position = 1 End With With ptquantity.PivotFields("quantity") .Orientation = xlDataField .Position = 1 .Function = xlSum End With 这样写，那上面提示说“引用无效”呀，倒底是哪错了呢，请指示，多谢！

以下是引用[I]shuyee[/I]在2006-2-21 8:44:04的发言：[BR]
C1:C9表示第三列的第1行到第9行,只有9个单元格.如果要取9列:[a1].resize(1,9).entirecolumn 注意这是个range.
再就是你这里最好只取数据区.比如[a1].currentregion

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