Utilisation de l'objet OWC PivotTable
dans un UserForm Excel



Image non disponible

Cet article présente l'utilisation du contrôle OWC PivotTable dans un UserForm.

Les exemples ont été testés avec:
* Excel2007, WinXP et OWC11.
* Excel2002, WinXP et OWC10.

Article lu   fois.

L'auteur

Site personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Introduction

Le complément Microsoft Office Web Components (OWC ou Composants Web) est une collection de contrôles permettant de publier sur le Web des feuilles de calcul, des graphiques, des bases de données et des tableaux croisés dynamiques.
Ces contrôles peuvent aussi être utilisés dans les applications bureautiques Office, et notamment dans les UserForms.


Si le complément Microsoft Office Web Components n'est pas disponible sur le poste de travail, téléchargez les composants en fonction de votre version d'Office.

Complément d'Office 2003 : composants Web Office OWC11
Office XP Tool: Web Components OWC10


Ensuite, faites un clic droit dans votre UserForm et sélectionnez l'option Contrôles supplémentaires.

Image non disponible


Cochez la ligne Microsoft Office PivotTable xx.x et cliquez sur le bouton OK pour valider.
xx.x dépend de votre version d'Office:
     11.0 (OWC11) pour Office 2003 et Office 2007.
     10.0 (OWC10) pour Office 2002.


Le contrôle PivotTable est désormais disponible dans la boîte à outils.

Image non disponible




La démo présentée dans cet article utilise le contrôle PivotTable afin de créer et manipuler un tableau croisé dynamique dans un UserForm.

Image non disponible


Tout comme un classique TCD de l'application Excel, cet outil permet d'effectuer des calculs (somme, nombre, nb, moyenne, produit, max, min, ecarttype, var) et d'analyser de façon Dynamique vos sources de données.
Le contrôle OWC PivotTable ne remplace pas les tableaux croisés dynamiques de la feuille , mais peut être un support complémentaire si vous souhaitez effectuer des calculs directement depuis un UserForm.


Dans cet exemple, les données sont stockées dans la Feuil2 du même classeur, mais vous pouvez facilement l'adapter et utiliser une source externe (table Access, fichier texte, feuille d'un classeur Excel fermé ...)

II. Description générale

II-A. La fenêtre 'Liste des champs'

Image non disponible


La fenêtre contient tous les champs qui peuvent être utilisés dans les zones du PivotTable.


Il existe trois types de champs:
     * Les jeux de champs
     * Les champs
     * Les totaux

Les jeux de champs sont les éléments supérieurs des noeuds extensibles.
Les Champs sont les éléments inférieurs des jeux de champs.
Le noeud Totaux (Totals) regroupe les champs de calcul automatiques (Somme, Moyenne, Min, Max, Variance...) et les champs de calcul personnalisés.


Les jeux de champs et les champs sont issus de la requête effectuée dans la base de données.
Pour les données de type Date, le jeu de champs contient automatiquement des champs spécifiques (année, trimestre, mois, semaine, jour...) pour faciliter les filtres et les regroupements.

Image non disponible

II-B. Les zones du PivotTable

Image non disponible


Le PivotTable est constitué de 4 zones:
     * Les champs de ligne
     * Les champs de colonne
     * Les champs de filtre
     * Les totaux et les champs de détails


Pour construire votre TCD, faites glisser/déposer les champs de la fenêtre 'Liste des champs' vers les zones du PivotTable.

II-C. La barre d'outils

Image non disponible


1. Bouton 'A propos de Microsoft Office Web Components'.
Affiche des informations générales sur la version, le Product ID, le copyright du contrôle OWC.


2. Permet de copier la zone sélectionnée dans le PivotTable.


3. Trie la colonne sélectionnée par ordre croissant.


4. Trie la colonne sélectionnée par ordre décroissant.


5. Filtre automatique.


