' Force la définition des variables utilisées dans le script
' ----------------------------------------------------------
Option Explicit

' Définition des variables utilisées dans le script
' -------------------------------------------------
Dim xlApp, xlBook, xlSheet, xlChart, xlptCache
Dim xlSheet_manual
Set xlApp = WScript.CreateObject("Excel.Application")
xlApp.Visible = True
set xlBook = xlApp.Workbooks.Add 

With xlBook

' Creation de la feuille Data pour recevoir les résultats de la requete
' ---------------------------------------------------------------------
  set xlsheet = .sheets.add
  xlsheet.name = "Data"

' Creation d'une feuille temporaire pour tester la fonction Pivotcache
' --------------------------------------------------------------------
  set xlSheet_manual = .sheets.add
  with xlSheet_manual
    .name = "Manual_Data"
    .range("A1").value = "Date"
    .range("B1").value = "Partition"
    .range("C1").value = "CPU"

    .range("A2").value = "01/01/2008"
    .range("B2").value = "P1A"
    .range("C2").value = 100.00

    .range("A3").value = "01/01/2008"
    .range("B3").value = "P2A"
    .range("C3").value = 10.00

    .range("A4").value = "01/01/2008"
    .range("B4").value = "P3A"
    .range("C4").value = 50.00

    .range("A5").value = "01/02/2008"
    .range("B5").value = "P1A"
    .range("C5").value = 101.00

    .range("A6").value = "01/02/2008"
    .range("B6").value = "P2A"
    .range("C6").value = 9.00

    .range("A7").value = "01/02/2008"
    .range("B7").value = "P3A"
    .range("C7").value = 48.00
  end with                

' Creation du tableau croisé dynamique
' ------------------------------------
  .PivotCaches.Add(SourceType:=xlDatabase, SourceData:="Manual_Data!R1C1:R3C10").CreatePivotTable TableDestination:="'Data'!R1C1", TableName:="TabXdyn", DefaultVersion=xlPivotTableVersion10
  
End With

' Répartition des données dans le tableau dynamique
' -------------------------------------------------
With xlSheet.PivotTables("TabXdyn").AddFields
  .RowFields= Array("Date")
  .ColumnFields="Partition"
  With .PivotFields("CPU")
    .Orientation = xlDataField
    .NumberFormat = "0.00"
  End With
  .ShowPivotTableFieldList = false
End With

' Creation de la feuille Graphe pour recevoir le graphe du tableau dynamique de la feuille Data
' ---------------------------------------------------------------------------------------------
Set xlChart = .Charts.Add
With xlChart
  .Name = "Graphe"
  .SetSourceData Source=Sheets("Data").Range("A1")
  .Location WHERE=xlLocationAsNewSheet
End with

xlApp.Quit

Err_Procedure:
set xlBook = Nothing
set xlApp =Nothing