PivotCache.Create无法处理大范围 [英] PivotCache.Create unable to handle large Range

问题描述

运行正常:

Dim pc As PivotCache
With tgtBook.Sheets("Data")
    Set pc = tgtBook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Range("A1:B5"), Version:=Excel.xlPivotTableVersion14) ' Range("myMthdata"))
End With

抛出运行时错误'13'类型不匹配:

The throws a Run-time error '13' Type mismatch:

Dim pc As PivotCache
With tgtBook.Sheets("Data")
    Set pc = tgtBook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Range("A1:B500000"), Version:=Excel.xlPivotTableVersion14) ' Range("myMthdata"))
End With

唯一的区别是SourceData中的行数.这是错误还是在逻辑上背后存在逻辑上的原因?

The only difference is the number of rows in the SourceData. Is this a bug or is there a logical reason behind this seeming inconsistency?

编辑

在进一步调查中，B65536似乎可以工作，而更大的问题会出现问题.这是旧版本的Excel中不是excel-2010的最大行数.感觉像个虫子.

On further investigation it seems like B65536 will work whereas anything greater will bug. This is the max number of rows in the old version of Excel not excel-2010. Feels like a bug.

有人知道解决方法吗?

推荐答案

以我的经验，如果使用R1C1样式引用而不是Range对象或A1样式字符串，效果会更好:

In my experience, it works better if you use an R1C1 style reference rather than a Range object or an A1 style string:

Dim pc As PivotCache
    Set pc = tgtBook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Data!R1C1:R500000C2", Version:=Excel.xlPivotTableVersion14)

这篇关于PivotCache.Create无法处理大范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！