6. Affiche les éléments les plus hauts et les plus bas.
Sélectionnez une cellule du tableau croisé dynamique pour que le bouton soit actif.
Ensuite, cliquez sur le bouton et choisissez le filtre à appliquer.

Image non disponible


7. Applique un calcul automatique.
Affichez le détail d'un champ (14) et sélectionnez une cellule pour activer le bouton.

Image non disponible


8. Sous-total.


9. Champ calculé.


10. Affiche la sélection sous forme de pourcentage.

Image non disponible


11. Réduire.


12. Développer.


13. Masque les détails.


14. Affiche les détails.


15. Actualisation des données.


16. Export vers Excel (pas réussi à faire fonctionner cette transaction).


17. Commandes et options pour la mise en forme du PivotTable.


18. Affiche la fenêtre 'Liste des champs'.
Faites ensuite glisser les champs dans les différentes zones du PivotTable afin de créer le tableau croisé dynamique.


19. Ouvre la fenêtre d'aide des PivotTable. Le fichier d'aide contient de nombreuses informations au sujet de ce contrôle.


20. Bouton personnalisé.
Il est possible d'ajouter des boutons personnels dans la barre d'outils, par exemple:

Vba
Sélectionnez

' --------- Ajout et gestion d'un bouton personnalisé dans le pivotTable --------
Private Sub CommandButton5_Click()
   Dim Tb 'As ????
   Dim NouveauBouton 'As ????

   'Définit la barre d'outils du contrôle PivotTable.
   Set Tb = PivotTable1.Toolbar

   On Error Resume Next
   
   'Définit l'image qui va être associée au bouton.
   'Privilégiez des fichiers .ico pour la visibilité
   'Dans cet exemple, l'image est stockée dans le même répertoire que le classeur.
   Tb.ImageList.ListImages.Add , "NomImage", LoadPicture(ThisWorkbook.Path & "\bouton.ICO")

   If Err.Number > 0 Then
      MsgBox "L'image 'bouton.ICO' n'a pas pu être chargée: Vérifiez le chemin"
      Exit Sub
   End If
   
   On Error GoTo 0
   
   
   'Définition du nouveau bouton
   'Tb.Buttons.Count + 1 permet de placer le bouton en dernier (sur la droite)
   Set NouveauBouton = Tb.Buttons.Add(Tb.Buttons.Count + 1, "Bouton01", , , "NomImage")
 
   'Applique une info-bulle au nouveau bouton
   NouveauBouton.TooltipText = "Info-bulle du nouveau bouton."

   'Insère le bouton et ajoute une séparation après le dernier bouton existant.
   Tb.Buttons.Add Tb.Buttons.Count, , , 3
   
   PivotTable1.Refresh
   CommandButton5.Enabled = False
End Sub



II-D. Les connexions

II-D-1. Les fenêtres de connexion

Lorsque le contrôle est inséré dans votre UserForm, cliquez sur le lien pour établir la connexion avec la base de données.

Image non disponible


Cliquez sur le bouton de connexion (Edit).

Image non disponible


Cliquez sur le bouton Nouvelle source.

Image non disponible


Choisissez DSN ODBC dans le type de source et cliquez sur le bouton Suivant.

Image non disponible


Sélectionnez le format de la base (ici MS Access Database) et cliquez sur le bouton Suivant.

Image non disponible


Recherchez la base Access sur votre disque dur et validez en cliquant sur le bouton OK.

Image non disponible


Sélectionnez la table contenant les données sources et cliquez sur le bouton Suivant.

Image non disponible


Personnalisez les informations (nom, description...) et cliquez sur le bouton OK pour enregistrer la connexion.

Image non disponible

II-D-2. Les connexions manuelles

Pour lier le PivotTable aux données sources, vous pouvez aussi renseigner directement les propriétés ConnectionString et DataMember.

Image non disponible




Exemple de connexion à une table Access:

