none
Create Pivot table using vb script error RRS feed

  • Question

  • Hi All,

    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.

    Set objTable = Sheet1.PivotTableWizard

    I referred to a code from Microsoft website to try out if something else in my code is causing issue , but it is the same. Below is the URL

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

    Sample code copied below.

    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. 

    Thanks in advance.



    Wednesday, November 15, 2017 11:44 PM

All replies

  • Hi SuryaManyam,

    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.

    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. 

    Here is the simply example code.

    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
    End Sub
    

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 17, 2017 5:34 AM