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?VB Code:
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