ConnectionString:
Provider=MSDASQL.1;Persist Security Info=True;Extended Properties="DSN=MS Access Database;DBQ=C:\DOCUMENTS AND SETTINGS\mimi\dossier\maBase.mdb;DefaultDir=C:\DOCUMENTS AND SETTINGS\mimi\dossier;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;";Initial Catalog=C:\DOCUMENTS AND SETTINGS\mimi\dossier\maBase.mdb


DataMember:
`C:\DOCUMENTS AND SETTINGS\mimi\dossier\maBase.mdb`.`Table1`

Vous pouvez aussi créer une requête dans la table source. Indiquez ce type de syntaxe dans la propriété CommandText:
SELECT Champ1,Champ2 FROM Table1




Exemple de connexion à un classeur Excel:

Il est possible de se connecter à un classeur Excel à condition que les données soient correctement structurées (la première ligne sert à indiquer le nom des champs, à partir de la première colonne et les informations ne sont pas éparpillées dans la feuille).

ConnectionString:
Provider=MSDASQL.1;Persist Security Info=True;Extended Properties="DSN=Fichiers Excel;DBQ=C:\Documents and Settings\mimi\dossier\PivotTable_UserForm_XL2002.xls;DefaultDir=C:\Documents and Settings\mimi\dossier;DriverId=790;MaxBufferSize=2048;PageTimeout=5;";Initial Catalog=C:\Documents and Settings\mimi\dossier\PivotTable_UserForm_XL2002

Nota: L'extension .xls n'est pas spécifiée dans le paramètre Initial Catalog.


DataMember:
`C:\Documents and Settings\mimi\dossier\PivotTable_UserForm_XL2002`.`Feuil2$`
Ou pour une plage nommée:
`C:\Documents and Settings\mimi\dossier\PivotTable_UserForm_XL2002`.`PlageNommee`

Nota: L'extension .xls n'est pas spécifiée.


CommandText:
SELECT * FROM [Feuil2$]
Ou pour une plage nommée:
SELECT * FROM [PlageNommee]

II-D-3. Les connexions automatisées

La connexion peut aussi être paramétrée par macro. Il s'agit en fait d'automatiser le renseignement des propriétés présentées dans le chapitre précédent. Cela facilitera ensuite le déplacement du classeur sans devoir réécrire manuellement le chemin dans la propriété ConnectionString.


Pour l'exemple qui nous intéresse (les données sources sont dans la Feuil2 du classeur), utilisez:

Vba
Sélectionnez

    'Le catalogue est représenté par le chemin complet du classeur, sans l'extension (.xls).
    Cible = Left(ThisWorkbook.FullName, Len(ThisWorkbook.FullName) - 4)
    'Empêche l'affichage de la fenêtre gestion des champs
    PivotTable1.DisplayFieldList = False
    
    
    'Connexion
    PivotTable1.ConnectionString = _
        "Provider=MSDASQL.1;Persist Security Info=True;Extended Properties=" & Chr(34) & _
        "DSN=Fichiers Excel;DBQ=" & ThisWorkbook.FullName & ";DefaultDir=" & ThisWorkbook.Path & _
        ";DriverId=790;MaxBufferSize=2048;PageTimeout=5;" & Chr(34) & _
        ";Initial Catalog=" & Cible					


    'Requète dans la feuille (Feuil2) contenant les données sources
    PivotTable1.CommandText = "SELECT * FROM [Feuil2$]"
    'Exemple pour filtrer les champs
    'PivotTable1.CommandText = "SELECT [Feuil2$].[Ville] FROM [Feuil2$]"



III. Les évènements

Le contrôle PivotTable dispose de plusieurs évènements pour interagir avec les actions de l'utilisateur.
Quelques exemples:

BeforeContextMenu
Survient lors de l'utilisation du menu contextuel (clic droit) dans le PivotTable.

Click
Survient lorsque l'utilisateur clique dans le PivotTable.

CommandExecute
Survient après la sélection d'une commande de la barre de menus ou de la barre d'outils.

OnConnect
Survient lorsque le PivotTable se connecte à la source de données.

