ChangePivotCache提供类型不匹配错误 [英] ChangePivotCache giving type mismatch error
问题描述
我有一个宏，该宏先前已重置两个数据透视表的缓存，而没有任何错误.我只遇到一张表的运行时错误13类型不匹配，而且我不明白为什么它突然不起作用.当两个表的代码相同时，也只适用于一个表.
I have a macro that previously reset the cache of two pivot tables without any error. I'm running into a run time error 13 type mismatch for just 1 of the tables and I do not understand why it's suddenly not working. And for just the one table as well when the code is identical for both tables.
Sub refresh_caches()
Dim cpt As PivotTable, chpt As PivotTable
Set ca2 = Sheets("claim edit").Range("A2")
Set cha2 = Sheets("chrg review").Range("A2")
Set cpc = Range(ca2.End(xlToRight), ca2.End(xlDown))
Set chpc = Range(cha2.End(xlToRight), cha2.End(xlDown))
Set cpt = Sheets("Formulas & Pivots").PivotTables("ptClaim")
Set chpt = Sheets("Formulas & Pivots").PivotTables("ptCharge")
cpt.ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
cpc, Version:=xlPivotTableVersion15)
chpt.ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
chpc, Version:=xlPivotTableVersion15)
For Each PivotCache In ActiveWorkbook.PivotCaches
PivotCache.Refresh
Next
End Sub
changepivotcache代码出现运行时错误.当我单步执行并跳到chpt的第二个代码时，它将更新该表.
The run time error is hitting on the changepivotcache code. When I step through and skip to the second code for chpt, it updates that table fine.
任何帮助将不胜感激.
编辑:刷新数据源(即数据连接)后出现错误.即使直接将范围引用为sourcedata，我也会遇到相同的错误.尽管如果我手动更改数据源，则数据透视表也会更新.
Edit The error appears after the data source, which is a data connection, is refreshed. I get the same error even when referencing the range directly as the sourcedata. Though if i manually change the data source the pivot table updates just fine.
奇怪的是，当直接引用全部数据时，它将引发错误，但是如果我不引用每一行，它将更新表.
Whats also odd that when referencing the full range of data directly it will throw the error, but if I do not reference every row it will update the table.
NEW Edit 我相信我已经确定了导致问题的原因，尽管我不完全理解为什么或以前是引起问题的原因.我将问题缩小到确切的单元格，并且只要范围中的任何单元格包含超过255个字符，似乎都会发生此问题.我通过向该单元格添加文本并在该范围内的其他单元格中对此进行了测试，并提示错误，同样，可以通过确保该单元格的文本不超过255个字符来防止错误.
NEW Edit I believe I've identified the line causing my issue, though I do not fully understand why or if this is what caused the issue previously. I narrowed the issue down to the exact cell and it seems to happen whenever any cell in the range contains more than 255 characters. I tested this with additional cells in the range by adding text to the cell and prompted the error, likewise I could prevent the error by ensuring the text of the cell contained no more than 255 chars.
这是为什么?我可以手动选择数据范围并将其用作数据透视缓存，但是我无法获取代码来执行完全相同的操作.在Google上发现了单元格超过255个字符的问题，但由于我没有具体引用该单元格，只是将其包括在范围内，因此仍然感到困惑.非常感谢您对此有所了解，尤其是在下次数据提取超出此限制的单元格值时，我可以找到一种解决方法.
Why is this? I can manually select the data range and use it as my pivotcache but I cannot get the code to do the exact same thing. Found some issues with cells exceeding 255 chars on google but am still baffled as I'm not referencing the cell specifically, just including it in a range. Would really appreciate some insight on this, especially so I can find a workaround for the next time the data pulls a cell value exceeding this limit.
推荐答案
尽管我仍然不完全理解为什么每当包含超过255个字符的单元格包含在数据范围内时，代码就会出错，但这确实是原因.
Although I still do not fully understand why the code errors out whenever a cell containing over 255 characters are included in the data range, it does appear to be the cause.
我已经实现了一些代码，可以简单地识别单元格并在设置PivotCache之前将文本减少到可接受的数量.
I've implemented a bit of code to simply identify the cells and reduce the text down to the acceptable amount before setting the PivotCache.
Dim c as Range
For each c in Range("SpecialtyClaimEdit") 'the name of my data connection
If Len(c) > 255 Then
c = Left(c, 255)
End If
Next
这篇关于ChangePivotCache提供类型不匹配错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
