[VBA] How to create a PivotTable using an OLEDBConnection whose Data Source is ThisWorkbook

I'm trying to use VBA to create a PivotTable, using an existing WorkbookConnection(OLEDBConnection). Here is what I found:

• If the "Data Source" of the Connection String is outside ThisWorkbook, then it would be successful to create a PivotTable using the created PivotCache.
• However, if the data source is Thisworkbook, an error occurs when create the PivotTable using the PivotCache: "Run-time error '1004': 'The connection for viewing your linked Microsoft Excel worksheet was lost. ' "

I'm not sure why this leads to these two different situations when the data sources are different.

Based on my daily experience, I can successfully create ListObjects ("Excel Tables") with VBA, using an Oledb Connection; I can also create pivot tables manually in the Excel interface using an existing Oledb Connection. (In both cases, the data source for the Oledb Connection can be ThisWorkbook.)

Here's how I did it:

 Step1.   Add an OLEDB Connection (Connection Only)

Sub Step1_Add_OledbConnection() 'recorded code
'
' Step1_Add_OledbConnection Macro
'
    Application.CutCopyMode = False
    With ActiveWorkbook.Connections("VBA_CreatePivotTableUsingOLEDBConnection"). _
        OLEDBConnection
        .BackgroundQuery = True
        .CommandText = Array("SELECT * FROM [DATA$]")
        .CommandType = xlCmdSql
        .Connection = Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=D:\Q1\VBA_CreatePivotTableUsingOLEDBConnection.xlsm;Mode" _
        , _
        "=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Pass" _
        , _
        "word="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Tra" _
        , _
        "nsactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB" _
        , _
        ":Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex" _
        , _
        " Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validatio" _
        , "n=False")
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = "D:\Q1\VBA_CreatePivotTableUsingOLEDBConnection.xlsm"
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("VBA_CreatePivotTableUsingOLEDBConnection")
        .Name = "Oledb_Connects_to_ThisWorkbook_Data"
        .Description = ""
    End With
    Workbooks("VBA_CreatePivotTableUsingOLEDBConnection.xlsm").Connections. _
        AddFromFile "D:\Q1\VBA_CreatePivotTableUsingOLEDBConnection.xlsm"
End Sub

Step2.   Create "PivotTable1" Using the WorkbookConnection Created in Step1.

Sub step2_AddPivotTableUsingExistingOLEDBconn() 'recorded code
'
' step2_AddPivotTableUsingExistingOLEDBconn Macro
'
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        ActiveWorkbook.Connections("Oledb_Connects_to_ThisWorkbook_Data"), Version:=8 _
        ).CreatePivotTable TableDestination:="step2_CreatePvtTableUsingOledb!R1C1", _
        TableName:="PivotTable1", DefaultVersion:=8
    Cells(1, 1).Select
    With ActiveSheet.PivotTables("PivotTable1")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = True
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
End Sub

Step3.  Re-do the work in step2 with VBA......where I ran into difficulties.

Sub Repeat_step2_With_VBA() 'to create a "PivotTable2"

Sheet4.Cells.Clear
Dim pvtCache As PivotCache
Set pvtCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        ThisWorkbook.Connections("Oledb_Connects_to_ThisWorkbook_Data"), Version:=8 _
        )
        
' ----  Error occurs below -----
'Run-time error '1004':
'The connection for viewing your linked Microsoft Excel worksheet was lost.
pvtCache.CreatePivotTable _
            TableDestination:="Step3_CreatePivotTable2_WithVBA!R1C1", _
            TableName:="PivotTable2", _
            DefaultVersion:=8
'If you run this code right after "PivotTable1" is created in Step2,
'this error won't happen.--- this is easy to understand: the PivotTable2 actually using
'the same pivotcache of PivotTable1, since after Excel 2007,the pivotcache is sharable.

'But the wired thing is,
'if you save and close this workbook right after step2, and re-open it to run
'this Sub, then the Error occurs.

End Sub

 Step4.   If the Data Soure is outside ThisWorkbook, then a "PivotTable3" is created successfully.

Sub AnotherTry() 'to create a "PivotTable3"
                'with Oledb Connection connecting to External workbook.

Sheet5.Cells.Clear

Dim pvtCache As PivotCache
Set pvtCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        ThisWorkbook.Connections("Oledb_Connects_To_Real_EXTERNAL_Data"), Version:=8 _
        )
        
pvtCache.CreatePivotTable _
            TableDestination:="IfTheDATA_Is_RealEXTERNAL!R3C1", _
            TableName:="PivotTable3", _
            DefaultVersion:=8

End Sub

The Excel file associated with the above presentation is available at this link.

At this point I'm wondering:  since the data source is tables within ThisWorkbook, why not use "xlDatabase" for the SourceType parameter when I create the PivotCache using the PivotCaches.Create method, but if I did that, I wouldn't be able to do a multi-table join using SQL.