PivotTableChange
Survient lorsqu'un champ, un jeu de champs ou un total est ajouté ou supprimé du PivotTable.

Query
Survient lorsque le PivotTable fait une requête dans la source de données.

SelectionChange
Survient à chaque sélection dans le PivotTable.

ViewChange
Survient à chaque fois que le PivotTable est redessiné.


Les sous chapitres suivants décrivent les évènements utilisés dans le classeur démo.

III-A. BeforeContextMenu

L'évènement BeforeContextMenu gère le menu contextuel (clic droit dans le PivotTable).


Vous pouvez:
     * Désactiver totalement le clic droit.
     * Ajouter des transactions personnalisées dans le menu, avec la possibilité de masquer les sous menus prédéfinis.

Vba
Sélectionnez

Private Sub PivotTable1_BeforeContextMenu(ByVal x As Long, ByVal y As Long, _
        ByVal Menu As OWC11.ByRef, ByVal Cancel As OWC11.ByRef)
    
    Dim MonMenu()
    Dim j As Integer
    
    '----
    'Permet de désactiver le menu contextuel
    'Cancel = True
    '----
    
    If CheckBox1.Value = False Then
        'Ajoute des nouveaux élements à la suite des éléments existants, dans
            'Le menu contextuel.
        MonMenu = Menu.Value
        j = UBound(MonMenu) + 3
    
        ReDim Preserve MonMenu(j)
    
        MonMenu(j - 2) = Empty
        'Array(Description, MotClé)
        MonMenu(j - 1) = Array("Afficher la chaîne de connexion", "MenuPerso01")
        MonMenu(j) = Array("Afficher le recordset en cours", "MenuPerso02")
        
    Else
    
        'Crée un nouveau menu contextuel personnalisé
        ReDim Preserve MonMenu(1)
        
        'Array(Description, MotClé)
        MonMenu(0) = Array("Afficher la chaîne de connexion", "MenuPerso01")
        MonMenu(1) = Array("Afficher le recordset en cours", "MenuPerso02")
        
    End If
    
    Menu.Value = MonMenu
End Sub



III-B. Click

La procédure ci dessous gère l'évènement Click dans le PivotTable.


La macro vérifie le type d'objet cliqué (TypeName(oPvtSel)) et affiche dans un Label le contenu de la cellule sélectionnée dans la zone totaux et champs de détails.

Vba
Sélectionnez

'Evènement Clic sur le TCD
'Cet exemple extrait le contenu de la cellule sélectionnée.
Private Sub PivotTable1_Click()
    Dim oPvtSel As Object
    Dim PivotAgg As PivotAggregate
    Dim strTotal As String
    Dim NomCol As String, NomLigne As String
    Dim Resultat As String
   
    'Définit la sélection
    Set oPvtSel = PivotTable1.Selection
   
    If TypeName(oPvtSel) = "PivotAggregates" Then
        Set PivotAgg = oPvtSel.Item(0)
        Resultat = PivotAgg.Value
      
        strTotal = PivotAgg.Total.Caption
        'nom colonne
        NomCol = PivotAgg.Cell.ColumnMember.Caption
        'nom élément ligne
        NomLigne = PivotAgg.Cell.RowMember.Caption
      
        'Remise en forme du résultat en fonction de la colonne cliquée
        If strTotal = "Pourcentage du Total" Then Resultat = Format(Resultat, "0.00 %")
        If strTotal = "Moyenne par ville" Then Resultat = Format(Resultat, "00.00")
        
        Label1.Caption = Resultat & vbCrLf & strTotal & vbCrLf & NomLigne & vbCrLf & NomCol
    End If

End Sub



III-C. CommandExecute

L'événement CommandExecute se déclenche après la sélection d'une commande de la barre de menus ou de la barre d'outils.

L'argument Command identifie la commande exécutée et permet par exemple de lancer une macro en fonction du bouton ou du menu déclenché.

Vba
Sélectionnez

