PivotCaches.Add错误 [英] PivotCaches.Add Errors out
本文介绍了PivotCaches.Add错误的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用excel 2013,并且在调用PivotCaches.Add
I'm using excel 2013 and I am getting invalid procedure call or argument while calling PivotCaches.Add
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set ActiveWorkbook = objExcel.Workbooks.Open("C:\Users\srujan\Desktop\TIME REPORT\fresh\25_Report Time Booking_25.xls")
ActiveWorkbook.Sheets("25_Report Time Booking_25").Select
' Set the range to be pivoted to be called PivotRange
Set PivotTopLeft = ActiveWorkbook.Parent.Worksheets("25_Report Time Booking_25").Range("A1")
Set PivotTopRight = PivotTopLeft.Range("G1")
Set PivotTop = ActiveWorkbook.Parent.Worksheets("25_Report Time Booking_25").Range(PivotTopLeft, PivotTopRight)
Set MyPivotRange = ActiveWorkbook.Parent.Worksheets("25_Report Time Booking_25").Range(PivotTop, PivotTop.Range("G78"))
' Create the pivot table
'ActiveWorkbook.CutCopyMode = False
ActiveWorkbook.Parent.Worksheets.Add
MyPivotRangeName = "'" & MyPivotRange.Parent.Name & "'" & "!" & MyPivotRange.Address(ReferenceStyle = xlR1C1)
MsgBox (MyPivotRangeName)
Set MyPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType = xlDatabase, SourceData = MyPivotRangeName)
MyPivotCache.CreatePivotTable TableDestination = (ActiveWorkbook.Parent.Worksheets("Sheet1").Range("A3")), TableName = "PivotTable1"
ActiveWorkbook.Parent.Worksheets("Sheet1").PivotTables("PivotTable1").PivotTableWizard
ActiveWorkbook.Parent.Worksheets("Sheet1").PivotTables("PivotTable1").SmallGrid = False
ActiveWorkbook.Parent.Worksheets("Sheet1").PivotTables("PivotTable1").AddFields RowFields = "Activity Type", PageFields = "User"
With ActiveWorkbook.Parent.Worksheets("Sheet1").PivotTables("PivotTable1").PivotFields("Effort Spent (Hrs)" & Chr(10) & "($)")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
With ActiveWorkbook.Parent.Worksheets("Sheet1").PivotTables("PivotTable1").PivotFields("Activity Type")
.PivotItems("ANALYSIS").Visible = False
.PivotItems("BUILD").Visible = False
.PivotItems("CARRIERSAP").Visible = False
.PivotItems("HOWTO").Visible = False
.PivotItems("MAINTENANC").Visible = False
.PivotItems("MAINTENANCE").Visible = False
.PivotItems("REVIEW").Visible = False
.PivotItems("STX-SCRIPT").Visible = False
.PivotItems("(blank)").Visible = False
End With
推荐答案
为什么你得到这个错误是因为你应该使用 .Create
而不是 .Add
The reason why you are getting that error is because your are supposed to use .Create
instead of .Add
设置MyPivotCache = ActiveWorkbook.PivotCaches.Create(xlDatabase,MyPivotRangeName)
编辑
除了我在您的问题下的评论之外,您的代码可以像这样进行优化(UNTESTED)
Further to my comments under your question, you code could be optimized (UNTESTED) like this
Sub Sample()
Dim wb As Workbook
Dim ws As Worksheet, newWs As Worksheet
Dim sFile As String, MyPivotRangeName As String
Dim MyPivotRange As Range
Dim pt As PivotTable
Dim MyPivotCache As PivotCache
sFile = "C:\Users\srujan\Desktop\TIME REPORT\fresh\25_Report Time Booking_25.xls"
Set wb = Workbooks.Open(sFile)
Set ws = wb.Sheets("25_Report Time Booking_25")
Set newWs = wb.Worksheets.Add
With ws
Set MyPivotRange = .Range("A1:G78")
MyPivotRangeName = "'" & ws.Name & "'!" & MyPivotRange.Address(ReferenceStyle = xlR1C1)
Set MyPivotCache = wb.PivotCaches.Create( _
SourceType = xlDatabase, _
SourceData = MyPivotRangeName)
Set pt = MyPivotCache.CreatePivotTable( _
tabledestination:=(newWs.Name & "!R3C1"), _
tablename:="PivotTable1")
'
'~~> Rest of the code
'
End With
End Sub
这篇关于PivotCaches.Add错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文