Mark Simms
marksimms at verizon.net
Tue Jul 26 19:39:40 CDT 2011
Simple. Add a paramarray variant parameter to the procedure and pass it the
column numbers or column letters that should be ignored.
The only other way is to first test the column using Application.Sum.
If the result is non-zero, then it is LIKELY a numeric column.
Caveat: all zeros in a column will fail the test !
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:accessd-
> bounces at databaseadvisors.com] On Behalf Of Brad Marks
> Sent: Tuesday, July 26, 2011 4:40 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?
>
> Mark,
>
> Thanks for the help, I appreciate it.
>
> The sample code that you posted works nicely. Each column is summed.
>
> I do have one question, however.
>
> How can I prevent select columns from being summed in case they contain
> data that should not be summed?
>
> Thanks,
> Brad
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Simms
> Sent: Monday, July 25, 2011 7:03 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Exporting Access to Excel - How to "SUM" acolumn
> 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.
> >
> >
>
>
>
> --
> 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.
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com