Private Sub PivotTable1_CommandExecute(ByVal Command As Variant, ByVal Succeeded As Boolean)
    
    'L'argument "Command" permet de contrôler la commande exécutée.
    If VarType(Command) = vbString Then
        Select Case Command
            'Déclenche la macro du bouton personnalisé.
            Case "Bouton01"
                NomMacro_01
                
            'Déclenche les macros associées aux nouveaux éléments du menu contextuel
            Case "MenuPerso01"
                NomMacro_02
        
            Case "MenuPerso02"
                NomMacro_03
        End Select
    End If
End Sub



IV. Les procédures du classeur démo

Les codes suivants correspondent à une utilisation dans Excel2003/2007 et OWC11.


Le tableau croisé dynamique est créé automatiquement lors du chargement du UserForm et permet de visualiser:

     * Le nombre d'enregistrements par Ville.
     * La somme du champ "Valeur" par ville.
     * La moyenne du champ "Valeur" par ville.
     * Un champ calculé "pourcentage du total pour chaque ville".
     * L'exemple montre aussi comment:
          - Extraire des résultats particuliers en cliquant sur les cellules du PivotTable.
          - Mettre en forme les données du tableau (couleur des cellules, type de caractères, formats des valeurs ...).
          - Une option permet de créer un filtre par semaine sur le champ "ChampDate".
          - Il est ainsi possible de choisir les semaines à afficher dans le PivotTable.


Vba
Sélectionnez

Option Explicit


Private Sub UserForm_Initialize()
    Dim Cible As String
    Dim oPvtView As OWC11.PivotView
    Dim oFieldSets As OWC11.PivotFieldSets
    Dim oPvtTotal As PivotTotal
    Dim GrdTotal As Integer
    
    
    'Le catalogue est représenté par le chemin complet du classeur, sans l'extension (.xls).
    Cible = Left(ThisWorkbook.FullName, Len(ThisWorkbook.FullName) - 4)
    'Empêche l'affichage de la fenêtre gestion des champs
    PivotTable1.DisplayFieldList = False
    
    
    'Connexion
    PivotTable1.ConnectionString = _
        "Provider=MSDASQL.1;Persist Security Info=True;Extended Properties=" & Chr(34) & _
        "DSN=Fichiers Excel;DBQ=" & ThisWorkbook.FullName & ";DefaultDir=" & ThisWorkbook.Path & _
        ";DriverId=790;MaxBufferSize=2048;PageTimeout=5;" & Chr(34) & _
        ";Initial Catalog=" & Cible
    
    
    'Requète dans la feuille (Feuil2) contenant les données sources
    PivotTable1.CommandText = "SELECT * FROM [Feuil2$]"
    'Exemple pour filtrer les champs
    'PivotTable1.CommandText = "SELECT [Feuil2$].[Ville] FROM [Feuil2$]"
    
    
    Set oPvtView = PivotTable1.ActiveView
    Set oFieldSets = oPvtView.FieldSets


    With oPvtView
        'Ajoute le champ "Ville" dans la zone Row (ligne)
        .RowAxis.InsertFieldSet oFieldSets("Ville")
        .FieldSets("Ville").Fields(0).Caption = "Liste des villes"

        'Ajoute le champ "Valeurs" dans la zone données (Data)
        .DataAxis.InsertFieldSet oFieldSets("Valeurs")
        


        'Ajoute une fonction Nombre
        Set oPvtTotal = oPvtView.AddTotal("Nombre par ville", _
            oFieldSets("Valeurs").Fields(0), plFunctionCount)
        .DataAxis.InsertTotal oPvtTotal
        'Alignement centré
        oPvtTotal.HAlignment = plHAlignCenter
        'Couleur de fond
        oPvtTotal.View.TotalBackColor = RGB(220, 180, 200)

        
        
        'Ajoute une fonction Somme
        Set oPvtTotal = oPvtView.AddTotal("Somme Valeurs par ville", _
            oFieldSets("Valeurs").Fields(0), plFunctionSum)
        .DataAxis.InsertTotal oPvtTotal
       oPvtTotal.HAlignment = plHAlignCenter
        
        
        ' ---- Ajoute un champ calculé Moyenne
         Set oPvtTotal = oPvtView.AddCalculatedTotal("Moyenne", "Moyenne par ville", _
            "[Somme Valeurs par ville] / [Nombre par ville]")
        .DataAxis.InsertTotal oPvtTotal
        oPvtTotal.HAlignment = plHAlignCenter
        'application d'un format
        oPvtTotal.NumberFormat = "00.00"
        '-------------------------------
        
        
        
        ' ---- Ajoute un champ calculé 'Pourcentage'
         
        With PivotTable1.ActiveData
        'récupére le total du champ "Somme Valeurs par ville"
        GrdTotal = .Cells(.RowAxis.RowMember.TotalMember, .ColumnAxis.ColumnMember). _
            Aggregates("Somme Valeurs par ville").Value
        End With
         
        Set oPvtTotal = oPvtView.AddCalculatedTotal("Pourcentage", "Pourcentage du Total", _
            "([Somme Valeurs par ville] / " & GrdTotal & ")")
        .DataAxis.InsertTotal oPvtTotal
        oPvtTotal.HAlignment = plHAlignCenter
        oPvtTotal.NumberFormat = "0.00 %"
        
        '-------------------------------
       
      
        'Mise en forme de la barre de titre
        With .TitleBar
            'La police de caractères
            .Font.Name = "comic sans ms"
            'Le titre
            .Caption = "Le tableau croisé dynamique dans un UserForm"
            'Couleur de fond
            .BackColor = RGB(220, 180, 200)
            'Couleur du texte
            .ForeColor = RGB(0, 0, 0)
        End With
    End With
    
    
    'Masque les détails
    PivotTable1.ActiveData.HideDetails
    
