透视缓存类型不匹配错误VB.NET的Excel [英] Pivot Cache Type Mismatch error VB.NET Excel

问题描述

请参阅下编辑的我在哪里目前在，谢谢你。

Please see bottom edit for where I am currently at, thank you.

我创建了一个数据透视表时的透视表缓存被定义为正常工作：

I have created a pivot table that works fine when the pivot cache is defined as:

        Dim ptCache As Excel.PivotCache = mainHighway.PivotCaches.Add(SourceType:=Excel.XlPivotTableSourceType.xlDatabase, SourceData:=mainHighwayData.Range("a1:v7500"))

我的问题是，从日常的行数发生变化，所以我想出的行数在工作表中，然后用在我的透视表缓存：

My problem is that the number of rows changes from day to day, so I figure out the number of rows in the worksheet and then use that in my pivot cache:

Dim highlRow As Integer
highlRow = mainHighwayData.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row
Dim ptCache As Excel.PivotCache = mainHighway.PivotCaches.Add(SourceType:=Excel.XlPivotTableSourceType.xlDatabase, SourceData:=mainHighwayData.Range("a1:V" & highlRow))

该解决方案为我伟大的工作历史，但是当我在这里做，我得到：

This solution has worked great for me historically, but when I do it here, I get:

Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))

我一直在摆弄它周围公平一点，结果发现，如果我做的：

I have been fiddling around with it a fair bit and found that if I do:

highlRow = 7000
Dim ptCache As Excel.PivotCache = mainHighway.PivotCaches.Add(SourceType:=Excel.XlPivotTableSourceType.xlDatabase, SourceData:=mainHighwayData.Range("a1:V" & highlRow))

它工作得很好，所以我在想什么是我如何定义highlRow使用SpecialCells命令，并将其存储为一个整数的问题。我觉得有某种类型或在这里铸造的问题，但我不能把我的手指上。我想：

It works fine, so I am left wondering what is the problem with how I define highlRow using the specialcells command and store it as an integer. I feel like there is some kind of type or casting issue here, but I can't put my finger on it. I tried:

highlRow = CType(mainHighwayData.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row, Integer)

这是没有好或者。也许我的方法找到最后一行就是在这里不适用？无论如何，我花了很长时间与它修修补补，我只是纺纱我的车轮在这一点上。如果您有什么想法这将是更AP preciated。

And that was no good either. Perhaps my method for finding the last row is just not applicable here? Anyway, I have spent a long time tinkering with it and I am just spinning my wheels at this point. If you have any thoughts it would be much appreciated.

感谢您一如既往左右。

编辑：我也尝试了：

UsedRange.Rows.Count

这曾在历史上，它也无法运行。在这一点上我很好奇，为什么支点缓存专门似乎有这个问题。

Which has worked historically and it was also not able to run. At this point I am very curious as to why pivot cache specifically seems to have this issue.

@charles有一个好主意，试图获得最后一个单元格的其他方式。我想：

@charles had a good idea to try other ways of getting the last cell. I tried:

highlRow = mainHighwayData.Range("A65535").End(XlDirection.xlUp).Row

我把它给了正确的行，但仍引发类型不匹配错误。

I got it to give the correct rows, but it still throws the type mismatch error.

highlRow = mainHighwayData.Cells.Find("*", SearchOrder:=Excel.XlSearchOrder.xlByRows, SearchDirection:=XlSearchDirection.xlPrevious).Row

这将返回适当数量的最后一排，但再次抛出同样的类型不匹配的错误。

This returns the proper number for the last row, but once again throws the same type mismatch error.

编辑：我发现了另一个信息比特，但我不知道该怎么办。数据透视表工作正常，如果它的值是＆LT; = 65536，但第二次我增加至65537我得到的类型不匹配已经困扰我的范围。这适用于所有数字> = 65537.我知道65535，或有用于在Excel中的最后一排abouts，但已不再是这种情况。此外，当我在Excel中手动创建数据透视表我没有问题，它有所有的数据。我使用int或长，所以它不应该是一个溢出或任何东西。任何人有，为什么VB.NET不会让我做出基于数据透视表超过65537行有什么想法？

I found out another bit of information, but I am not sure what to do with it. The pivot table works fine if the values in it are <= 65536, but the second I increase the range to 65537 I get the type mismatch that has been haunting me. This is true for all numbers >= 65537. I know that 65535 or there abouts used to be the last row in excel, but that is no longer the case. Also when I create the pivot table manually in excel I have no trouble and it has all of the data. I am using int or long, so it should not be an overflow or anything. Anyone have any thoughts on why VB.NET will not let me make a pivot table based on data with more than 65537 rows?

推荐答案

那么这是一个有点一场噩梦，但我发现，工程修复。我注意到，人们在VBA有同样的烦恼，他们被切换到R1C1符号，但我无法得到那个工作下去，但我发现这个<一href="http://stackoverflow.com/questions/8450450/type-mismatch-error-when-creating-a-pivot-table-in-excel-with-vba">post并在回答最后一个注释也有解决方案。

Well it was a bit of a nightmare, but I found a fix that works. I noticed that people in vba have the same trouble and they were switching to r1c1 notation, but I couldn't get that working either, but I found this post and the last comment on the answer there had the solution.

highlRow = mainHighwayData.Range("A500000").End(XlDirection.xlUp).Row
Dim rng As Excel.Range
rng = mainHighwayData.Range("A1:Q" & highlRow)
mainHighway.Names.Add(Name:="Range1", RefersTo:=rng)
Dim ptCache As Excel.PivotCache = mainHighway.PivotCaches.Add(SourceType:=Excel.XlPivotTableSourceType.xlDatabase, SourceData:="Range1")

所以，你创建一个名为范围，然后参考。我猜的想法是，你从简单的范围对象需要抽象掉，这将让你做出你的数据透视表。

So you create a named range and then refer to it. I guess the idea is you need to abstract away from simple range objects and that will allow you to make your pivot table.

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