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
| Private Sub AffichLstFactures()
Dim WS As Worksheet
Dim i As Long
Dim Connexion As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim Req As String
If SheetExists("MFactures") Then
Application.DisplayAlerts = False
ThisWorkbook.Worksheets("MFactures").Delete
Application.DisplayAlerts = True
End If
Set WS = ThisWorkbook.Worksheets.Add
WS.Name = "MFactures"
Connexion.Open "DSN=MS Access Database;DBQ=M:\commun\DIMITRI\BDD\BASE1.mdb;"
Req = Req + "SELECT Ventes.Code_Vente, Ventes.DateVente, Nom, Prénom, Total "
Req = Req + "FROM Personnes INNER JOIN "
Req = Req + "("
Req = Req + "SELECT *"
Req = Req + "FROM Ventes LEFT JOIN "
Req = Req + "("
Req = Req + "SELECT Code_Vente , SUM(TotalLigne) AS Total "
Req = Req + "FROM LigneVentes "
Req = Req + "GROUP BY Code_Vente"
Req = Req + ") "
Req = Req + "AS NewTable ON NewTable.Code_Vente = Ventes.Code_Vente "
Req = Req + "ORDER BY Ventes.DateVente ASC"
Req = Req + ") "
Req = Req + "AS NewTable2 ON Personnes.Code_Personne = NewTable2.Code_Personne"
Set rs = Connexion.Execute(Req)
With WS
.Cells(2, 1).CopyFromRecordset rs
For i = 2 To Fin(WS)
.Cells(i, 2).NumberFormat = "DD/MM/YYYY"
.Cells(i, 3) = RTrim(.Cells(i, 3))
.Cells(i, 4) = RTrim(.Cells(i, 4))
.Cells(i, 5) = Cdbl(Cells(i, 5))
Next i
End With
Connexion.Close
Set Connexion = Nothing
End Sub |
Partager