Go Back   Access World Forums > Apps and Windows > Excel

 
 
 
Thread Tools Rate Thread Display Modes
Prev Previous Post   Next Post Next
Old 10-26-2016, 09:30 AM   #1
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
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
 

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 04:07 PM.


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

Featured Forum post


Sponsored Links


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