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

Open in new window


I created a simple VB6 project and run this code from the "OK" button in the form.
RobMasterSoftware DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
SELECT Top 100 Rtrim(C1.CONTACT) AS [Name], Rtrim(C1.COMPANY) AS Company FROM Contact1 C1

Just as an observation:
TOP now has a preferred syntax of TOP(100) and  results from top is not predictable unless you use an ORDER BY clause.
0
RobMasterSoftware DeveloperAuthor Commented:
I have tried recording the creation of a pivot table as a macro using Excel 2016, verifying that the code worked in VBA by running it, then inserted it into my vb6 project.  Now it uses OLEDB as the provider, but same result -- the error above is returned when it gets to the point of refreshing the pivot table.  Here is the new code:

    Dim gExcel As Application
    Dim gWB As Workbook
    Dim wb As Workbook
    Dim PT As PivotTable

    On Error GoTo 0
    
    Set gExcel = CreateObject("Excel.Application")
    gExcel.Workbooks.Add
    Set gWB = gExcel.Workbooks(1)
    gWB.Activate
 
    ' Excel 2016 with OLEDB provider

    gWB.Connections.Add2 Name:="mmConn3", Description:="MasterMine", ConnectionString:=Array("OLEDB;Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Password=#####;Data Source=MM-1-VM;Use Procedure for Prepare=1;", "Auto Translate=True;Packet Size=4096;Workstation ID=WIN-83A9QRLN3ML;Use Encryption for Data=False;", "Auto Translate=True;Initial Catalog=GoldMine"), CommandText:="Select Top(100) * from Contact1", lCmdType:=xlCmdSql
    With gWB.Connections("mmConn3").OLEDBConnection
        .BackgroundQuery = True
        .CommandText = Array("Select Top(100) * from Contact1")
        .CommandType = xlCmdSql
        .Connection = Array( _
        "OLEDB;Provider=SQLOLEDB.1;Password=######;Persist Security Info=True;User ID=sa;Data Source=MM-1-VM;Use Procedure for Prepare=1;Aut" _
        , _
        "o Translate=True;Packet Size=4096;Workstation ID=WIN-83A9QRLN3ML;Use Encryption for Data=False;Tag with column collation when po" _
        , "ssible=False;Initial Catalog=GoldMine")
        .RefreshOnFileOpen = False
        .SavePassword = True
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With

    gWB.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        gWB.Connections("mmConn3"), Version:=3).CreatePivotTable _
        TableDestination:="Sheet1!R1C1", TableName:="PivotTable2", DefaultVersion:=3
    
    DoEvents
    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"
    
    Err.Clear
    DoEvents
    PT.PivotCache.Refresh ' ERROR OCCURS HERE
    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

          
End Sub

Open in new window

0
RobMasterSoftware DeveloperAuthor Commented:
Possible progress, but I think I need an explanation as I need this to work in vb6.  I tried copying the above code into XLA in a new Excel 2016 workbook.

When I run the code exactly as above in VBA, I get the same error that the compiled VB6 code generates:  Method '~' of object '~' failed

However, if I set gExcel to the current Excel application object, it runs without error and creates the pivot table refreshable.
So for line 8 above, I substituted:  
set gExcel = Application

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

RobMasterSoftware DeveloperAuthor Commented:
Can anyone suggest how to identify which DLL is involved in generating or refreshing this pivot table?  Or explain how it is different between doing so within the current Excel application vs in an Excel object instantiated outside the active one?

Does it help to know that it works correctly with Excel release 16.0.8201.2102 but fails starting with release 16.0.8326.2096?
0
John TsioumprisSoftware & Systems EngineerCommented:
If you run it as an administrator does it run OK ?
0
RobMasterSoftware DeveloperAuthor Commented:
No, it does the same thing.  Thanks for asking.
0
John TsioumprisSoftware & Systems EngineerCommented:
If i understood correctly you are the creator of this addin...if you have such an issue then you have 2 'solutions":
1st carefully examine your source code to identify the critical point
2nd Process Monitor is the way to go...a lot of time consuming but with a bit of luck you find the troubling point...probably something with registry
0
RobMasterSoftware DeveloperAuthor Commented:
The code above is a stand-alone VB6 app that I created to reduce the problem to its simplest form. That's the entirety of the code, plus a form that I use to enter the SQL server parameters and insert them into the connection string at lines 15 and 22.  It errors out at the point indicated (where it says: ' ERROR OCCURS HERE).   Is that the "critical point" you're talking about?

With Process Monitor are you referring to the Sysinternals utility from Microsoft?  I have never been successful figuring out how to use it to track down a thing like this.  Do you have any advice how to narrow down to the DLL or registry setting that might be involved?

In hindsight, it was probably a mistake to mention my app in the original post, as that may give people the impression I'm just debugging code.  This simple demo app clearly demonstrates the problem is a change in Excel, not my other application.  It seems like my best hope is that others will experience the same issue but it may be a small group of users worldwide who build pivot tables programmatically from outside VBA.
0
John TsioumprisSoftware & Systems EngineerCommented:
The SysInternal  process monitor is the way to find out strange problems related to dlls misconfiguration...just lookout for everything "negative"..like NOT FOUND,DENIED...etc...as for further help i think you should provide a full example (source code+ dlls+data)
Of course there is also the option of Gig...
0
RobMasterSoftware DeveloperAuthor Commented:
This remains a technical issue in Excel, but I have found an acceptable solution to my problem.  Here's a summary of what I know:

If you create a pivot table against outside data (i.e. SQL data) programmatically from VB6, or from VBA in an instance of Excel outside the one running the VBA, or presumably other ways such as in VB.net, any attempt to refresh the pivot cache will throw the error "Method '~' of object '~' Failed" and the pivot table will be invalidated for the remainder of the session.  The workbook can be saved, subsequently opened and successfully refreshed manually but not programmatically.

The condition holds for any release of Excel 2016 or 365 released after June 2017 (16.0.8201.2102) through at least November 2017.

My solution was simply to avoid attempting to refresh the cache before saving and turning the workbook over to users.  

Some background: To allow for backward compatibility with Excel 2000 and 2003, my code had used the oldest syntax for creating pivottables (worksheet.PivotTableWizard) to create the pivot table, then used the UpgradeOnRefresh setting to cause whatever version of Excel the user had to upgrade to their most up-to-date version.  Even updating the syntax to the newer Excel 2007 syntax (pivotcaches.create) continued to throw the error if a refresh was attempted.  However, by deprecating compatibility with Excel 2003 and earlier, and creating the pivot cache and pivot table the newer way I removed the need for a refresh within my code.  ...At least until Microsoft again changes the syntax for creating pivot tables.

I didn't get any help to reach this solution here, but want to express appreciation to those who gave it their attention.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RobMasterSoftware DeveloperAuthor Commented:
There was no other relevant feedback.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.