Results 1 to 9 of 9

Thread: Can somebody please help me with my code?

  1. #1

    Thread Starter
    New Member
    Join Date
    Sep 2004
    Posts
    11

    Can somebody please help me with my code?

    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

  2. #2
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343
    Why bother with code?


    Vince

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  3. #3
    I don't do your homework! opus's Avatar
    Join Date
    Jun 2000
    Location
    Good Old Europe
    Posts
    3,863
    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?
    You're welcome to rate this post!
    If your problem is solved, please use the Mark thread as resolved button


    Wait, I'm too old to hurry!

  4. #4

    Thread Starter
    New Member
    Join Date
    Sep 2004
    Posts
    11
    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.

  5. #5
    Junior Member
    Join Date
    Sep 2004
    Location
    Hamburg / Germany
    Posts
    20
    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;

  6. #6

    Thread Starter
    New Member
    Join Date
    Sep 2004
    Posts
    11
    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.

  7. #7
    Junior Member
    Join Date
    Sep 2004
    Location
    Hamburg / Germany
    Posts
    20
    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.

    VB Code:
    1. 'copy recordset onto excel
    2. 'xl.Range("A5").CopyFromRecordset rs
    3. Dim Row As Integer
    4. Dim Col As Integer
    5. Row = 5
    6. Do While Not rs.EOF
    7.   For Col = 0 To rs.Fields.Count - 1                                  ' \
    8.     xl.Cells(Row, Col + 1).Value = rs.Fields(Col).Value               '  Move all columns
    9.   Next                                                                ' /
    10.   xl.Cells(Row, rs.Fields.Count + 1).Value = rs!completed + rs!Open   ' add the total
    11.   rs.MoveNext                                                         ' next row
    12.   Row = Row + 1
    13. Loop

  8. #8

    Thread Starter
    New Member
    Join Date
    Sep 2004
    Posts
    11

    Can somebody please help me with my code? RESOLVED

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

  9. #9
    Junior Member
    Join Date
    Sep 2004
    Location
    Hamburg / Germany
    Posts
    20
    You're welcome.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width