Excel 2010 vba不能在Excel 2012中工作 [英] Excel 2010 vba not working in Excel 2012
问题描述
ActiveWorkbook.PivotCaches.Create(SourceType:= xlDatabase,SourceData:= _
WorkRange,Version:= xlPivotTableVersion14).CreatePivotTable _
TableDestination:= ActiveSheet.Cells(3,1),TableName:=PivotTable2,DefaultVersion _
:= xlPivotTableVersion14
我的代码复制新的数据表,并将其重命名为Call Data - Date Time,并在每次新表上创建一个新的数据透视表,所以我不认为这是表名造成问题特别是因为它对我来说很好,她是唯一有问题的人,任何想法?
这里是所有的代码:
Sub GeneratePivot()
'
'Macro11宏
'
Dim myDate As Date,aDate
myDate = Date + 7 - 工作日(日期)
aDate = Format(myDate,mm.dd.yyyy)
Dim LValue As Date
LValue = Now
范围(A1)。 b $ b范围(选择,ActiveCell.SpecialCells(xlLastCell))。选择
ActiveWorkbook.Names.Add名称:=WorkRange,参考:=选择
范围(C5) 。选择
Application.Goto参考:=WorkRange
Sheets.Add
ActiveSheet.Name =SummaryData&格式(DateTime.Now,MM.dd.yy hh.mm.ss)
ActiveWorkbook.PivotCaches.Create(SourceType:= xlDatabase,SourceData:= _
WorkRange版本:= xlPivotTableVersion14).CreatePivotTable _
TableDestination:= ActiveSheet.Cells(3,1),TableName:=PivotTable2,DefaultVersion _
:= xlPivotTableVersion14
ActiveSheet.Select
单元格(3,1)。选择
使用ActiveSheet.PivotTables(PivotTable2)。PivotFields(Month)
.Orientation = xlColumnField
.Position = 1
结束
使用ActiveSheet.PivotTables(PivotTable2)。PivotFields(Site / Location)
.Orientation = xlRowField
.Position = 1
结束
With ActiveSheet.PivotTables(PivotTable2)。PivotFields(Called Number)
.Orientation = xlRowField
结束
ActiveSheet.PivotTables(PivotTable2)。AddDataField ActiveSheet.PivotTables(_
PivotTabl e2)PivotFields(Called Number),被叫号码计数,xlCount
使用ActiveSheet.PivotTables(PivotTable2)PivotFields(Site / Location)
.PivotItems(#N / A)。Visible = False
结束
列(A:A)。选择
Selection.Insert Shift:= xlToRight, CopyOrigin:= xlFormatFromLeftOrAbove
End Sub
所以我更新了可疑行:
Dim PC As PivotCache
Dim pt As PivotTable
设置PC = ActiveWorkbook。 PivotCaches.Create(SourceType:= xlDatabase,SourceData:= _
WorkRange,Version:= xlPivotTableVersionCurrent)
设置pt = PTCache.CreatePivotTable _
(TableDestination:= ActiveSheet .Cells(3,1),_
TableName:=PivotTable1,_
DefaultVersion:= xlPivotTableVersionCurrent)
PC.CreatePivotTable TableDestination:= ActiveSheet.Cells(3, 1),TableName:=PivotTable1,DefaultVersion _:= xlPivotTableVersio nCurrent
它在设置PC上失败。我也尝试过xlPivotTableVersion15,但是失败了。
我认为问题是因为数据透视表的版本即本节:
版本:= xlPivotTableVersion14
版本14适用于Excel 2010.我不知道2012年的版本号。
尝试将其更改为:< br>
DefaultVersion:= xlPivotTableVersionCurrent
,看看它是否运行。
这是一个列表从这里: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlpivottableversionlist(v=office.15).aspx )与可用版本。我没有看到Excel 2012,所以你的里程可能会有所不同:
xlPivotTableVersion2000是指Excel 2000
xlPivotTableVersion10是指Excel 2002
xlPivotTableVersion11引用Excel 2003
xlPivotTableVersion12引用Excel 2007
xlPivotTableVersion14引用Excel 2010
xlPivotTableVersion15引用Excel 2013
xlPivotTableVersionCurrent引用仅提供向后兼容性
I have vba code that creates a pivot table automatically from some data that is copied from another workbook to the current workbook. When I run it on my machine, it is fine. Another person has no problems, either, but one woman is getting an error about "Run-time error '5': Invalid procedure call or argument" and it highlights my code for
"ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"WorkRange", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:=ActiveSheet.Cells(3,1), TableName:="PivotTable2", DefaultVersion _
:=xlPivotTableVersion14
My code copies the new data sheet and renames it every time to "Call Data - Date Time" and creates a new pivot table on a new sheet every time, so I don't think it is the table name causing issues, especially since it runs fine for me. She is the only one having issues. Any ideas?
Here is all of the code:
Sub GeneratePivot()
'
' Macro11 Macro
'
Dim myDate As Date, aDate
myDate = Date + 7 - Weekday(Date)
aDate = Format(myDate, "mm.dd.yyyy")
Dim LValue As Date
LValue = Now
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWorkbook.Names.Add Name:="WorkRange", RefersTo:=Selection
Range("C5").Select
Application.Goto Reference:="WorkRange"
Sheets.Add
ActiveSheet.Name = "SummaryData " & Format(DateTime.Now, "MM.dd.yy hh.mm.ss")
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"WorkRange", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:=ActiveSheet.Cells(3, 1), TableName:="PivotTable2", DefaultVersion _
:=xlPivotTableVersion14
ActiveSheet.Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Month")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Site/Location")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Called Number")
.Orientation = xlRowField
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Called Number"), "Count of Called Number", xlCount
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Site/Location")
.PivotItems("#N/A").Visible = False
End With
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub
So I updated the suspect lines to:
Dim PC As PivotCache
Dim pt As PivotTable
Set PC = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"WorkRange", Version:=xlPivotTableVersionCurrent)
Set pt = PTCache.CreatePivotTable _
(TableDestination:=ActiveSheet.Cells(3, 1), _
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersionCurrent)
PC.CreatePivotTable TableDestination:=ActiveSheet.Cells(3, 1), TableName:="PivotTable1", DefaultVersion _ :=xlPivotTableVersionCurrent
And it failes on Set PC. I also tried xlPivotTableVersion15, but that failed, as well.
I think the problem is because of the version of the PivotTable namely this section:
Version:=xlPivotTableVersion14
Version14 is for Excel 2010. I'm not sure what the version number is for 2012.
Try changing it to:
DefaultVersion:=xlPivotTableVersionCurrent
and see if it runs.
Here is a list (taken from here: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlpivottableversionlist(v=office.15).aspx) with the available versions. I don't see Excel 2012 on there, so your mileage may vary:
xlPivotTableVersion2000 refers to Excel 2000
xlPivotTableVersion10 refers to Excel 2002
xlPivotTableVersion11 refers to Excel 2003
xlPivotTableVersion12 refers to Excel 2007
xlPivotTableVersion14 refers to Excel 2010
xlPivotTableVersion15 refers to Excel 2013
xlPivotTableVersionCurrent refers to Provided only for backward compatibility
这篇关于Excel 2010 vba不能在Excel 2012中工作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!