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
| connString = "DSN=TOP;Uid=TOPMAN01;Pwd=TOPMAN01"
conn.Open connString
rsRecords.CursorLocation = adUseServer
rsRecords.Open "select a.num_achat,al.id_achat_ligne,art.id_article,al.num_ligne,art.reference,art.designation,nvl(sf.libelle, ' ') as sous_famille,nvl(f.libelle, ' ') as famille,al.tot_cmde,u.libelle,u2.libelle as unite_achat,nvl(cv.ratio_u1_u2,1), " _
& " Case u.libelle when u2.libelle then al.tot_cmde when 'Millimètre' then (al.tot_cmde/1000) * nvl(cv.ratio_u1_u2,1) else al.tot_cmde * nvl(cv.ratio_u1_u2,1) end as qte_achat, nvl(rs.reference, ' ') as ref_externe from t_achat a inner join t_fst f " _
& " on a.id_fst = f.id_fst inner join t_achat_ligne al on a.id_achat = al.id_achat inner join t_article art on art.id_article = al.id_article inner join topsys.unit u on al.id_unite_tot_cmde = u.id_unit inner join topsys.unit u2 on art.id_unite_achat = u2.id_unit " _
& " left outer join topsys.unit_abaque ab on art.id_unite_abaque = ab.id_unit_abaque left outer join topsys.unit_conv cv on ab.id_unit_abaque = cv.id_unit_abaque left outer join toperp.t_sousfamille sf on art.id_sousfamille = sf.id_sousfamille left outer join toperp.t_famille f " _
& " on art.id_famille = f.id_famille left outer join toppdm.reference_supply rs on (art.id_article = nvl(rs.id_erp_item,0) and nvl(rs.id_from,0) = f.id_fst) where nvl(art.id_groupe_article, 0) = 3 and al.id_article <> 43057 and f.raison_sociale = '" & Cfour.Value & "' and a.date_creation between TO_DATE('" & ddeb.Value & "','DD/MM/YYYY') and TO_DATE('" & dfin.Value & "','DD/MM/YYYY') order by a.id_achat, al.id_achat_ligne", conn, adOpenForwardOnly, adLockReadOnly
If conn.State = adStateOpen Then
lig = num_lig_dep
' Lecture du premier enregistrement
rsRecords.MoveFirst
While Not rsRecords.EOF
With Worksheets("CDE")
.Range("A" & lig).Value = rsRecords.Fields("num_achat").Value
.Range("B" & lig).Value = rsRecords.Fields("num_ligne").Value
.Range("C" & lig).Value = rsRecords.Fields("reference").Value
.Range("D" & lig).Value = rsRecords.Fields("designation").Value
.Range("E" & lig).Value = rsRecords.Fields("famille").Value
.Range("F" & lig).Value = rsRecords.Fields("sous_famille").Value
.Range("G" & lig).Value = rsRecords.Fields("tot_cmde").Value
.Range("H" & lig).Value = rsRecords.Fields("libelle").Value
.Range("I" & lig).Value = rsRecords.Fields("unite_achat").Value
.Range("J" & lig).Value = rsRecords.Fields("qte_achat").Value
End With
lig = lig + 1
' Lecture de l'enregistrement suivant
rsRecords.MoveNext
Wend
Else
MsgBox "Problème de connexion à la base de données."
End If
rsRecords.Close
Set rsRecords = Nothing
conn.Close
Set conn = Nothing |
