View Single Post
Old 10-28-2016, 06:19 AM   #2
Nothing In Moderation
Rx_'s Avatar
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,794
Thanks: 635
Thanked 336 Times in 307 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Re: Creating Excel Pivot from VBA running in MSAccess, SourceData size limit undocume

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:
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"
Were you lucky enough to get an answer? Please mark your question as [SOLVED] The original poster can go to Thread Tools to mark it as Solved.

Quotation Thomas Jefferson: "Peace is that brief glorious moment in history when everybody stands around reloading."

There are 2 Kinds of Countries on this Planet
1. Those that use the Metric System
2. Those that had a man walk on the moon

Denver, Colorado - The "Mile High City" - non-metric!
Rx_ is offline   Reply With Quote