使用vb脚本错误创建数据透视表 [英] Create Pivot table using vb script error

问题描述

大家好，

我有一个excel宏 它可以从工作表创建数据透视表读取数据，它运行正常，直到我安装了最新的Excel 2016版本我的工作电脑。升级后，当我运行宏时崩溃excel迫使我在
任务管理器进程中杀死它。在调试时观察到它在下面的行执行时发生。

I have a excel macro which creates pivot table reading data from a sheet , it worked fine until I got the latest Excel 2016 version installed in my work PC. After the upgrade when I run the macro it is crashing excel forcing me to kill it in the task manager process. On debugging observed it is happening when executing below line.

设置objTable = Sheet1.PivotTableWizard

我 推荐  来自
的代码 Microsoft  网站在我的代码 ：0.75em">导致问题，但它是一样的。
以下是网址

网址：https：//msdn.microsoft.com/en-us/library/office/hh243933（v = office.14 ）.aspx＃CreatingPivotTable_CreatingPivotTableReport

URL : https://msdn.microsoft.com/en-us/library/office/hh243933(v=office.14).aspx#CreatingPivotTable_CreatingPivotTableReport

下面复制的示例代码。

Sub CreatePivot()
  
  Dim objTable As PivotTable, objField As PivotField
   
  ActiveWorkbook.Sheets("RAW").Select
  Range("A1").Select
  
  Set objTable = Sheet1.PivotTableWizard
                        
  
  Set objField = objTable.PivotFields("DEPT")
  objField.Orientation = xlRowField
  
  Set objField = objTable.PivotFields("LOCATION")
  objField.Orientation = xlColumnField
  
  Set objField = objTable.PivotFields("SALARY")
  objField.Orientation = xlDataField
  
  objField.Function = xlSum
  objField.NumberFormat = "$ #,##0"
  
  ActiveSheet.PrintPreview

End Sub

我尝试过浏览，但未找到任何建议。 

I tried browsing but no suggestions found. 

提前致谢。

推荐答案

嗨SuryaManyam，

Hi SuryaManyam,

我未能重现您的问题。我下载文件并尝试运行代码。我在上一个版本办公室（建筑物编号8625.2127）和内部版本（建筑物编号  8730.2034）中运行了代码，两者都可以很好地运行代码。

I failed to reproduce your issue. I download the file and tried to run the code. I ran the code in last version office (building number 8625.2127) and an insider version(building number 8730.2034), both of them could run the code well.

你的excel的建筑物编号是多少？你能否将Excel恢复到以前的版本使代码有效？

What's building number of your excel? Could you revert the Excel to previous version make the code work?

你能手动创建数据透视表吗？如果数据透视表向导不起作用，您可以尝试添加数据透视表缓存，然后通过缓存创建数据透视表。 

Could you create the pivot table manually? If the PivotTableWizard does not work for you, you could try to add a pivot table cache and then create a pivot table via the cache. 

这是简单的示例代码。

Sub Macro1()
    Dim objSheet As Worksheet
    Set objSheet = Sheets.Add
    Dim objCache As PivotCache
    Set objCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, Sheet1.Range("A1:H19"), 6)
    Dim objTable As PivotTable
    Set objTable = objCache.CreatePivotTable(objSheet.Range("A1"))
    Dim objField As PivotField
    
    Set objField = objTable.PivotFields("DEPT")
    objField.Orientation = xlRowField
  
    Set objField = objTable.PivotFields("LOCATION")
    objField.Orientation = xlColumnField
    
    Set objField = objTable.PivotFields("SALARY")
    objField.Orientation = xlDataField
  
    objField.Function = xlSum
    objField.NumberFormat = "

＃，## 0"

ActiveSheet.PrintPreview
结束子

#,##0" ActiveSheet.PrintPreview End Sub

最好的问候，

特里

这篇关于使用vb脚本错误创建数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！