Add Pivot Cache To Excel Workbook From Access Using VBA With Late Binding

[goss]

Hi all,

Using Office 2013.

I'm trying to add a Pivot Cache to an Excel Workbook from Access using VBA with Late Binding.
.
I'm receiving this error:

Run-time error '91':
Object variable or With block variable not set

I tested the lo.parent.name and range.address in a sub and they both returned correct results.

Any thoughts?
thx
w


The PivotCache Function

Option Explicit

Public Function GetPivotCache(wb As Object, _
                              lo As Object)
 
    'Declare Objects
        Dim pc As Object
    
    'Declare variables
        Dim strPivotCacheSource As String
 
'    'Error handler
'        On Error GoTo ErrHandler
 
    'Pivot cache source
        strPivotCacheSource = lo.Parent.Name & "!" & _
                              lo.Range.Address(ReferenceStyle:=gclxlR1C1)
 
    'Create pivot cache
        Set pc = wb.PivotCaches.Create( _
                        SourceType:=gclxlDatabase, _
                        SourceData:=strPivotCacheSource)
 
    'Pass object to function
        Set GetPivotCache = pc
 
'ErrHandler:
'    If Err.Number > 0 Then _
'        MsgBox Err.Description, vbMsgBoxHelpButton, "Get pivot cache", Err.HelpFile, Err.HelpContext
 
    'Tidy up
        Set pc = Nothing
 
End Function

These Globals since I am using Late Binding:

'XlListObjectSourceType Enumeration (Excel)
'Info: https://msdn.microsoft.com/en-us/library/office/ff820815.aspx
'-------------------------------------------------------------------
Public Const gclxlSrcExternal As Long = 0   'External data source (Microsoft SharePoint Foundation site).
Public Const gclxlSrcModel As Long = 4      'PowerPivot Model
Public Const gclxlSrcQuery As Long = 3      'Query
Public Const gclxlSrcRange As Long = 1      'Range
Public Const gclxlSrcXml As Long = 2        'XML


'XlReferenceStyle Enumeration (Excel)
'Info: https://msdn.microsoft.com/en-us/library/office/ff821207.aspx
'---------------------------------------------------------------------
Public Const gclxlA1 As Long = 1        'Default. Use xlA1 to return an A1-style reference.
Public Const gclxlR1C1 As Long = -4150  'Use xlR1C1 to return an R1C1-style reference.

'XlPivotTableSourceType Enumeration (Excel)
'Info: https://msdn.microsoft.com/en-us/library/office/ff836220.aspx
'-----------------------------------------------------------------------
Public Const gclxlConsolidation As Long = 3     'Multiple consolidation ranges.
Public Const gclxlDatabase As Long = 1          'Microsoft Excel list or database.
Public Const gclxlExternal As Long = 2          'Data from another application.
Public Const gclxlPivotTable As Long = -4148    'Same source as another PivotTable report.
Public Const gclxlScenario As Long = 4          'Data is based on scenarios created using the Scenario Manager.

 

[goss]

Solved.

I forgot to use "Set" in the Call to the Function:

'Add a Pivot Cache
        xlPivotCache = GetPivotCache(wb:=xlWorkbook, _
                                         lo:=xlListObject)

Should be

'Add a Pivot Cache
        Set xlPivotCache = GetPivotCache(wb:=xlWorkbook, _
                                         lo:=xlListObject)