Excel VBA：PivotCaches.Create 类型不匹配

Excel VBA: PivotCaches.Create Type Mismatch

提问by Roy Kaminski

I used to work with vb a long time back (as part of the visual studio pkg) back in the day, and a new job has me working a little bit with VBA as part of a side project. I wouldn't say I'm straight of the noobie boat, but I'm still re-learning a lot of what I used to do (not to mention, incorporating excel specific bits that wasn't touched upon when I was learning VB in visual studio)

我曾经在很长一段时间内使用 vb（作为 Visual Studio pkg 的一部分），而一份新工作让我在一个副项目中使用 VBA。我不会说我是菜鸟，但我仍在重新学习我以前做过的很多事情（更不用说，结合了我在学习 VB 时没有接触过的 excel 特定部分）在视觉工作室）

I've already had several pivot tables done via vba in another projects, but I have a particular project right now that has me stumped.

我已经在另一个项目中通过 vba 完成了几个数据透视表，但是我现在有一个特定的项目让我感到困惑。

I've been reusing the code for some of the Pivot tables listed in a VBA Excel reference book, and it has been working fine just far. But in this particular workbook, a button is pressed on the worksheet which calls a function while passing a few variables along. Everything's usually fine and dandy, but as I try to create the Cache, I receive an error telling me that I have a mismatch error? From my understanding, strings and variants can be passed as source data when creating a pivot cache, but having repeated the code below several times before, I've had no issue until now.

我一直在重复使用 VBA Excel 参考书中列出的一些数据透视表的代码，并且它一直运行良好。但是在这个特定的工作簿中，在工作表上按下了一个按钮，该按钮在传递一些变量的同时调用一个函数。一切通常都很好，但当我尝试创建缓存时，我收到一个错误，告诉我我有一个不匹配错误？根据我的理解，在创建数据透视缓存时，字符串和变体可以作为源数据传递，但是之前多次重复下面的代码，直到现在我都没有问题。

---

---

Private Function createPivotTable(ByRef sheetName As Variant, ByVal sheetNum As Integer)

Dim DataRange As Range
Dim pivotTableCache As PivotCache
Dim pivotTableReport As PivotTable

ActiveWorkbook.Worksheets("EXAMPLE SHEET").Activate
Range("A1").Select
Set DataRange = Selection.CurrentRegion

Worksheets.Add.Name = sheetName

Set pivotTableCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, _ 
      SourceData:=DataRange)
Set pivotTableReport = pivotTableCache.createPivotTable(TableDestination:=ActiveWorkbook.Worksheets(sheetName).Range("A1"), _
TableName:="PVR")

.... 

So I'm essentially stuck. I've found another thread that sort of danced around a similar problem here. The top most rated comment did solve one issue, in that I no longer has a mismatch when I passed it along the range location as a string, but then as I tried to create the actual table itself, I kept getting a 1004 error telling me that I need to select more than one row of data (from what I could interpret - sadly enough, the msgbox for the error code cuts off the line which is kind of funny haha). But, and I've double checked, the selection does indeed grab my entire table (all 6 columns and 1000 something rows).

所以我基本上被卡住了。我发现另一个线程那种手舞足蹈了类似的问题在这里。评分最高的评论确实解决了一个问题，因为当我将它作为字符串沿着范围位置传递时不再出现不匹配，但是当我尝试创建实际表本身时，我不断收到 1004 错误告诉我我需要选择多于一行的数据（根据我的理解 - 很遗憾，错误代码的 msgbox 切断了这条线，这有点有趣，哈哈）。但是，我已经仔细检查过，选择确实占据了我的整个表格（所有 6 列和 1000 行）。

Any help or pointing in the right direction would be appreciated.

任何帮助或指向正确方向将不胜感激。

*To verify; I pass along sheetName as a variant instead of a string because I use an input box asking the user to name the worksheet, and as a fail safe, dimensioning the handle for the inputbox as a variant allows me to check if the user has cancelled the operation (which then returns a value of false instead of a blank string).

*验证; 我将 sheetName 作为变体而不是字符串传递，因为我使用输入框要求用户命名工作表，并且作为故障安全，将输入框的句柄尺寸标注为变体允许我检查用户是否已取消操作（然后返回 false 值而不是空字符串）。

回答by GetUserName

It looks like although you have the proper range identified, it isn't finding the data because your range doesn't refer to the sheet that contains the data. You might want to try:

看起来虽然您确定了正确的范围，但它没有找到数据，因为您的范围没有引用包含数据的工作表。您可能想尝试：

Private Function createPivotTable(ByRef sheetName As Variant, ByVal sheetNum As Integer)

   Dim pivotTableCache As PivotCache
   Dim pivotTableReport As PivotTable

   Worksheets.Add.Name = sheetName

   Set pivotTableCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, _ 
     SourceData:=Sheets("EXAMPLE SHEET").Range("A1").CurrentRegion)
   Set pivotTableReport = pivotTableCache.createPivotTable( _
     TableDestination:=ActiveWorkbook.Worksheets(sheetName).Range("A1") _
     , TableName:="PVR")

   .... 

回答by Sama Tori

it's a bit late in time, but I stumbled with a similar problem and maybe my solution is helpful for others.

时间有点晚了，但我偶然发现了类似的问题，也许我的解决方案对其他人有帮助。

First, microsoft's documentation says:

首先，微软的文档说：

"When passing a Range, it is recommended to either use a string to specify the workbook, worksheet, and cell range, or set up a named range and pass the name as a string. Passing a Range object may cause "type mismatch" errors unexpectedly."

“在传递 Range 时，建议要么使用字符串指定工作簿、工作表和单元格范围，要么设置命名范围并将名称作为字符串传递。传递 Range 对象可能会导致“类型不匹配”错误不料。”

So in my experience, passing a range object works OK most of the times, but sometimes a type mismatch is returned. The "unexpectedly" ending is what made me go for a workaround, which is very simple. I found that the method will accept DataRange.Address only if the activesheet is the data sheet, so I do:

因此，根据我的经验，传递范围对象在大多数情况下都可以正常工作，但有时会返回类型不匹配。“出乎意料”的结局让我选择了一个非常简单的解决方法。我发现只有当活动表是数据表时，该方法才会接受 DataRange.Address，所以我这样做：

DataRange.Worksheet.Activate()
cache = ActiveWorkbook.PivotCaches().Create(1, DataRange.Address, 6)
pivot = cache.CreatePivotTable(destination, name, True, 6)

Please, note that I'm doing this in Python by using pywintypes, so in VBA would probably be ".Activate" without parentheses, and object variables declared and set, etc.

请注意，我是通过使用 pywintypes 在 Python 中执行此操作的，因此在 VBA 中可能是“.Activate”而不带括号，以及声明和设置的对象变量等。

"destination" in the CreatePivotTable method is a range object and didn't have any problem so far.

CreatePivotTable 方法中的“目标”是一个范围对象，到目前为止没有任何问题。