End Sub




'Evènement Clic sur le TCD
'Cet exemple extrait le contenu de la cellule sélectionnée.
Private Sub PivotTable1_Click()
    Dim oPvtSel As Object
    Dim PivotAgg As PivotAggregate
    Dim strTotal As String
    Dim NomCol As String, NomLigne As String
    Dim Resultat As String
   
    'Définit la sélection
    Set oPvtSel = PivotTable1.Selection
   
    If TypeName(oPvtSel) = "PivotAggregates" Then
        Set PivotAgg = oPvtSel.Item(0)
        Resultat = PivotAgg.Value
      
        strTotal = PivotAgg.Total.Caption
        'nom colonne
        NomCol = PivotAgg.Cell.ColumnMember.Caption
        'nom élément ligne
        NomLigne = PivotAgg.Cell.RowMember.Caption
      
        'Remise en forme du résultat en fonction de la colonne cliquée
        If strTotal = "Pourcentage du Total" Then Resultat = Format(Resultat, "0.00 %")
        If strTotal = "Moyenne par ville" Then Resultat = Format(Resultat, "00.00")
        
        Label1.Caption = Resultat & vbCrLf & strTotal & vbCrLf & NomLigne & vbCrLf & NomCol
    End If

End Sub




Private Sub CommandButton1_Click()
    If CommandButton1.Caption = "Bloquer la barre d'outils" Then
        PivotTable1.Toolbar.Enabled = False
        CommandButton1.Caption = "Débloquer la barre"
        Else
        PivotTable1.Toolbar.Enabled = True
        CommandButton1.Caption = "Bloquer la barre d'outils"
    End If
End Sub




Private Sub CommandButton2_Click()
    'Affichage fenêtre gestion des champs
    PivotTable1.DisplayFieldList = True
End Sub




