Excel chart using macro

[yaminik_1981]

hi all,
im writing a macro to generate an excel chart.im new to macro and chart.with the following coding im able to get values for the y axis, but not the values for x axis.can anyone help with this?

Public Sub Reports()
Sheet2.Cells(2, 1).Value = ""
Sheet2.Cells(2, 2).Value = ""
Sheet2.Cells(2, 3).Value = ""
Sheet2.Activate
 
Set objconn = New ADODB.Connection
objconn.Open "ivrserver", "sa", ""
Dim rs As Recordset
Dim DaySec As Long, nDateDiff As Long, iDCnt As Long
Dim TempEdTime As String, TempStTime As String
 
Set ors = New ADODB.Recordset
StrSql = "SELECT distinct(CalledID),Count(*)[TotalCalls] FROM CallLogSummary WHERE CallTime >= " & p1 & " AND EndTime <= " & p2
If CalledID <> "" Then
StrSql = StrSql & " and CalledID in (" & CalledID & ")"
End If
'''If callType <> "" Then
'''StrSql = StrSql & " and callType in (" & callType & ")"
'''End If
'''If IVRFlow <> "" Then
'''StrSql = StrSql & " and ApplicationName in ('" & IVRFlow & "')"
'''End If
StrSql = StrSql & " group by Calledid"
ors.Open StrSql, objconn
 
lngCurRow = 1
lngCurCol = 0
 
If Not ors.EOF Then
ors.MoveFirst
While Not ors.EOF
RecCnt = RecCnt + 1
ors.MoveNext
Wend
ors.MoveFirst
If RecCnt > 100 Then
Temp = RecCnt - 100
End If
 
For i = 1 To Temp
ors.MoveNext
Next i
End If
 
ActiveSheet.Range("a1" & ":a" & (RecCnt)).Columns.Select
 
 
Set rptchart = Excel.Charts.Add
ors.MoveFirst
While Not ors.EOF
'Activesheet.Cells(lngCurRow, 1) = ors("LineID")
Sheet2.Cells(lngCurRow, 2) = ors("CalledId")
lngCurRow = lngCurRow + 1
ors.MoveNext
Wend
ors.MoveFirst
While Not ors.EOF
lngCurCol = lngCurCol + 1
Sheet2.Cells(lngCurCol, 1) = ors("TotalCalls")
' Sheet2.Cells(1, lngCurCol) = ors("TotalCalls")
ors.MoveNext
Wend
 
With rptchart
.PlotBy = xlRows
.HasTitle = True
.ChartTitle.Caption = "Number of Calls in each CalledID"
.ChartTitle.Font.Size = 12
.ChartTitle.Font.Bold = True
 
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory).AxisTitle.Text = "Called ID"
 
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue).AxisTitle.Text = "Total Calls"
End With
 
rptchart.Activate
Exit Sub
errhand:
MsgBox "Error No : " & Err.Number & ",Description : " & Err.Description & ",Source : " & Err.Source
End Sub

[Hack]

Excel VBA question moved to Office Development