Avatar of RobMaster
Flag for United States of America

asked on 

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

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")
    Set gWB = gExcel.Workbooks(1)
    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
    ' 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())))
' change gExcel.Activesheet to gWB.activesheet
    Form1.lText = "Done Pivot table"
    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"
    MsgBox "Refresh error if any: " & Err.Description
    gExcel.Visible = True
    gExcel.Interactive = True
    If Err.Number <> 0 Then MsgBox "Error " & Err.Number & ": " & Err.Description
     For Each wb In gExcel.Workbooks
        If wb.Name <> "Book1" Then wb.Close
     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.
Microsoft Office* Pivot TablesMicrosoft Excel

Avatar of undefined
Last Comment

8/22/2022 - Mon