'Création d'un TCD et d'un filtre pour gérer les dates (par numéro de semaine)
Private Sub CommandButton3_Click()
    Dim oPvtView As OWC11.PivotView
    Dim oFieldSets As OWC11.PivotFieldSets
    Dim oPvtTotal As PivotTotal
    
    
    'affichage de la fenêtre gestion des champs
    PivotTable1.DisplayFieldList = True
    
    
    'Requète dans la feuille (Feuil2) contenant les données sources
    'Exemple pour filtrer les champs
    PivotTable1.CommandText = "SELECT [Feuil2$].[Ville],[Feuil2$].[ChampDate] FROM [Feuil2$]"

    Set oPvtView = PivotTable1.ActiveView
    Set oFieldSets = oPvtView.FieldSets
    
    With oPvtView
        'Ajoute le champ "Ville" dans la zone Row (ligne)
        .RowAxis.InsertFieldSet oFieldSets("Ville")
        .FieldSets("Ville").Fields(0).Caption = "Liste des villes"
        
        
        'Ajoute le champ "ChampDate by Week" dans la zone de filtre
        .FilterAxis.InsertFieldSet oFieldSets("ChampDate by Week")
        '.FieldSets("ChampDate by Week").Fields(0).ExcludedMembers = Array("")
        
        
        'Ajoute le champ "ChampDate" dans la zone données (Data)
        .DataAxis.InsertFieldSet oFieldSets("ChampDate")
        
        'Ajoute une fonction Nombre
        Set oPvtTotal = oPvtView.AddTotal("Nombre de dates par ville", _
            oFieldSets("ChampDate").Fields(0), plFunctionCount)
        .DataAxis.InsertTotal oPvtTotal
        'Alignement centré
        oPvtTotal.HAlignment = plHAlignCenter
        'Couleur de fond
        oPvtTotal.View.TotalBackColor = RGB(220, 180, 200)
    End With
    
    
    'Emplêche l'affichage de la fenêtre gestion des champs
    PivotTable1.DisplayFieldList = False
    'Masque les détails
    PivotTable1.ActiveData.HideDetails
End Sub




'Affiche un TCD vierge en ne prenant en compte que la ville "Ville01" dans la requête.
Private Sub CommandButton4_Click()
    PivotTable1.CommandText = "SELECT * FROM [Feuil2$] WHERE Ville='Ville01'"

    'Affiche la fenêtre de gestion des champs
    PivotTable1.DisplayFieldList = True
End Sub




' --------- Ajout et gestion d'un bouton personnalisé dans le pivotTable --------
Private Sub CommandButton5_Click()
   Dim Tb 'As OWC11.????
   Dim NouveauBouton 'As OWC11.????

   'Définit la barre d'outils du contrôle PivotTable.
   Set Tb = PivotTable1.Toolbar

   On Error Resume Next
   
   'Définit l'image qui va être associée au bouton.
   'Privilégiez des fichiers .ico pour la visibilité
   'Dans cet exemple, l'image est stockée dans le même répertoire que le classeur.
   Tb.ImageList.ListImages.Add , "NomImage", LoadPicture(ThisWorkbook.Path & "\bouton.ICO")

   If Err.Number > 0 Then
      MsgBox "L'image 'bouton.ICO' n'a pas pu être chargée: Vérifiez le chemin"
      Exit Sub
   End If
   
   On Error GoTo 0
   
   
   'Définition du nouveau bouton
   'Tb.Buttons.Count + 1 permet de placer le bouton en dernier (sur la droite)
   Set NouveauBouton = Tb.Buttons.Add(Tb.Buttons.Count + 1, "Bouton01", , , "NomImage")
 
   'Applique une info-bulle au nouveau bouton
   NouveauBouton.TooltipText = "Info-bulle du nouveau bouton."

   'Insère le bouton et ajoute une séparation après le dernier bouton existant.
   Tb.Buttons.Add Tb.Buttons.Count, , , 3
   
   PivotTable1.Refresh
   CommandButton5.Enabled = False
End Sub



