[E-03]Comment faire un diagramme de type Line.

**XnaMitch** · 13/11/2008, 15h34

Bonjour,

J'essaie de trouver un moyen rapide de faire un diagramme de type Line.
Je veux que pour chaque application id diferent, je puisse avoir une ligne d'une couleur différente.
Et que l'axe des X serait le total de request.
Et l'axe des Y serait les heures.
Pour éclaircir un peu je vais copier la feuille excel que je veux faire mon graphique a l'entour.

From Date ToDate ApplicationID Application Name Total Query Max Query Minimum Query Average Query Methods
23/10/2008 20:00 24/10/2008 0:00 229 58 57 57.25 POST
24/10/2008 0:00 24/10/2008 4:00 229 58 57 57.25 POST
24/10/2008 4:00 24/10/2008 8:00 230 58 57 57.5 POST
24/10/2008 8:00 24/10/2008 12:00 220 141 5 55 GET
24/10/2008 8:00 24/10/2008 12:00 254 73 57 63.5 POST
24/10/2008 8:00 24/10/2008 12:00 0 Application Object Library 61 40 21 15.25 GET
24/10/2008 12:00 24/10/2008 16:00 408 220 17 102 GET
24/10/2008 12:00 24/10/2008 16:00 316 138 58 79 POST
24/10/2008 12:00 24/10/2008 16:00 0 Application Object Library 85 43 10 21.25 GET
24/10/2008 12:00 24/10/2008 16:00 690 CRM Foundation 3 2 1 0.75 GET
24/10/2008 16:00 24/10/2008 20:00 165 161 4 41.25 GET
24/10/2008 16:00 24/10/2008 20:00 236 64 57 59 POST
24/10/2008 16:00 24/10/2008 20:00 0 Application Object Library 40 40 40 10 GET
25/10/2008 20:00 26/10/2008 0:00 585 239 114 146.25 POST
26/10/2008 0:00 26/10/2008 4:00 460 116 114 115 POST
26/10/2008 4:00 26/10/2008 8:00 458 116 114 114.5 POST
26/10/2008 8:00 26/10/2008 12:00 44 44 44 11 GET
26/10/2008 8:00 26/10/2008 12:00 460 116 114 115 POST
26/10/2008 8:00 26/10/2008 12:00 0 Application Object Library 20 20 20 5 GET
26/10/2008 12:00 26/10/2008 16:00 56 56 56 14 GET
26/10/2008 12:00 26/10/2008 16:00 462 116 114 115.5 POST
26/10/2008 12:00 26/10/2008 16:00 0 Application Object Library 20 20 20 5 GET
26/10/2008 16:00 26/10/2008 20:00 458 116 114 114.5 POST
26/10/2008 20:00 27/10/2008 0:00 230 58 57 57.5 POST
27/10/2008 0:00 27/10/2008 4:00 229 58 57 57.25 POST
27/10/2008 4:00 27/10/2008 8:00 229 58 57 57.25 POST
27/10/2008 8:00 27/10/2008 12:00 174 166 8 43.5 GET
27/10/2008 8:00 27/10/2008 12:00 238 64 58 59.5 POST
27/10/2008 8:00 27/10/2008 12:00 0 Application Object Library 34 34 34 8.5 GET
27/10/2008 8:00 27/10/2008 12:00 690 CRM Foundation 4 4 4 1 GET
27/10/2008 12:00 27/10/2008 16:00 65 38 27 16.25 GET
27/10/2008 12:00 27/10/2008 16:00 233 59 57 58.25 POST
27/10/2008 12:00 27/10/2008 16:00 0 Application Object Library 21 11 10 5.25 GET
27/10/2008 16:00 27/10/2008 20:00 79 72 7 19.75 GET
27/10/2008 16:00 27/10/2008 20:00 232 60 57 58 POST
27/10/2008 16:00 27/10/2008 20:00 0 Application Object Library 10 10 10 2.5 GET
27/10/2008 20:00 28/10/2008 0:00 229 58 57 57.25 POST
28/10/2008 0:00 28/10/2008 4:00 229 58 57 57.25 POST
28/10/2008 4:00 28/10/2008 8:00 230 58 57 57.5 POST
28/10/2008 8:00 28/10/2008 12:00 204 204 204 51 GET
28/10/2008 8:00 28/10/2008 12:00 329 157 57 82.25 POST
28/10/2008 8:00 28/10/2008 12:00 0 Application Object Library 31 31 31 7.75 GET
28/10/2008 8:00 28/10/2008 12:00 690 CRM Foundation 2 2 2 0.5 GET
28/10/2008 12:00 28/10/2008 16:00 256 99 26 64 GET
28/10/2008 12:00 28/10/2008 16:00 411 143 60 102.75 POST
28/10/2008 12:00 28/10/2008 16:00 0 Application Object Library 31 11 10 7.75 GET
28/10/2008 16:00 28/10/2008 20:00 249 99 22 62.25 GET
28/10/2008 16:00 28/10/2008 20:00 380 144 58 95 POST
28/10/2008 16:00 28/10/2008 20:00 0 Application Object Library 20 10 10 5 GET
28/10/2008 20:00 29/10/2008 0:00 170 134 2 42.5 GET
28/10/2008 20:00 29/10/2008 0:00 232 60 57 58 POST
28/10/2008 20:00 29/10/2008 0:00 0 Application Object Library 31 21 10 7.75 GET
29/10/2008 0:00 29/10/2008 4:00 230 58 57 57.5 POST
29/10/2008 4:00 29/10/2008 8:00 229 58 57 57.25 POST
29/10/2008 8:00 29/10/2008 12:00 98 93 2 24.5 GET
29/10/2008 8:00 29/10/2008 12:00 237 63 57 59.25 POST
29/10/2008 8:00 29/10/2008 12:00 0 Application Object Library 10 10 10 2.5 GET
29/10/2008 8:00 29/10/2008 12:00 690 CRM Foundation 1 1 1 0.25 GET
29/10/2008 12:00 29/10/2008 16:00 296 118 29 74 GET
29/10/2008 12:00 29/10/2008 16:00 278 79 61 69.5 POST
29/10/2008 12:00 29/10/2008 16:00 0 Application Object Library 50 21 1 12.5 GET
29/10/2008 12:00 29/10/2008 16:00 690 CRM Foundation 4 2 2 1 GET
29/10/2008 16:00 29/10/2008 20:00 65 55 5 16.25 GET
29/10/2008 16:00 29/10/2008 20:00 232 60 57 58 POST

