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
|
Private Sub Commande1_Click()
On Error GoTo Err_Commande1_Click
Dim XlApp As Excel.Application
Dim xlSheet As Excel.Worksheet
Dim xlBook As Excel.Workbook
Dim i As Long, j As Long
Dim t0 As Long, t1 As Long
Dim Date_choisie As String
Dim Chemin As String
Dim ligne_fin, num_date, res As Long
Dim db As DAO.Database
Dim rstT_Pays, rstTemp, rstTemp2 As DAO.Recordset
t0 = Timer
'Initialisations
Set XlApp = CreateObject("Excel.Application")
Set xlBook = XlApp.Workbooks.Add
Set db = CurrentDb
'Ajouter une feuille de calcul
Set xlSheet = xlBook.Worksheets.Add
xlSheet.Activate
'Création des blocs de BB
Date_choisie = Me.Modifiable4
ligne_fin = 5
res = 0
Mise_en_forme_haut xlSheet, Date_choisie
'On cherche la date
If Date_choisie = "Date Notification" Then num_date = 11
If Date_choisie = "Date FAT" Then num_date = 8
If Date_choisie = "Date SAT" Then num_date = 9
If Date_choisie = "Date Prévisionnelle" Then num_date = 10
'Boucle de création des blocs
Set rstT_Pays = db.OpenRecordset("SELECT Pays FROM T_Pays ORDER BY Pays")
Do While Not rstT_Pays.EOF
Set rstTemp = db.OpenRecordset("SELECT * FROM T_Prod_Prog Where T_Prod_Prog.Pays = '" & rstT_Pays.Fields(0).Value & "' ")
Set rstTemp2 = db.OpenRecordset("SELECT DISTINCT Fourniture FROM T_Prod_Prog Where T_Prod_Prog.Pays = '" & rstT_Pays.Fields(0).Value & "' ")
ligne_fin = Bloc(xlSheet, rstTemp, rstTemp2, (ligne_fin), (num_date))
rstT_Pays.MoveNext
Loop
' code de fermeture et libération des objets
Chemin = Application.CurrentProject.Path + "\ Radar " + Date_choisie + ".xls"
xlBook.SaveAs Chemin
MsgBox "Un fichier excel a été créé à l'adresse suivante : " & Chemin
xlBook.WebPagePreview
XlApp.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set XlApp = Nothing
t1 = Timer
Debug.Print i & " enregistrements", Format(t1 - t0, "0") & " secondes"
Exit_Commande1_Click:
Exit Sub
Err_Commande1_Click:
MsgBox Err.Description
Resume Exit_Commande1_Click
End Sub |
Partager