Rx_
Nothing In Moderation
- Local time
- Today, 00:13
- Joined
- Oct 22, 2009
- Messages
- 2,803
Has anyone run up against a limit of elements used in the Array used in the VBA automation of DATA Sorting and Grouping?
The latest project takes SQL Server views, pairs them with an Excel Template, then processes three levels of Sort and Grouping plus all kinds of fun Excel enhancements.
A.K.A. Menu DATA - Grouping - Sorting and Grouping in the Excel Menu
The Excel 2016 uses an Subtotal GroupBy function that uses an Array to determine what columns receive a Subtotal at each grouping.
Some of the Excel worksheets for example have 36 Columns.
So, each Array is dynamically created depending on the number of columns.
PROBLEM: The code worked great - until the columns exceeded around 18. Then, there is an entrapped system error.
ObjXL is set as an Excel Object in MSAccess to remotely call automation in Excel.
ObjXL.Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(GroupArray), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
If this Array has a limit for use in the function call above, this might require a custom option to finish the Subtotals for Excel worksheets that have dozens of columns of data.
The latest project takes SQL Server views, pairs them with an Excel Template, then processes three levels of Sort and Grouping plus all kinds of fun Excel enhancements.
A.K.A. Menu DATA - Grouping - Sorting and Grouping in the Excel Menu
The Excel 2016 uses an Subtotal GroupBy function that uses an Array to determine what columns receive a Subtotal at each grouping.
Some of the Excel worksheets for example have 36 Columns.
So, each Array is dynamically created depending on the number of columns.
PROBLEM: The code worked great - until the columns exceeded around 18. Then, there is an entrapped system error.
ObjXL is set as an Excel Object in MSAccess to remotely call automation in Excel.
ObjXL.Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(GroupArray), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Code:
Dim aFullYear as Variant ' This will create an Array to pass in to a function as GROUPARRAY used in function call
aFullYear = Split("5, 6, 7, 8, 9, 10", ",")
' Example if the Array is too large it FAILS!
aFullYearFstTotal = Split("5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22", ",")
ObjXL.Range(StartLocation).Select
StartRowNumber = ObjXL.ActiveCell.Row
StartRowNumber = StartRowNumber - 1 ' start location is data row
LastRowNumber = ObjXL.Cells(ObjXL.Rows.Count, 1).End(xlUp).Row
'LastRow = ObjXL.ActiveSheet.UsedRange.Rows.Count
nextRowValue = "A" & StartRowNumber & ":" & LastComumn & LastRowNumber
ObjXL.Range(nextRowValue).Select ' ' example ObjXL.Range("A8:M53").Select
DoEvents
'ObjXL.Range("A8:M53").Select ' Must Pass array of columns for Subtotal
ObjXL.Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(GroupArray), Replace:=True, PageBreaks:=False, SummaryBelowData:=True