This project also involved, filtering an area (in this case a Pivot) and copying the visible files over to another worksheet.
: 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:
This article is "interesting" but probably doesn't really address Remote Automation:
This is the only resource found that addressed Excel limits, although not specifically for Access Remoter Automation.
'Check if there are not more then 8192 areas(limit of areas that Excel can copy) 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"