none
run-time error using vba to create Excel Pivot tables in 2016 Excel RRS feed

  • Question

  • In updating to 2016 Excel I now get "run-time error 2147417848 Automation Error The object invoked has disconnected from its clients"when using PivotTableWizard. The code below in caps is the line that generates the error.

    When creating the application in 2010 Excel I used the base code from this article  "Creating PivotTable Reports and Charts with VBA in Excel 2010" located here:

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

    Any help would be appreciated, thanks.

    ActiveWorkbook.Sheets("ELMS Data").Select
    Range("A1").Select
    Set objTable = Sheet1.PivotTableWizard
    
    Set objField = objTable.PivotFields("TESTENGINEER")
    objField.Orientation = xlRowField
    On Error Resume Next                            'if no blank field resume next
    objField.PivotItems("(blank)").Visible = False  'do not display blanks in TESTENGINEER
    
    Set objField = objTable.PivotFields("REQUESTSTATUS")
    objField.Orientation = xlColumnField
    
    Set objField = objTable.PivotFields("REQUESTSTATUS")
    objField.Orientation = xlDataField
    objField.Function = xlCount
    objField.caption = "Number of Test Requests"
    Set objField = objTable.PivotFields("WTN")
    objField.Orientation = xlPageField
    objField.Position = 1
    objField.caption = (program)
    Thursday, January 18, 2018 3:38 AM

All replies

  • Hello Xldevaus,

    >>In updating to 2016 Excel I now get 

    Could the code work for you in before version of Office(such as 2010), but failed to work once you update your office to 2016? If so, could you test the code using a new created workbook in office 2016? Besides, could you test the macro using a PC which install Excel 2016 directly rather than updating from a lower version?

    >>Set objTable = Sheet1.PivotTableWizard

    Where is the source data?Sheets("ELMS Data")? Are you sure Sheet1 is the source sheet? Please refer to the source worksheet exactly. Such as 

    ActiveWorkbook.Sheets("ELMS Data").Select
    Range("A1").Select
    Set objTable = ActiveWorkbook.Sheets("ELMS Data").PivotTableWizard

    Please try to refer to below link for more information.

    INFO: Error or Unexpected Behavior with Office Automation When You Use Early Binding in Visual Basic

    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.

    Thursday, January 18, 2018 6:33 AM
  • Hi Terry, yes this code worked in my application using 2010 Excel and I did try it in a newly created 2016 workbook as suggested but no luck. I did check the following code in 2010 and 2016 and it worked in 2010 but failed in 2016 Excel see below

    Appreciate your time on this , Thanks

    Sample Data

    I set up 2 Columns with headings and data in 3 rows

     
    a b
    1 5
    2 6
    3 4

    Code to be executed

    Sub Checking()

     

    Dim objTable As PivotTable

     

    ActiveWorkbook.Sheets("Sheet1").Select

    Range("A1").Select

     

    Set objTable = ActiveSheet.PivotTableWizard

     

           

    End Sub


    Runtime Error message (running Excel 2016)

    Run-time error 2147417848 Method 'PivotTableWizard' of object'_Worksheet'failed

    Friday, January 19, 2018 3:29 AM
  • Hello Xldevaus,

    The code could work for me in Excel 2016.

    As workaround, I would suggest you create pivot table in another way. Please take below as reference.

    Sub Macro1()
    
        Sheets("Employees").Range("A1").Select
        Set NewSheet = Sheets.Add
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            Sheets("Employees").UsedRange, Version:=6).CreatePivotTable TableDestination:= _
            NewSheet.Cells(3, 1), TableName:="PivotTable1", DefaultVersion:=6
        NewSheet.Select
    
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("DEPT")
            .Orientation = xlRowField
        End With
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("LOCATION")
            .Orientation = xlColumnField
        End With
        ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("SALARY"), "Sum of SALARY", xlSum
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of SALARY")
            .NumberFormat = "$ #,##0"
        End With
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("GENDER")
            .Orientation = xlPageField
        End With
    End Sub

    Besides, I would suggest you try to repair/reinstall your Office 2016. 

    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, January 19, 2018 9:21 AM