运行时错误5有时在VBA中创建数据透视表 [英] Runtime Error 5 Sometimes in VBA Creating PivotTable

问题描述

我一直在64位版本的Excel 2016中记录/编写宏.我需要在其末尾创建一个数据透视表.我在计算机上尝试了四种不同的方法，它们都可以工作.

I have been recording/writing a macro in Excel 2016, 64 bit version. I need to create a PivotTable at the end of it. I have tried four different ways on my computer and they all work.

但是！当我将文件发送给另一个用户时，数据透视表"部分始终失败，并且始终返回

HOWEVER! When I send the file to another user, the PivotTable section always fails and it always returns

运行时错误5:无效的过程调用或参数.

Run-time Error 5: Invalid procedure call or argument.

另一个用户位于虚拟桌面(Windows 2008 R2)和32位版本的Office 2013上.

The other user is on a virtual desktop (Windows 2008 R2) and Office 2013, which is the 32 bit version.

我确实研究了这个问题，这就是为什么我在下面有这么多不同的代码，但是无论我做什么，其他用户都会得到运行时错误5 .同样，所有这些操作都可以在我的计算机上正常进行，因此我假设存在版本问题，但是我承认我正在猜测.我想念什么?

I did research this issue, which is why I have so many different bits of code below, but no matter what I do, the other user gets the Run-time Error 5. Again, all of these work as intended on my computer, so I'm assuming there's a version issue, but I admit that I'm guessing at this point. What am I missing?

宏录制器代码:

Range("A1").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
  "Table1", Version:=6).CreatePivotTable TableDestination:="Summary!R1C1", _
  TableName:="PivotTable3", DefaultVersion:=6

我在网上找到的最常见的修补程序:

Most common fix I found online:

Range("A1").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
  "Table1", Version:=6).CreatePivotTable TableDestination:="'Summary'!R1C1", _
  TableName:="PivotTable3", DefaultVersion:=6

我在网上找到的第二个最常见的修复方法:

Second most common fix I found online:

Range("A1").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
  "Table1", Version:=6).CreatePivotTable TableDestination:=ActiveWorkbook.Worksheets("Summary").Range("$A$1"), _
  TableName:="PivotTable3", DefaultVersion:=6

我什至试图通过将现有的数据透视表导入到新工作簿中然后在数据透视表获取数据的位置进行更新来作弊，但我仍然收到运行时错误5 .

I even tried to cheat by importing an existing PivotTable into a new workbook and then updating where the pivot gets its data, but I still get a Run-time Error 5.

ActiveSheet.PivotTables("PivotTable3").ChangePivotCache ActiveWorkbook. _
  PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Table1", Version:=6)

推荐答案

我遇到了同样的问题.我可以DefaultVersion:= xlPivotTableVersion12，而不是Version:= xlPivotTableVersion12

I had the same problem. I am OK with DefaultVersion:=xlPivotTableVersion12, not Version:=xlPivotTableVersion12

这篇关于运行时错误5有时在VBA中创建数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！