Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-26-2016, 09:30 AM   #1
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,770
Thanks: 624
Thanked 332 Times in 304 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Lightbulb Creating Excel Pivot from VBA running in MSAccess, SourceData size limit undocumented

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.

Code:
' ****************************************************************************
' 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.

__________________
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
Old 10-28-2016, 06:19 AM   #2
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,770
Thanks: 624
Thanked 332 Times in 304 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
Reply

Tags
excel , msaccess 2013 , remote automation , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
255 limit size in memo field when importing from excel adromeda Tables 4 09-30-2015 02:45 PM
Creating excel pivot table - Access 2010 Kobe2932 Macros 0 11-26-2012 02:06 AM
Creating an Excel Pivot table using Access VBA gorgon777 Modules & VBA 0 05-17-2012 06:50 AM
Pivot Table - Limit Sum ChrisAnthony Reports 2 11-29-2010 06:16 PM
Running an automated Query created in MSACCESS utopianbl General 1 07-20-2006 12:00 PM




All times are GMT -8. The time now is 07:47 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World