**Heenan, Lambert**
Lambert.Heenan at chartisinsurance.com

*Mon Jul 25 13:31:06 CDT 2011*

- Previous message: [AccessD] Exporting Access to Excel - How to "SUM" a column with a variable number of rows?
- Next message: [AccessD] Exporting Access to Excel - How to "SUM" a column with a variable number of rows?
**Messages sorted by:**[ date ] [ thread ] [ subject ] [ author ]

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

- Previous message: [AccessD] Exporting Access to Excel - How to "SUM" a column with a variable number of rows?
- Next message: [AccessD] Exporting Access to Excel - How to "SUM" a column with a variable number of rows?
**Messages sorted by:**[ date ] [ thread ] [ subject ] [ author ]