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")
    
    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

Select allOpen in new window

I created a simple VB6 project and run this code from the "OK" button in the form.