Can somebody please help me with my code?

[bibig1978]

I am creating a macro on analyst activity in my group. My output has four columns on excel: Takenby, Completed Emails, Open Email Tickets and Total. Below is how the output looks:

Takenby
Kit
Patricia
Tabitha
John
Katie

Completed Emails
7
1
0
7
1

Open Email Tickets
2
4
3
4
1

I need to somehow add up the Completed Emails to Open Email Tickets to get the following ouput for the Totals column:
Total
9
5
3
11
2
Can somebody please help me? I'm fairly new to VB coding and welcome any help I can get. Thanks.
This is the code i have so far:

Sub NewReport()
'
' NewReport Macro
' Macro recorded 8/20/2004 by bevithag
'

Dim conn As ADODB.Connection
Dim ConnectionString As String
Set conn = New ADODB.Connection

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim rs1 As ADODB.Recordset
Set rs1 = New ADODB.Recordset
Dim SqlString As String
Dim xl As Excel.Application
Dim xlw As Excel.Workbook
Dim xls As Excel.Worksheets
Dim intRows As Integer



conn.CursorLocation = adUseClient
ConnectionString = "DRIVER=ODBC;Data Source=csathenacms;NA=172.27.101.179, 5000;Database=CMS;UID=Bevithag;PWD=bevithag"
conn.Open ConnectionString 'Open connection

'test to see if connection works

If conn.State = adStateOpen Then
MsgBox "You are now connected to the database"
ElseIf conn.State = adStateClose Then
MsgBox "You are not connected to the database"
End If


'Open recordset query
rs.Open "Select takenby AS Takenby," & Chr(13) & "" & Chr(10) & _
"(Select count(*)" & Chr(13) & "" & Chr(10) & _
"FROM CMS.dbo.disp" & Chr(13) & "" & Chr(10) & _
"WHERE takenby=a.takenby and status = 'Completed' and source='Email' and opendte=Current_Date())," & Chr(13) & "" & Chr(10) & _
"(Select count(*)" & Chr(13) & "" & Chr(10) & _
"from CMS.dbo.disp " & Chr(13) & "" & Chr(10) & _
"where takenby = a.takenby and status != 'Completed' and source = 'Email' and opendte = Current_Date()) " & Chr(13) & "" & Chr(10) & _
"from CMS.dbo.disp a " & Chr(13) & "" & Chr(10) & _
"where opendte = Current_Date() " & Chr(13) & "" & Chr(10) & _
"Group By takenby", conn, adOpenStatic, adLockOptimistic


Set xl = Excel.Application

Range("F2").Select
ActiveCell.FormulaR1C1 = _
"CMS Support Report on Analyst Activity"
With ActiveCell.Characters(Start:=1, Length:=53).Font
.Name = "Arial"
.FontStyle = "Italic"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("F2").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With


MsgBox "Open Workbook"
xl.Worksheets("Analyst-Activity").Select
Columns("B:B").Select
Selection.ColumnWidth = 15#
Selection.WrapText = True
Range("B4").Select
ActiveCell.FormulaR1C1 = "Completed Emails"
'Set the focus back at the first cell
Columns("C:C").Select
Selection.ColumnWidth = 15#
Selection.WrapText = True
Range("C4").Select
ActiveCell.FormulaR1C1 = "Open Emails"
'Set the focus back at the first cell
Columns("D:D").Select
Selection.ColumnWidth = 15#
Selection.WrapText = True
Range("D4").Select
ActiveCell.FormulaR1C1 = "Total"

'Open existing worksheet


MsgBox "Open Worksheet"
With xls
'copy recordset onto excel
xl.Range("A5").CopyFromRecordset rs


Set xlw = Nothing
Set xl = Nothing


End With

conn.Close
Set conn = Nothing



End Sub

[Ecniv]

Why bother with code?


Vince

[opus]

As I get it you have one cell with Completed EMails and one with Open EMail Tickets and you want to have the sum of both, why t. F. don't you use Excle to do that.
In other words

Why bother with code?

[bibig1978]

why not use code especially in the case that my takenby column is constantly changing. Somedays i might have two users creating tickets and somedays i might have five.
I believe if i add the fields within the recordset, i might get my answer but i'm not sure how to display that in code. Please somebody help me.

[zeneri]

There is an simple way to get it done:

Let SQL do the work for you. Add an "as completed" and an "as Open" to your select clause. Then you can add a last column completed+open as Total to the statement.

example:
SELECT Tabelle1.a AS x, Tabelle1.b AS y, [x]+[y] AS T
FROM Tabelle1;

[bibig1978]

Thanks Zeneri,
i had thought about doing the "Total" in SQL but the problem is my actual SQL string is a lot longer and more complicated than what i posted. So when I tried to do the Total in SQL, my query ran extremely slow. That's why i wanted to try to see if I can solve this through a recordset.

[zeneri]

Ok. I'm just a little wondering. The SQL-Query should'nt slow down if you add the column with a total. But you may try this one.

Instead of "CopyFromRecordset" loop by yourself. You get the fields of the recorset by rs!Takenby or rs("Takenby"). The first is the better choice if there are no blanks in the columnname.

'copy recordset onto excel
'xl.Range("A5").CopyFromRecordset rs
Dim Row As Integer
Dim Col As Integer
Row = 5
Do While Not rs.EOF
  For Col = 0 To rs.Fields.Count - 1                                  ' \
    xl.Cells(Row, Col + 1).Value = rs.Fields(Col).Value               '  Move all columns
  Next                                                                ' /
  xl.Cells(Row, rs.Fields.Count + 1).Value = rs!completed + rs!Open   ' add the total
  rs.MoveNext                                                         ' next row
  Row = Row + 1
Loop

[bibig1978]

Thanks zeneri ,
I tried your suggestion and it worked...thanks again

[zeneri]

You're welcome.