We help IT Professionals succeed at work.
Get Started

Refresh of just-created pivot table fails with "Method '~' of object '~' failed"

488 Views
Last Modified: 2017-12-02
My product, an add-in for MS Excel that has been on the market for 18 years and still supported for a few hundred users, just stopped working with Excel 2016/365.  It creates pivot tables from a database using a SQL query.

The simplified VB6 code below creates a pivot table in a new worksheet, places a couple of fields into the pivot table, then tries to refresh that pivot table.  The refresh works with every version of Excel from 2003 up to 2016, but it fails  when run on Excel 2016 release 16.0.8326.2096 (Aug. 2017) or higher.  The error returned is number -2147417851  "Method '~' or object '~' failed".  I can reliably roll back Excel to release 16.0.8201.2102 (June 2017) and it will work again. But I can't ask every client using the software to roll back and freeze Excel updates forever.

Can someone help me find a workaround, or tell me how to pinpoint the MS DLL that is likely causing the problem?

Option Explicit

Private Sub cmdOK_Click()
    Dim gExcel As Application
    Dim gWB As Workbook
    Dim wb As Workbook
    Dim PT As PivotTable
    Dim gODBCConnection As String
    Dim selArraycnt As Long
    Dim selArray(250) As String
    Dim selStatement As String
    
    On Error GoTo ErrHandler
    
    Form1.lText = "Open gExcel"
    Set gExcel = CreateObject("Excel.Application")
    
    gExcel.Workbooks.Add
    Set gWB = gExcel.Workbooks(1)
    gWB.Activate
    Form1.lText = "About to set up PTW variables"
    gODBCConnection = "ODBC;DRIVER=SQL Server;SERVER=MM-1-VM;UID=sa;PWD=###########;APP=2007 Microsoft Office system;WSID=56888;DATABASE=GoldMine;"
    selStatement = "SELECT Top 100 Rtrim(C1.CONTACT) AS [Name], Rtrim(C1.COMPANY) AS Company FROM Contact1 C1"

    Erase selArray
    selArraycnt = 1
    selArray(0) = Left(selStatement, 200)
    While Len(selArray(selArraycnt - 1)) = 200
        selArray(selArraycnt) = Mid(selStatement, selArraycnt * 200 + 1, 200)
        selArraycnt = selArraycnt + 1
    Wend
    ' Create the pivot table
    gExcel.ActiveWorkbook.ActiveSheet.PivotTableWizard _
        SourceType:=2, _
        SourceData:=selArray, _
        TableDestination:="R1C1:R10C1", _
        TableName:="PT2", _
        BackgroundQuery:=False, _
        Connection:=gODBCConnection & ";WSID=" & Trim(CStr(Int(Timer())))
        
                      
    DoEvents
    
' change gExcel.Activesheet to gWB.activesheet
    Form1.lText = "Done Pivot table"
        
    Err.Clear
    Set PT = gWB.ActiveSheet.PivotTables(1)
    PT.PivotFields(1).Orientation = xlRowField
    PT.PivotFields(2).Orientation = xlDataField
    
    PT.PivotFields(1).Name = "Person"
    PT.PivotFields(2).Name = "Firm"
    
    Form1.lText = "Fields Oriented"
    Err.Clear
    DoEvents
    PT.PivotCache.Refresh
    MsgBox "Refresh error if any: " & Err.Description
    Err.Clear
    
    gExcel.Visible = True
    gExcel.Interactive = True
    
ErrHandler:
    If Err.Number <> 0 Then MsgBox "Error " & Err.Number & ": " & Err.Description
    
CloseOut:
     For Each wb In gExcel.Workbooks
        If wb.Name <> "Book1" Then wb.Close
     Next
     Set PT = Nothing
     Set wb = Nothing
     Set gWB = Nothing
     ' Set gExcel = Nothing
     Unload Me
          
End Sub

Open in new window


I created a simple VB6 project and run this code from the "OK" button in the form.
Comment
Watch Question
Software Developer
Commented:
This problem has been solved!
Unlock 1 Answer and 11 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE