[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