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
|
Private Sub bouton_parcCible_Click()
Set db = CurrentDb
'automation pour piloter excel
Dim xlApp As Excel.Application
Dim xlSheet As Excel.Worksheet
Dim xlBook As Excel.Workbook
'Initialisations
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
xlApp.Visible = True
'Ajouter une feuille
Set xlSheet = xlBook.Worksheets.Add
xlSheet.Name = "Fonction"
Set xlSheet = xlBook.ActiveSheet
'Créer un nouveau classeur
' Set wbk = .Workbooks.Add
'**************************************************************************************************************************
'tableau1 : quelle est la quantité de chaque type de véhicule dans le parc cible total ?
'**************************************************************************************************************************
'Stockage de la requête dans un recordset
Dim Rq_Tab As String
Rq_Tab = " Select Quantite, Type from Parc_Veh_Cible_Total;"
Dim Rst_Tab As DAO.recordset
Set Rst_Tab = db.OpenRecordset(Rq_Tab)
i = 2
'Initialisation des entêtes de colonnes du tableau
xlSheet.Cells(i - 1, 2) = "Type"
xlSheet.Cells(i - 1, 3) = "Quantite"
xlSheet.Cells(i - 1, 2).Interior.Color = RGB(100, 190, 10)
xlSheet.Cells(i - 1, 3).Interior.Color = RGB(100, 190, 10)
xlSheet.Cells(i - 1, 2).Borders.LineStyle = xlContinuous
xlSheet.Cells(i - 1, 3).Borders.LineStyle = xlContinuous
'On remplit maintenant le tableau Excel
While Not Rst_Tab.EOF
xlSheet.Cells(i, 2) = Rst_Tab![Type]
xlSheet.Cells(i, 3) = Rst_Tab![Quantite]
xlSheet.Cells(i, 2).Font.Bold = True
xlSheet.Cells(i, 2).Interior.Color = RGB(192, 192, 192)
xlSheet.Cells(i, 2).Borders.LineStyle = xlContinuous
xlSheet.Cells(i, 3).Borders.LineStyle = xlContinuous
i = i + 1
Rst_Tab.MoveNext
Wend
Rst_Tab.Close
xlSheet.Columns(2).AutoFit
xlSheet.Columns(3).AutoFit
o = "B2:C" & i - 1
'******************************************************************************************************************************
'graphique tableau 1
'******************************************************************************************************************************
Dim objChart As Chart, objRange As Range
Set objRange = xlApp.Worksheets("Fonction").Range("(" & o & ")")
Set objChart = xlBook.Charts.Add
objChart.Name = "Graph-Fonction"
objChart.ChartType = xlColumnClustered
objChart.SetSourceData objRange, xlColumns
With xlApp.ActiveChart
objChart.HasTitle = True
objChart.ChartTitle.Characters.Text = "Type et quantité des véhicules dans le parc cible total "
objChart.Axes(xlCategory, xlPrimary).HasTitle = True
objChart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Type"
objChart.Axes(xlValue, xlPrimary).HasTitle = True
objChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Nombre de véhicules"
End With
objChart.SeriesCollection(1).Name = "Nombre de véhicules de ce type"
objChart.SeriesCollection(1).ApplyDataLabels xlDataLabelsShowValue, False
End Sub |
Partager