Mark Simms
marksimms at verizon.net
Tue Jul 26 10:20:21 CDT 2011
Bill - look again. I am only looping thru EACH COLUMN...and I AM using
Application.SUM.
I just came from an investment bank where some workbooks would take 20 to 40
MINUTES to complete.
Reason: formula-based SUMS, VLOOKUPS, and SUMIFS.
VLOOKUPs themselves were horribly inefficient. Even MATCH replacements did
not improve performance.
When replaced with VBA "static" counterparts, the calc time would decrease
90% in many cases.
> -----Original Message-----
> From: William Benson (VBACreations.Com) [mailto:vbacreations at gmail.com]
> Sent: Monday, July 25, 2011 9:03 PM
> To: 'Mark Simms'
> Subject: FW: [AccessD] Exporting Access to Excel - How to "SUM" a
> column with a variable number of rows?
>
> Mark,
>
> I have programmed in Excel for about 8 years and would not recommend
> this
> approach, but did not want to say so on the ListServ. Perhaps there is
> a
> reason you would loop through each cell to build a sum instead of using
> Excel's marvelous (and instantaneous!) SUM worksheetfunction --
> available to
> the application object?
>
> If you are looping through cells for the purpose of examining them
> prior to
> adding them, I don't say that is a bad idea - but for a standard
> summation,
> you would want to use Application.SUM.
>
> Warm regards,
>
> Bill
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Simms
> Sent: Monday, July 25, 2011 8:03 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?
>
> 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