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