Get a total when number or rows is not know in advance

B

BillyRogers

I have this code which returns a recordset from Access and pastes the results
in Excel. I need it to totall columns B and C.
The only problem is that the query will return a different number of rows
each month, so I don't know how to code the sum formula.


SQLcmd = "SELECT * FROM [S 06 Safelite Fee Summary]"

rs.Open Source:=SQLcmd, _
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=G:\Brad and
Mary's DB.mdb" + _
"; User Id=admin; Password="


Column = 1
Row = 1

For Each fld In rs.Fields

Cells(Row, Column).Value = fld.Name
Cells(Row, Column).Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid

End With
Selection.Font.Bold = True


Column = Column + 1

Next fld

Cells(2, 1).CopyFromRecordset rs

Cells.Select
Cells.EntireColumn.AutoFit

Range("A65000").End(xlUp).Offset(1, 0).Activate
ActiveCell.Value = "Fees Subtotal"



--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top