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 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184
|
Option Explicit
Sub chargement_Fiche()
Dim identifianteleveur As Integer
Dim OuiNon As String
Dim Path_name As String
Dim NomSauvegarde As String
fin:
debut:
identifianteleveur = InputBox("Saisissez le n°de l'identifiant eleveur sur 1 caractère")
If Not Len(identifianteleveur) <> 1 Then
OuiNon = MsgBox("Veuillez saisir un numero sur un caractères")
If OuiNon = vbOK Then
GoTo debut:
Else
GoTo fin:
End If
End If
Eleveurfin:
Eleveurdebut:
Sheets("Eleveur").Select
Range("A2").Select
OuiNon = MsgBox("Charger l'onglet eleveur?", 1)
If OuiNon = vbCancel Then GoTo Eleveurfin:
Call Requete_Sql(identifianteleveur)
Volaillefin:
Volailledebut:
Sheets("Volaille").Select
Range("A2").Select
OuiNon = MsgBox("charger l'onglet volaille?", 1)
If OuiNon = vbCancel Then GoTo Volaillefin:
Call Requete_Sqlv(identifianteleveur)
Distributeurdebut:
DistributeurFin:
Sheets("Distributeur").Select
Range("A2").Select
OuiNon = MsgBox("charger l'onglet Distributeur?", 1)
If OuiNon = vbCancel Then GoTo DistributeurFin:
Call Requete_Sqld(identifianteleveur)
'distributeurfin:
Sheets("Eleveur").Select
Path_name = ThisWorkbook.Path
NomSauvegarde = Path_name & " \" & identifianteleveur
saveAsFin:
OuiNon = MsgBox("fermeture?")
If OuiNon = vbCancel Then GoTo fin:
'fin:
End Sub
Sub Requete_Sql(identifianteleveur)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mtxData As Variant
Dim colonne As Integer
Dim ligne As Integer
'Dim feuil2 As Worksheet
Dim var_nom As String
Dim Requete As String
'Dim ligne As Long
Dim col As Integer
Dim feuille As Worksheet
ligne = Selection.Rows.Count
col = Selection.Columns.Count
'Dim c As Integer
var_nom = InputBox("donner le numero ")
Requete = "select * from eleveur where id_eleveur = " & var_nom
MsgBox Requete
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open ("User ID=system" & ";Password = " & ";Data Source = localhost" & "; Provider = OraOledB.Oracle")
rs.CursorType = adOpenForwardOnly
rs.Open (Requete), cn
ligne = 2
rs.MoveFirst
While Not rs.EOF
For colonne = 0 To rs.Fields.Count - 1
Worksheets("Eleveur").Cells(ligne, colonne + 1) = rs.Fields(colonne).Value
Next colonne
rs.MoveNext
ligne = ligne + 1
Wend
Set rs = Nothing
Set cn = Nothing
End Sub
Sub Requete_Sqlv(identifianteleveur)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mtxData As Variant
Dim colonne As Integer
Dim ligne As Integer
'Dim feuil2 As Worksheet
Dim var_nom As String
Dim Requete As String
'Dim ligne As Long
Dim col As Integer
Dim feuille As Worksheet
ligne = Selection.Rows.Count
col = Selection.Columns.Count
'Dim c As Integer
var_nom = InputBox(" donner le numero ")
Requete = "select eleveur.id_eleveur , eleveur.nom , abattoir.id_abattoir , abattoir.nom , count(id_volaille)" & _
" from " & _
" eleveur, volaille, abattoir " & _
" where " & _
"(eleveur.id_eleveur = volaille.id_eleveur)" & _
" And " & _
"(volaille.id_abattoir = abattoir.id_abattoir)" & _
" And " & _
"(date_vente Is Not Null)" & _
" (group by) " & _
" (eleveur.id_eleveur, eleveur.nom, abattoir.id_abattoir, abattoir.nom) = " & var_nom
MsgBox Requete
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open ("User ID=system" & ";Password = " & ";Data Source = localhost" & "; Provider = OraOledB.Oracle")
rs.CursorType = adOpenForwardOnly
rs.Open Requete, cn
ligne = 2
rs.MoveFirst
While Not rs.EOF
For colonne = 0 To rs.Fields.Count - 1
Worksheets("Eleveur").Cells(ligne, colonne + 1) = rs.Fields(colonne).Value
Next colonne
rs.MoveNext
ligne = ligne + 1
Wend
Set rs = Nothing
Set cn = Nothing
End Sub
Sub Requete_Sqld(identifianteleveur)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mtxData As Variant
Dim colonne As Integer
Dim ligne As Integer
'Dim feuil2 As Worksheet
Dim var_nom As String
Dim Requeted As String
'Dim ligne As Long
Dim col As Integer
Dim feuille As Worksheet
ligne = Selection.Rows.Count
col = Selection.Columns.Count
'Dim c As Integer
var_nom = InputBox("donner le numero ")
Requeted = "select volaille.id_volaille , eleveur.nom , abattoir.nom , distributeur.nom" & _
" from " & _
"volaille, eleveur, abattoir, fournir, distributeur" & _
"where" & _
"(eleveur.id_eleveur = volaille.id_eleveur)" & _
"And" & _
"(volaille.id_abattoir = abattoir.id_abattoir)" & _
"and" & _
"(volaille.id_volaille=fournir.id_volaille)" & _
"and" & _
"(fournir.id_distributeur=distributeur.id_distributeur)"
MsgBox Requeted
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open ("User ID=system" & ";Password = " & ";Data Source = localhost" & "; Provider = OraOledB.Oracle")
rs.CursorType = adOpenForwardOnly
rs.Open (Requeted), cn
ligne = 2
rs.MoveFirst
While Not rs.EOF
For colonne = 0 To rs.Fields.Count - 1
Worksheets("Distributeur").Cells(ligne, colonne + 1) = rs.Fields(colonne).Value
Next colonne
rs.MoveNext
ligne = ligne + 1
Wend
Set rs = Nothing
Set cn = Nothing
End Sub |
Partager