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 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166
| Private Sub InitialisationDuTableau(ByVal projectName As String)
Dim params As Variant
Dim ligneCourant As Integer
Dim objDBHelper As New DBHelper
Dim cnx As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim identifiant As Integer
Dim sequenceCourant As String
Set cnx = New ADODB.Connection
With cnx
.ConnectionString = objDBHelper.GetConnectionString
Call .Open
End With
Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = cnx
.CommandType = adCmdStoredProc
.CommandText = "usp_MSP_Extract_Project_Tableau"
Set prm = cmd.CreateParameter("@ProjectName", adVarChar, adParamInput, 4000, projectName)
Call .Parameters.Append(prm)
End With
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cnx
.LockType = adLockOptimistic
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
End With
Call rs.Open(cmd, , , adCmdStoredProc) '''''' cette ligne là pose problème
'Affichage du résultat
With rs
Call Application.Goto(Reference:="ORIGINE")
ligneCourant = 0
Do While rs.EOF = False
If IsNull(rs!Sequence) = False Then
'Il faut rajouter un index pour toutes les ressources uniquements
If IsNull(rs!DisplayOrder) = False Then
'1 : Correspond à une tâche
'2 : Correspond à une ressource
If rs!DisplayOrder = 1 Then
sequenceCourant = rs!Sequence
identifiant = 0
Else
identifiant = identifiant + 1
sequenceCourant = rs!Sequence & "." & identifiant
End If
ActiveCell.Offset(ligneCourant, -2).Value = rs!DisplayOrder
End If
ActiveCell.Offset(ligneCourant, -1).Value = "'" & CStr(sequenceCourant)
End If
If IsNull(rs!ProjectCode) = False Then
ActiveCell.Offset(ligneCourant, 0).Value = rs!ProjectCode
End If
If IsNull(rs!TaskName) = False Then
ActiveCell.Offset(ligneCourant, 1).Value = rs!TaskName
End If
If IsNull(rs!TableRateCost) = False Then
ActiveCell.Offset(ligneCourant, 2).Value = rs!TableRateCost
End If
If IsNull(rs!Costs) = False Then
ActiveCell.Offset(ligneCourant, 3).Value = rs!Costs
If rs!Active = 0 Then
ActiveCell.Offset(ligneCourant, 3).NumberFormat = "(#,##0.00)"
Else
ActiveCell.Offset(ligneCourant, 3).NumberFormat = "#,##0.00"
End If
End If
If IsNull(rs!Times) = False Then
ActiveCell.Offset(ligneCourant, 4).Value = rs!Times
If rs!Active = 0 Then
ActiveCell.Offset(ligneCourant, 4).NumberFormat = "(#,##0.00)"
Else
ActiveCell.Offset(ligneCourant, 4).NumberFormat = "#,##0.00"
End If
End If
If IsNull(rs!Durate) = False Then
ActiveCell.Offset(ligneCourant, 5).Value = Replace(rs!Durate, "'", "")
If rs!Active = 0 Then
ActiveCell.Offset(ligneCourant, 5).NumberFormat = "(#,##0)"
Else
ActiveCell.Offset(ligneCourant, 5).NumberFormat = "#,##0"
End If
End If
If IsNull(rs!StartDate) = False Then
ActiveCell.Offset(ligneCourant, 6).Value = rs!StartDate
If rs!Active = 0 Then
ActiveCell.Offset(ligneCourant, 6).NumberFormat = "(dd/mm/yyyy)"
Else
ActiveCell.Offset(ligneCourant, 6).NumberFormat = "dd/mm/yyyy"
End If
End If
If IsNull(rs!EndDate) = False Then
ActiveCell.Offset(ligneCourant, 7).Value = rs!EndDate
If rs!Active = 0 Then
ActiveCell.Offset(ligneCourant, 7).NumberFormat = "(dd/mm/yyyy)"
Else
ActiveCell.Offset(ligneCourant, 7).NumberFormat = "dd/mm/yyyy"
End If
End If
Call .MoveNext
ligneCourant = ligneCourant + 1
Loop
Call .Close
End With
Call cnx.Close
Set cnx = Nothing
Set rs = Nothing
End Sub |
Partager