'L'événement CommandExecute se déclenche après la sélection
'd'une commande de la barre de menus ou de la barre d'outils.
Private Sub PivotTable1_CommandExecute(ByVal Command As Variant, ByVal Succeeded As Boolean)
    
    'L'argument "Command" permet de contrôler la commande exécutée.
    If VarType(Command) = vbString Then
        Select Case Command
            'Déclenche la macro du bouton personnalisé.
            Case "Bouton01"
                NomMacro_01
                
            'Déclenche les macros associées aux nouveaux éléments du menu contextuel
            Case "MenuPerso01"
                NomMacro_02
        
            Case "MenuPerso02"
                NomMacro_03
        End Select
    End If
End Sub


'La macro associée au nouveau bouton
Sub NomMacro_01()
    Dim pTable As OWC11.PivotTable
    Dim pTableView As OWC11.PivotView
    Dim pFieldset As OWC11.PivotFieldSet
    Dim pField As OWC11.PivotField
    Dim Resultat As String
   
    'MsgBox "Essai utilisation bouton personnalisé."
   
    Set pTable = PivotTable1
    Set pTableView = pTable.ActiveView

    'source pour boucler sur les champs:
    'http://msdn2.microsoft.com/fr-fr/library/aa139960(office.10).aspx
    
    'Boucle sur chaque Fieldset
    For Each pFieldset In pTableView.FieldSets
        Resultat = Resultat & "The '" & pFieldset.Name & _
        "' FieldSet contains " & _
        pFieldset.Fields.Count & " field(s):" & vbCrLf

        'Boucle sur chaque Champ
        For Each pField In pFieldset.Fields
            Resultat = Resultat & vbTab & pField.Name & vbCrLf
        Next
   
        Resultat = Resultat & "----" & vbCrLf
   Next

   MsgBox Resultat, , "Liste les champs contenus dans le PivotTable."
End Sub
'-------------------------------------------------




'Gestion du menu contextuel (clic droit dans le PivotTable)
Private Sub PivotTable1_BeforeContextMenu(ByVal x As Long, ByVal y As Long, _
        ByVal Menu As OWC11.ByRef, ByVal Cancel As OWC11.ByRef)
    
    Dim MonMenu()
    Dim j As Integer
    
    '----
    'Permet de désactiver le menu contextuel
    'Cancel = True
    '----
    
    If CheckBox1.Value = False Then
        'Ajoute des nouveaux élements à la suite des éléments existants, dans
            'Le menu contextuel.
        MonMenu = Menu.Value
        j = UBound(MonMenu) + 3
    
        ReDim Preserve MonMenu(j)
    
        MonMenu(j - 2) = Empty
        'Array(Description, MotClé)
        MonMenu(j - 1) = Array("Afficher la chaîne de connexion", "MenuPerso01")
        MonMenu(j) = Array("Afficher le recordset en cours", "MenuPerso02")
        
    Else
    
        'Crée un nouveau menu contextuel personnalisé
        ReDim Preserve MonMenu(1)
        
        'Array(Description, MotClé)
        MonMenu(0) = Array("Afficher la chaîne de connexion", "MenuPerso01")
        MonMenu(1) = Array("Afficher le recordset en cours", "MenuPerso02")
        
    End If
    
    Menu.Value = MonMenu
End Sub


'La macro associée au menu contextuel
Sub NomMacro_02()
    'Affiche la chaîne de connexion
    Label1.Caption = PivotTable1.ConnectionString
End Sub


Sub NomMacro_03()
    'Affiche le paramétrage du Recordset pour l'affichage en cours.
    Label1.Caption = PivotTable1.CommandText
End Sub



V. Téléchargement

Téléchargez le classeur démo

Le fichier zip contient:
     Une version Excel2007/Excel2003 et OWC11
     Une version Excel2002 et OWC10

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

Ce document est issu de http://www.developpez.com et reste la propriété exclusive de son auteur. La copie, modification et/ou distribution par quelque moyen que ce soit est soumise à l'obtention préalable de l'autorisation de l'auteur.