PivotCaches.add错误5 - 2003年至2010年宏 [英] PivotCaches.add Error 5 - 2003 to 2010 Macros
问题描述
Sub Create_pivot()
Wbname = ActiveWorkbook.Name
'插入列透视表的空间
列(A:I)。选择
Selection.Insert Shift:= xlToRight
myData = Sheets(ActiveSheet.Name)。[J1] .CurrentRegion.Address
mySheet = ActiveSheet.Name& !
tableDest =[& Wbname& ]& mySheet& R1C1
>>>>> ActiveDirectory.PivotCaches.Add(SourceType:= xlDatabase,SourceData:= _
mySheet& myData).CreatePivotTable TableDestination:= tableDest,TableName _
:=RTP_alerts,DefaultVersion:= xlPivotTableVersion10
With ActiveSheet.PivotTables(RTP_alerts)。PivotFields(Application)
.Orientation = xlRowField
.Position = 1
End with
With ActiveSheet.PivotTables(RTP_alerts ).PivotFields(Object)
.Orientation = xlRowField
.Position = 2
结束
ActiveSheet.PivotTables(RTP_alerts)。AddDataField ActiveSheet.PivotTables(_
RTP_alerts)。PivotFields(警报),警报计数,xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars(PivotTable)。Visible = False
列(G:I)。选择
Selection.Delete Shift:= xlToLeft
范围(D2 )选择
ActiveCell.FormulaR1C1 =Owner
Range(E2)。选择
ActiveCell.FormulaR1C1 =问题单
列(E:E) .ColumnWidth = 13
范围(F2)。选择
ActiveCell.FormulaR1C1 =注释
列(F:F)。ColumnWidth = 48
End Sub
他们更改了的PivotCaches
。您在2007 - 2010年需要的方法(使用VBA版本7而不是版本6)是
PivotCaches.Create
您可以使用条件编译来创建可同时工作的代码,如下所示:
Dim pc As PivotCache
Dim pt As PivotTable
Dim lVBAVer As Long
lVBAVer = CLng(Application .VBE.Version)
#If lVBAVer< = 6然后
设置pc = ActiveWorkbook.PivotCaches.Add(xlDatabase,Sheet1.UsedRange)
#Else
设置pc = ActiveWorkbook.PivotCaches.create(xldtatabase,Sheet1.UsedRange)
#End如果
设置pt = pc.CreatePivotTable(Sheet2.Range(A3))
之前的散列如果/ EndIf
关键字意味着你不会当使用该版本中不存在的方法,但是仍然会执行的时候,使用编译错误。
I have an Excel 2003 .xls file that I am trying to run in Excel 2010. I first saved the file as .xlsm and added the directory as trusted in the Trust Center. I am getting an error code (indicated below by the arrows). Note: If I change the PivotTableVersion to 12, it still gives me the same error. Code is below.
Sub Create_pivot()
Wbname = ActiveWorkbook.Name
' Insert columns to make room for pivot table
Columns("A:I").Select
Selection.Insert Shift:=xlToRight
myData = Sheets(ActiveSheet.Name).[J1].CurrentRegion.Address
mySheet = ActiveSheet.Name & "!"
tableDest = "[" & Wbname & "]" & mySheet & "R1C1"
>>>> ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
mySheet & myData).CreatePivotTable TableDestination:=tableDest, TableName _
:="RTP_alerts", DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables("RTP_alerts").PivotFields("Application")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("RTP_alerts").PivotFields("Object")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("RTP_alerts").AddDataField ActiveSheet.PivotTables( _
"RTP_alerts").PivotFields("Alerts"), "Count of Alerts", xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable").Visible = False
Columns("G:I").Select
Selection.Delete Shift:=xlToLeft
Range("D2").Select
ActiveCell.FormulaR1C1 = "Owner"
Range("E2").Select
ActiveCell.FormulaR1C1 = "Problem Ticket"
Columns("E:E").ColumnWidth = 13
Range("F2").Select
ActiveCell.FormulaR1C1 = "Comments"
Columns("F:F").ColumnWidth = 48
End Sub
They changed the object model for PivotCaches
. The method you need in 2007-2010 (that use VBA version 7 instead of version 6) is
PivotCaches.Create
You can use conditional compilation to create code that will work in both, like this:
Dim pc As PivotCache
Dim pt As PivotTable
Dim lVBAVer As Long
lVBAVer = CLng(Application.VBE.Version)
#If lVBAVer <= 6 Then
Set pc = ActiveWorkbook.PivotCaches.Add(xlDatabase, Sheet1.UsedRange)
#Else
Set pc = ActiveWorkbook.PivotCaches.create(xldtatabase, Sheet1.UsedRange)
#End If
Set pt = pc.CreatePivotTable(Sheet2.Range("A3"))
The hashes preceding the If/EndIf
keywords means that you wont get compile errors when using methods that don't exist in that version, but that it will still execute.
这篇关于PivotCaches.add错误5 - 2003年至2010年宏的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!