Creating Excel Pivot from VBA running in MSAccess, SourceData size limit undocumented

[Rx_]

Problem: Running the same .ActiveWorkbook.PivotCaches.Create a.k.a Create Pivot Table - in Excel vs MSAccess with remote automation:
The Excel SourceData can be columns $A:$CC and run.
From MSAccess using remote automation - it throws an OUT OF MEMORY error at the system level.
Question: is there a known limit to the size of the SourceData?

From MSAccess Objxl is set to an Excel.Application
Originally, used the Macro Recorder to build the code for creating a Pivot table based from data in a worksheet in the same workbook.
The code ran perfectly from within Excel (as a macro).
However, when converting the code to run in MSAccess as remote automation, the SourceData: was scaled down to work.

The concern is that later data may exceed 1,000. The average has been around 150 rows. If anyone has other suggestions, please contribute.

' ****************************************************************************
' Pivot TAbles   AP  Note Need to replace with relative reference
'******************************************************************************
Dim PivotSource As String
PivotSource = "AP Pmts 1025!$A100$:CC100"
CountWorksheets = Objxl.Worksheets.Count
Objxl.Sheets(CountWorksheets).Select
 With Objxl
    .Sheets.Add.Name = "AP Pmts Pivot" ' by default becomes current worksheet
    .ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=.Worksheets(1).Range("$A1:$CC1000"), Version:=6).CreatePivotTable _
        TableDestination:=.Worksheets("AP Pmts Pivot").Range("A3"), TableName:="PivotTable1", DefaultVersion _
        :=6
    .Cells(1, 1).Select
    .ActiveSheet.PivotTables("PivotTable1").AddDataField .ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Transaction Value"), "Count of Transaction Value", _
        xlCount
    With .ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "Count of Transaction Value")
        .Caption = "Sum of Transaction Value"
        .Function = xlSum
    End With
    With .ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "Funding Business Unit - 6 Digit")
        .Orientation = xlRowField
        .Position = 1
    End With
    With .ActiveSheet.PivotTables("PivotTable1").PivotFields("Vlookup Project")
        .Orientation = xlRowField
        .Position = 2
    End With
    With .ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "Correct Expenditure Type")
        .Orientation = xlRowField
        .Position = 3
    End With
    .ActiveSheet.PivotTables("PivotTable1").RowAxisLayout xlTabularRow
End With

MSOffice 2016
B.T.W.
The code above works. Searching the web didn't return any good examples of creating a Pivot Table using VBA in Excel running the code from MS ACCESS.

 

[Rx_]

This project also involved, filtering an area (in this case a Pivot) and copying the visible files over to another worksheet.
Reminder: this is all remote automation from MSAccess to a newly created Excel workbook using object code. This problem did not occur when running the code from within Excel, only from running Remote Automation from MSAccess to Excel.

Found a good site that had code for Excel 97 vs Excel 2000+.
The Areas had a limitation in Automation.

In Excel to Excel, the Columns A:CC were copied with no problem.
In MSAccess remote automation, an Out of Memory error appeared.
The number above was changed to the area $A1:$CC10000 with no problems. In my case, the average turned out to be 1,000 rows. A 10 to one ratio is probably enough safety in my situation.

Microsoft seems clueless to this error:
https://support.microsoft.com/en-us/kb/2779852
This article is "interesting" but probably doesn't really address Remote Automation:
http://www.sevenforums.com/microsoft-office/217074-excel-2010-out-memory-not-enough-resources.html



This is the only resource found that addressed Excel limits, although not specifically for Access Remoter Automation.

[COLOR=black]'Check if there are not more then 8192 areas(limit of areas that Excel can copy)[/COLOR]     CCount = 0     On Error Resume Next     CCount = My_Range.Columns(1).SpecialCells(xlCellTypeVisible).Areas(1).Cells.Count     On Error GoTo 0     If CCount = 0 Then         MsgBox "There are more than 8192 areas:" _              & vbNewLine & "It is not possible to copy the visible data." _              & vbNewLine & "Tip: Sort your data before you use this macro.", _                vbOKOnly, "Copy to worksheet"