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
| Public Function InfoSinistres() As Collection
Dim result As New Collection
Dim TabConditions() As Boolean 'Déclare le tableau des conditions de l'accident qui viendra passer les valeurs à la collection
Dim TabPropositions() As Boolean
nbLignes = Sheets(1).Cells(2, 1).End(xlDown).Row 'Détermine le nombre d'enregistrements
'Set Sinistres = Nothing 'Efface une éventuelle collection existante
Dim Sinistre As New clSinistre 'Crée un nouvel enregistrement
For i = 2 To nbLignes 'Boucle de parcours des enregistrements de sinistres
ReDim TabConditions(0 To 19) 'Détermine les dimensions du tableau des conditions de sinistres
For j = 27 To 46 'Boucle de parcours des conditions
TabConditions(j - 27) = (wbSource.Sheets(1).Cells(i, j) = "O") 'Remplissage du tableau de conditions en convertissant les valeurs en Booléen
Next j
ReDim TabPropositions(0 To 18)
For j = 47 To 64
TabPropositions(j - 47) = (wbSource.Sheets(1).Cells(i, j) = "O")
Next j
Set Sinistre = New clSinistre
With Sinistre
.Numero = wbSource.Sheets(1).Cells(i, 1)
.APM = wbSource.Sheets(1).Cells(i, 2)
.Analysed = (wbSource.Sheets(1).Cells(i, 3) = "O")
.Societe = wbSource.Sheets(1).Cells(i, 4)
.Metier = wbSource.Sheets(1).Cells(i, 5)
.Site = wbSource.Sheets(1).Cells(i, 6)
.Immat = wbSource.Sheets(1).Cells(i, 7)
.ImmatRemorque = wbSource.Sheets(1).Cells(i, 8)
.DateSinistre = wbSource.Sheets(1).Cells(i, 9)
.Annee = wbSource.Sheets(1).Cells(i, 10)
.HeureSinistre = wbSource.Sheets(1).Cells(i, 11)
.LieuSinistre = wbSource.Sheets(1).Cells(i, 12)
.Client = wbSource.Sheets(1).Cells(i, 13)
.Declaration = (wbSource.Sheets(1).Cells(i, 14) = "O")
.Entretien = (wbSource.Sheets(1).Cells(i, 15) = "O")
.Circonstances = wbSource.Sheets(1).Cells(1, 16)
.NomConducteur = wbSource.Sheets(1).Cells(i, 17)
.PrenomConducteur = wbSource.Sheets(1).Cells(i, 18)
.DateNaissanceConducteur = wbSource.Sheets(1).Cells(i, 19)
.AncienneteConducteur = wbSource.Sheets(1).Cells(i, 20)
.Exploitant = wbSource.Sheets(1).Cells(i, 21)
.AnalysePar = wbSource.Sheets(1).Cells(i, 22)
.NbHeures = wbSource.Sheets(1).Cells(i, 23)
.StatutConducteur = wbSource.Sheets(1).Cells(i, 24)
.Milieu = wbSource.Sheets(1).Cells(i, 25)
.GenreSinistre = wbSource.Sheets(1).Cells(i, 26)
.Conditions = TabConditions
.PropositionConducteur = TabPropositions
.Responsabilite = wbSource.Sheets(1).Cells(i, 65)
.Constat = wbSource.Sheets(1).Cells(i, 66)
.DateDeclaration = wbSource.Sheets(1).Cells(i, 67)
.UsageTelephone = (wbSource.Sheets(1).Cells(i, 68) = "O")
.Evitable = (wbSource.Sheets(1).Cells(i, 69) = "O")
.RaisonsEvitable = wbSource.Sheets(1).Cells(i, 70)
.RaisonsInevitable = wbSource.Sheets(1).Cells(i, 71)
.Consequences = (wbSource.Sheets(1).Cells(i, 72) = "O")
.Modification = (wbSource.Sheets(1).Cells(i, 73) = "O")
.Commentaires = wbSource.Sheets(1).Cells(i, 74)
End With
Erase TabConditions
Erase TabPropositions
result.Add Sinistre
Next i
Set Sinistres = result
Set result = Nothing |
Partager