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

Brad,
Excel is VERY powerful, and VERY fast. So whenever possible, use what Excel
already has, when automating. To sum all the values in a column in Excel,
assuming you have hooks to the xl application object and a worksheet:

Function TestFunction ()
Dim xl As Excel.Application

Set xl = GetXL
With GetXL
	MsgBox .Sum(.ActiveWorkbook.ActiveSheet.Columns(1))
End With

End TestFunction

Function GetXL() As Excel.Application
On Error Resume Next
  Set GetXL = GetObject(, "Excel.Application")
  If GetXL Is Nothing Then
    Set GetXL = CreateObject("Excel.Application")
    GetXL.Visible = True
  End If
exit_me:
End Function	



-----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