Excel VBA数据透视缓存类型不匹配运行时错误'13' [英] Excel VBA Pivot Cache Type Mismatch Run-Time Error '13'

问题描述

感谢您对此的任何投入.我正在尝试制作一个简单的数据透视表，该数据表从工作表"5个月趋势5月15日"中获取数据，并将其放在我的数据透视表工作表中，该数据表称为关键性错误-数据透视".

Thanks for any input on this. I'm trying to make a simple pivot table that is taking data from sheet "5 Month Trending May 15" and putting it onto my Pivot Table sheet called "Errors By Criticality - Pivot".

当我尝试使用create方法设置数据透视表缓存范围时，它将返回类型不匹配的运行时错误.我已经检查了参数，看起来我已经正确设置了.我确实尝试指定了数据透视表的版本，但仍收到相同的错误.我的代码如下.

When I try to set the pivot cache range with the create method it's returning a runtime error of type mismatch. I've checked the parameters and it looks like I'm setting it up correctly. I did try to specify the PivotTable version and was still getting the same error. My code is below.

我假设这与pvtCache变量或将其设置为范围的方式有关，但我无法找到任何解决方案.

I'm assuming it's something to do with the pvtCache variable or the way I'm setting it to the range but I can't figure any solutions out.

Sub PivotTableCode()

Dim pvtCache As PivotCache 
Dim pvt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem 
'Set the cache of the pivot table
Sheets("5 Month Trending May 15").Select
Set pvtCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range("A2:H38"))

'create the Pivot Table
Sheets("Errors by Criticality - Pivot").Select
Set pvt = ActiveSheet.PivotTables.Add(pvtCache, Range("AP2"), "MyPivotTable") 
End Sub

推荐答案

如果SourceType不是xlExternal，则需要SourceData自变量.它可以是Range对象(当SourceType是xlConsolidation或xlDatabase时)或Excel Workbook Connection对象(当SourceType是xlExternal时).

The SourceData argument is required if SourceType isn't xlExternal. It can be a Range object (when SourceType is either xlConsolidation or xlDatabase) or an Excel Workbook Connection object (when SourceType is xlExternal).

尽管如此，宏记录器将始终在此处为SourceData创建String. (如果Sheet名称中有一个空格).

Despite this, the macro recorder will always create a String here for the SourceData. (It will even create a bad string if the Sheet has a space in the name).

鉴于对宏记录器的偏爱，我经常以String的形式提供其地址.

Given the preference for the macro recorder, I often supply this as a String with the addresses.

在此之前，我已经能够提供Range了，所以我不确定在这种情况下阻止Range使用的具体原因.

I have been able before to supply a Range here so I am not certain what is specifically going on that prevents the Range usage in this case.

要使用String，您的代码应如下所示:

To use a String, your code would look like:

Set pvtCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, "'5 Month Trending May 15'!A2:H38")

这篇关于Excel VBA数据透视缓存类型不匹配运行时错误'13'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！