View Single Post
Old 10-28-2016, 06:19 AM   #2
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,781
Thanks: 630
Thanked 333 Times in 305 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:
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...resources.html



This is the only resource found that addressed Excel limits, although not specifically for Access Remoter Automation.
Code:
'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