[AccessD] Exporting Access to Excel - How to "SUM" a column with a variable number of rows?

What you need to decide is: dynamic vs. static.
If the data is not going to change, then try this:

Sub AddTotals

Dim rngCol as Range

With Activesheet.UsedRange

For Each rngCol in .Columns
  rngCol.Cells(.rows.Count+1) = Application.Sum(rngCol)
Next

End With

End Sub

Of course this assumes the starting row is #1.

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:accessd-
> bounces at databaseadvisors.com] On Behalf Of Brad Marks
> Sent: Monday, July 25, 2011 4:52 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Exporting Access to Excel - How to "SUM" a
> column with a variable number of rows?
>
> William and Lambert,
>
> Thanks for the help, I appreciate it.
>
> One of the things that I often struggle with is that there is often
> more than one way to accomplish a task.  Before I start to build
> something big, I like to understand the options and also understand the
> "Best" way to get something done.
>
> Thanks Again,
> Brad
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com on behalf of Heenan, Lambert
> Sent: Mon 7/25/2011 1:31 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Exporting Access to Excel - How to "SUM" a
> column with a	variable number of rows?
>
> The way I do this is after exporting the results of a query to Excel I
> then run a Dcount() against the same query to discover how many rows
> were in the output data....
>
> nRows = DCount("*", "Some_Query_Name")
>
> Then I open the Excel file with Access (i.e. I set an
> Excell.Application and Excel.Worksheet object) so that I can then add
> the formulas that I need to the row under the last row of exported
> data.
>
> Essentially you build the formula in a text variable like this...
>
> strFormula = "=Sum(" & Excel_Column(nCol) & nRowOffest - nRowsToSum &
> ":" & Excel_Column(nCol) & nRowOffest - 1 & ")"
>
> Where nRowOffest is the row number to start summing (or averaging or
> counting, whatever) and nRowsToSum is the number of row in the output.
> The function Excel_Column is used to get the right alpha prefix in the
> cells address...
>
> Function Excel_Column(nCol As Long) As String ' given a valid column
> number return the Alpha name for it ' else return an empty string
>     If nCol > 256 Then ' Pre Excel 2007
>         Excel_Column = ""
>     Else
>         If nCol <= 26 Then
>             Excel_Column = Chr(Asc("A") + nCol - 1)
>         Else
>             Dim sSecond As String
>             sSecond = Excel_Column(nCol Mod 26)
>             Excel_Column = Excel_Column(nCol \ 26) & sSecond
>         End If
>     End If
> End Function
>
> Then, after building the text string with the formula just plug it into
> the relevant cell in the workbook.
>
> After setting the Excel.Application object you then need a reference to
> the worksheet that needs to be update...
>
> Set Ws = xlApp.Sheets(nSheet)
> With Ws
> 	strCellAddress = Excel_Column(nCol) & nRowOffest
> 	strFormula = "=Sum(" & Excel_Column(nCol) & nRowOffest -
> nRowsToSum & ":" & Excel_Column(nCol) & nRowOffest - 1 & ")"
> 	.Cells(nRowOffest, nCol) = strFormula
> End With
>
>
> HTH
>
> Lambert
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:accessd-
> bounces at databaseadvisors.com] On Behalf Of Brad Marks
> Sent: Monday, July 25, 2011 1:54 PM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Exporting Access to Excel - How to "SUM" a column
> with a variable number of rows?
>
> I am starting to do some experiments involving the creation of Excel
> files from Access.
>
> Let's say that I have an Access Recordset that can contain anywhere
> from 100 to 1,000 records.
>
> I have a little Access application that currently pushes this data into
> Excel.  This all works nicely.
>
> What is the best way to "Sum" a column in Excel after the last record,
> when the number of records can vary?
>
> Thanks,
> Brad
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> This message has been scanned for viruses and dangerous content by
> MailScanner, and is believed to be clean.
>
>