**XnaMitch** · 20/11/2008, 15h39

Bon apres plusieurs heures de développement, j'en ai tiré sa pour mon code...
Mais j'ai une erreure que jessaie de résoudre depuis plusieurs heures si quelqu'un pourrait m'aider... Merci!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
 
Dim Application_Id() As String
Dim objSheet As Worksheet
 Private Sub FillApplicationID() 
 Dim L, C As Integer
 Dim LineCounter2 As Integer
 Dim BFound As Boolean
 Dim blDimensioned As Boolean
 '******************************************************** 
  Set objSheet = ActiveSheet
    LineCounter2 = 3
    Do While Not IsEmpty(objSheet.Cells(LineCounter2, 1))
        LineCounter2 = LineCounter2 + 1
        BFound = False
        If blDimensioned = True Then
            For L = 0 To UBound(Application_Id)
                If CStr(Application_Id(L)) = objSheet.Cells(LineCounter2, 3) Then
                    BFound = True
                    End If
            Next                
            If BFound = False Then
                ReDim Preserve Application_Id(0 To UBound(Application_Id) + 1) As String
                Application_Id(UBound(Application_Id)) = objSheet.Cells(LineCounter2, 3)
            End If
        Else
            ReDim Application_Id(0 To 0) As String
            blDimensioned = True
            Application_Id(0) = objSheet.Cells(LineCounter2, 3)
        End If
    Loop    
 End Sub 
 Public Sub CreateSeries() 
    Dim L, C As Integer
    Dim blDimensionedValues As Boolean
    Dim XValues_Array As Variant
    Dim Values_Array As Variant
    Dim LineCounter As Integer    
    Charts.Add
            ActiveChart.ChartType = xlXYScatterLines '**************************************************************
 C = 0
     For C = 0 To UBound(Application_Id)            
            LineCounter = 3
            blDimensionedValues = False                       
            Do While Not IsEmpty(objSheet.Cells(LineCounter, 1))
                LineCounter = LineCounter + 1                
                If Application_Id(C) = objSheet.Cells(LineCounter, 3) Then
                    If blDimensionedValues = True Then
                        ReDim Preserve Values_Array(0 To UBound(Values_Array) + 1)
                        ReDim Preserve XValues_Array(0 To UBound(XValues_Array) + 1)
                        Values_Array(UBound(Values_Array)) = objSheet.Cells(LineCounter, 5)
                        XValues_Array(UBound(XValues_Array)) = CLng(objSheet.Cells(LineCounter, 1))
                    Else
                        ReDim Values_Array(0 To 0)
                        ReDim XValues_Array(0 To 0)
                        blDimensionedValues = True
                        Values_Array(0) = objSheet.Cells(LineCounter, 5)
                        XValues_Array(0) = CLng(objSheet.Cells(LineCounter, 1))
                    End If
                End If
            Loop   
            'Charts.Add
            'ActiveChart.SeriesCollection.NewSeries
            'ActiveChart.SeriesCollection(C).XValues = XValues_Array
            'ActiveChart.SeriesCollection(C).Values = Values_Array
            'ActiveChart.SeriesCollection(C).Name = Application_Id
            'ActiveChart.Location Where:=xlLocationAsNewSheet
             With ActiveChart.SeriesCollection.NewSeries
                            .Name = "Repport"
                            .Values = Values_Array
                            '.XValues = CStr(XValues_Array)
                            .XValues = XValues_Array                            
            End With
      Next
 End Sub
Private Sub CommandButton1_Click()
    FillApplicationID
    CreateSeries
End Sub

L'erreur que je recois quand j'excute mon bloc de code c'Est ...

Unable to set XValues property to the series class

Et la ligne prise en defaut est la suivante:

.XValues = XValues_Array

[E-03]Comment faire un diagramme de type Line.

Macros et VBA Excel

Discussions similaires

Partager

Partager