troubleshooting Question

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

Avatar of RobMaster
RobMasterFlag for United States of America asked on
Microsoft Office* Pivot TablesMicrosoft Excel
11 Comments1 Solution489 ViewsLast Modified:
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

I created a simple VB6 project and run this code from the "OK" button in the form.
ASKER CERTIFIED SOLUTION
RobMaster
Software Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros