Solved

How to create an Excel Groups and Subtotals

Posted on 2004-10-01
2
563 Views
Last Modified: 2008-01-09
Hi all,

From within:
With wkbApp
End with

I am trying to create an Excel report using the Tools, Subtotal options to create subtotals in three columns, grouping on one column using the following code. The code was recorded in the Excel macros and copied to VB.net -

.Selection.Subtotal GroupBy:=6, Function:=xlSum, TotalList:=Array(7, 8, 9, 10), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
By itself, the code fails because the array is not defined.  

I then tried :
Dim myArray As Array = Array.CreateInstance(GetType(Excel.XlRowCol), 1)
.Selection.Subtotal(GroupBy:=6, Function:=10, TotalList:=myArray(7), _
            Replace:=True, PageBreaks:=False, SummaryBelowData:=True)
The error for this is System.IndexOutOfRangeException:  Index was outside the bounds of the array.

AND

Dim myArray As Array = Array.CreateInstance(GetType(Excel.XlRowCol), 4)
.Selection.Subtotal(GroupBy:=6, Function:=10, TotalList:=myArray(7, 8, 9, 10), _
            Replace:=True, PageBreaks:=False, SummaryBelowData:=True)
Each time I receive
System.RankException: Attempted to operate on an array with the incorrect number of dimensions.

Can anyone help with the correct code to perform this task?

Thanks

RichW
0
Comment
Question by:RichW
2 Comments
 
LVL 96

Accepted Solution

by:
Bob Learned earned 125 total points
ID: 12202892
How many dimensions does MyArray have?  

What type of array are you trying to pass?  I think that you meant to create an array like this:

Dim myArray As Integer() = {7, 8, 9, 10)

.Selection.Subtotal(GroupBy:=6, Function:=10, TotalList:=myArray, _
            Replace:=True, PageBreaks:=False, SummaryBelowData:=True)

Bob
0
 
LVL 4

Author Comment

by:RichW
ID: 12203634
Thank you, Bob.  That worked perfectly!

RichW
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now