[REF] Wiki Page 9 de MichelXld

MichelXld

XLDnaute Barbatruc
Les sujets abordés dans cette page
Les mises en forme conditionnelles , Les Tableaux et graphiques Croisés Dynamiques , Gérer les fichiers XML depuis Excel , Piloter Open Office depuis Excel.​




Les mises en forme conditionnelles

La mise en forme conditionnelle (MEFC) permet d'appliquer une mise en forme ( ombrage de cellule , couleur de police...) automatiquement si une condition particulière est remplie.
Il est possible de définir le format de la cellule en fonction de la valeur de celle ci. La MEFC permet ainsi de mettre en évidence certaines données mais aussi de les masquer (en appliquant par exemple la meme couleur au texte et au fond de la cellule)
Vous pouvez accéder à cette fonction dans le Menu Format , Mise en forme conditionnelle .
  • Un mémo sur la MEFC ( par Brigitte )
  • Quelques informations générales
    La mise en forme conditionnelle ne peut se référer à un autre classeur .​
    La mise en forme conditionnelle peut se référer à une autre feuille (du meme classeur) en nommant les cellules qui seront utilisées dans la formule :​
    Menu Insertion​
    Nom​
    Définir​
    Il n'est pas posssible d'appliquer une mise en forme conditionnelle en fonction d'un format de cellule .​
    Il n'est pas possible de compter les cellules coloriées à l'aide d'une MFC. Une solution de substitution consiste à recréer une formule qui utilise les mêmes critères que la mise en forme conditionnelle .​
    3 conditions maximum peuvent etre appliquées dans une mise en forme conditionnelle .​
    Il est possible d'appliquer une mise en forme conditionnelle à une plage de cellules :​
    Sélectionnez la plage sur laquelle vous souhaitez créer la mise en forme conditionnelle.​
    Appliquez la MEFC pour la cellule supérieure gauche. Excel va automatiquement adapter la mise en forme pour les autres cellules de la plage .​
    En fonction de votre projet, il est possible d'utiliser des références absolues , relatives ou mixtes dans les formules de MEFC : consultez la MichelXldPageCinq pour plus d'informations.​
  • Rechercher les cellules dotées de mises en forme conditionnelles .
    Pour rechercher toutes les cellules contenant des mises en forme conditionnelles , clique sur n'importe quelle cellule.​
    Ensuite ,​
    Menu Edition​
    Atteindre​
    Clique sur le bouton Cellules​
    Selectionne l'option Formats conditionnels​
    Selectionne l'option "Toutes" pour rechercher l'ensemble des cellules contenant des mises en forme conditionnelles​
    Remarque : Selectionne l'option "Identiques" pour rechercher uniquement les cellules contenant des mises en forme conditionnelles identiques à ceux de la cellule selectionnée​
    Clique sur OK​
  • Colorier la valeur maximale d'une plage
    =A1=MAX($A$1:$C$10)​
  • Quelle MFC utiliser pour que #N/A n'apparaisse pas dans la cellule A1
    =ESTNA(A1)​
    et applique le format couleur de police identique au fond de la cellule .​
  • Appliquer une mise en forme conditionnelle sur les cellules qui contiennent une erreur
    =ESTERREUR(A1)​
  • Appliquer une mise en forme conditionnelle si la cellule A1 est non vide
    =NON(ESTVIDE(A1))​
  • Colorier une ligne complete si la cellule A1 = 10
    Sélectionnes la ligne à colorier​
    Dans la mise en forme conditionnelle , inseres "la formule est " =$A$1=10​
    Ensuite sélectionnes le motif à appliquer​
  • Colorier les cellules en fonction d'une Date
    Lien supprimé
  • Une MEFC qui indique la date du plus proche (une démo de Monique)
    Lien supprimé
  • Appliquer une MEFC en fonction d'une date de début et une date de fin ( une démo de Gérard )
    Lien supprimé
  • Identifier les doublons dans la colonne A
    =NB.SI(A:A;A1)>1​
  • Appliquer une mise en forme conditionnelle par macro
    Exemple pour colorier la cellule A1 si elle n'est non vide​
    With Range("A1")​
    .formatConditions.Delete​
    .formatConditions.Add Type:=xlExpression, Operator:=xlGreater, Formula1:="=NON(ESTVIDE(A1))"​
    .formatConditions(1).Interior.colorIndex = 3​
    End With​
    L'argument xlExpression permet d'insérer une formule dans la MFC . Utilisez l'argument xlCellValue pour que la mise en forme conditionnelle soit basée sur la valeur d'une cellule .​
Les tableaux et graphiques croisés dynamiques

Un Tableau Croisé Dynamique (TCD) est la présentation d'une source de données sous forme de tableau. Il est dynamique car toute modification de la source entraine la mise à jour du tableau ( l'actualisation des données) .Il permet de combiner et comparer rapidement un grand nombre de données .
Dans un rapport de tableau croisé dynamique, chaque colonne ou champ de données sources devient un champ de tableau croisé dynamique qui synthétise plusieurs lignes d'informations .La présentation du tableau peut être paramétrée en personnalisant la position des champs de données , en fonction des résultats à visualiser.
Cet outil permet d'effectuer des calculs (somme, nombre ,produit , max ,ecart type ... ) et d'analyser de facon Dynamique la source de données. Il est possible de faire pivoter les lignes et colonnes pour afficher différentes synthèses des données sources.
Le TCD est un outil statistique qui repose avant tout sur une base de données bien structurée .

  • Généralités
    Evitez de placer le TCD dans la feuille contenant la base de données​
    Il ne faut pas laisser de lignes ou colonnes vides entre les données de la base​
  • Visualiser ou modifier la disposition des champs dans un TCD existant
    Clic droit dans le TCD​
    Assistant​
    bouton Disposition​
  • Renommer un champ ou un élément d'un tableau croisé dynamique
    ( Informations issues de l'aide en ligne Excel)​
    Cliquez sur le champ ou l'élément que vous voulez renommer.​
    Tapez un nouveau nom.​
    Appuyez sur ENTRÉE.​
    Remarques :​
    Si vous masquez, puis réaffichez des niveaux dans des rapports de tableau croisé dynamique basés sur des données sources OLAP, tous les champs ou éléments renommés reprennent leur nom d'origine.​
    Renommer un élément numérique le transforme en texte, qui est alors trié séparément des valeurs numériques et ne peut pas être groupé avec des éléments numériques​
  • Réaliser un TCD à partir de données dans plusieurs feuilles ( une démo de Celeda)
    Dans la première étape de l'assistant , cochez l'option "Plage de feuilles de calcul avec étiquettes."​
    Lien supprimé
  • Consolidation de données issues de plusieurs feuilles (ou des plages distinctes sur une même feuille)
    Objectif : visualiser le montant total d'un Chiffre d'affaire de trois années , par client​
    Les 3 feuilles Feuil1 (An1) ,Feuil2 (An2) , Feuil3 (An3) , doivent comporter les mêmes champs :​
    Par exemple "Clients" et "CA"​
    Dans le menu de création du TCD , Choisir "Plages de feuilles de calcul avec étiquettes"​
    A l'étape 2, choisir le nombre de champ ou laisser en automatique .​
    A l'étape 3, ajouter les plages dans leur totalité​
    La boite de dialogue affiche quatre noms de champs :​
    Le champ page1 regroupe toutes les pages des données​
    il apparaît Item 1, Item 2,.. ( possibilité de renommer les items et filtrer sur un des éléments )​
    Le champ Ligne reporte toutes les valeurs du champ de sa propre ligne​
    Le champ Colonne reporte toutes les valeurs du champ dans sa propre colonne​
    Le champ Données reporte par défaut la somme de toutes les valeurs du champ que l'on veut afficher et pour du texte, compte le nombre de valeurs.​
    Ligne correspond à Client​
    Colonne correspond au CA​
    Page1 correspond à AN1,AN2,An3​
    Données correspond au total des trois feuilles​
  • Regrouper des dates dans un TCD
    Menu "Grouper et afficher détail"​
    Grouper​
    Déterminez la période et le type de regroupement dans la boite de dialogue (jour , mois , trimestre , année ...)​
    Il est possible d'afficher le résultat par nombre de jours​
  • Ne conserver que mois-année dans un TCD
    Lien supprimé
  • Regrouper des dates et des montants triés par mois ( une démo de Celeda)
    Lien supprimé
  • Grouper des dates malgré les vides
    La principale source d'erreur dans le groupement de dates se trouve dans la colonne des dates comportant des vides.​
    Une parade pour faire quand même ce regroupement consiste à insérer dans chaque cellule, une date bidon et unique qui permettra de décocher cette date pour obtenir l'affichage d'un résultat cohérent .​
  • Les champs Calculés
    Il s'agit d'un champ créé par l'utilisateur pour l'utilisation d'une formule à partir d'un autre champ .​
    Sélectionne le TCD​
    Choisis "Formules" dans la barre de menu TCD​
    Sélectionne "Champ calculé"​
    Dans le champ "nom", saisie le nom que tu souhaites attribuer à ce nouveau champ​
    Dans la rubrique "Formules", tu paramètres ta formule en sélectionnant les données disponibles dans la rubrique "Champs" et en utilisant le bouton "Insérer un champ"​
    (Par exemple =colonne_x/colonne_total)​
    Clique sur OK pour valider​
    Le TCD va s'adapter automatiquement avec cette nouvelle donnée.​
    Une démo de Celeda​
    Lien supprimé
    Remarque :​
    Parfois il est plus simple d'ajouter une colonne supplémentaire dans la base de données et d'y déposer la formule adéquate. En effet, les champs calculés acceptent les opérateurs et des constantes mais n'acceptent pas de noms de cellules, de plages de cellules, de formules qui font appel à des matrices ou des fonctions.​
  • Conserver une mise en forme personnalisée dans un graphique croisé dynamique, après la mise à jour des données
    Excel ne permet pas de conserver les mises en forme personnalisées lors des réactualisations de données​
    Il est cependant , possible d'utiliser l'evenement "Private Sub Chart_Calculate()" pour forcer une mise en forme​
    Un exemple pour que la 1ere série du graphique soit toujours de couleur rouge :​
    Private Sub Chart_Calculate()​
    activeChart.seriesCollection(1).Interior.colorIndex = 3​
    End Sub​
  • Masquer les sous totaux du champ "Noms" dans un TCD nommé "MonTCD"
    Sub cacheSousTotal()​
    activeSheet.pivotTables("monTCD").pivotFields("Noms").Subtotals(1) = False​
    End Sub​
  • Créer un TCD par macro
    Les données sont dans la feuille "BD" et les étiquettes de colonne en A1.Le TCD est construit dans la cellule A1 de la Feuil1​
    Sub CreerTCD()​
    activeWorkbook.pivotTableWizard xlDatabase, ?[BD!A1].currentRegion _​
    .Address(, , xlR1C1, True), "Feuil1!r1c1", "Mon tableau"​
    End Sub​
  • Détruire les anciennes étiquettes dans un TCD
    Suite à des modifications des étiquettes dans la base de données, le TCD ne s'actualise pas et les étiquettes de colonne persistent dans les filtres des champs.​
    Pour les supprimer , utilisez cette macro de Debra Dalgleish ,à placer dans un module de la feuille​
    Sub deleteOldItemsWB()​
    'gets rid of unused items in pivotTable​
    'Debra Dalgleish - based on MSKB (202232)​
    Dim ws As Worksheet​
    Dim pt As pivotTable​
    Dim pf As pivotField​
    Dim pi As pivotItem​
    Dim i As Integer​
    On Error Resume Next​
    For Each ws In activeWorkbook.Worksheets​
    For Each pt In ws.pivotTables​
    pt.refreshTable​
    For Each pf In pt.pivotFields​
    For Each pi In pf.pivotItems​
    If pi.recordCount = 0 And _​
    Not pi.isCalculated Then​
    pi.Delete​
    End If​
    Next​
    Next​
    Next​
    Next​
    End Sub​
    Et un autre solution , à partir d'excel 2002 uniquement :​
    Sub deleteMissingItems2002()​
    'prevents unused items in XL 2002 pivotTable​
    Dim pt As pivotTable​
    Set pt = activeSheet.pivotTables.Item(1)​
    pt.pivotCache.missingItemsLimit = xlMissingItemsNone​
    End Sub​
  • Les Filtres dans le TCD
    Peut-on supprimer des lignes ou des colonnes dans un TCD ?​
    Non, mais en se servant des filtres on décoche dans la liste déroulante les éléménts à ne pas afficher​
  • Actualiser par macro tous les TCDs du classeur
    Sub ActualiserTCD()​
    Dim Tcd As pivotTable​
    Dim Feuille As Worksheet​
    Application.screenUpdating = False​
    For Each Feuille In Worksheets​
    For Each Tcd In Feuille.pivotTables​
    Tcd.refreshTable​
    Next​
    Next​
    Application.screenUpdating = True​
    End Sub​
  • Nommer une base de données ( Plage de cellules) dynamiquement
    Menu Insertion​
    Nom​
    Définir​
    Dans le champ "Noms dans le classeur" , nommez la plage de cellules​
    Dans le champ "Fait référence à:" , indiquez la formule :​
    Clic sur OK pour valider​
    =DECALER($A$1;;;NBVAL($A$1:$A$2000);NBVAL($A$1:$X$1))​
    IMPORTANT : Il doit impérativement y avoir des étiquettes dans toutes les colonnes​
    Cet exemple limite les colonnes jusqu'à X et les lignes jusqu'à 2000 .​
    Pour utiliser la meme formule sans limite de lignes et de colonnes :​
    =DECALER($A$1;;;NBVAL(Feuil2!$A:$A);NBVAL(Feuil2!$1:$1))​
  • Créer un TCD par macro, à partir d'une plage de cellules variable
  • Ne pas afficher les zéro tout en conservant l'affichage de l'élement dans le TCD
    Sélectionnez la plage des cellules à l'intérieur du TCD (dans colonne TOTAL)​
    Clic droit sur le bouton du tableau croisé dynamique​
    Format de cellule​
    Personnalisé​
    Saisissez le format 0,0;-0,0;""​
  • Afficher à la fois le total et le pourcentage dans un TCD
    Lors de la création du TCD , à l'étape 3, il est possible d'ajouter une deuxième fois un champ dans la zone de données , et de le demander en pourcentage​
    Glissez une 2eme fois le champ dans la zone "données" (CA dans l'exemple ci-dessous)​
    Ensuite double cliquez sur le champ "Somme de CA2"​
    Cliquez sur le bouton "Options" dans la boite de dialogue "Champ pivotTable"​
    Dans le champ "Afficher les données:" , selectionnez "% du total"​
    Il est aussi possible de changer le nom de ce champ à cette étape​
    Cliquez sur OK pour valider​
  • Extraire la totalité des informations concernant les éléments du TCD
    En double cliquant sur le total d'un élément du TCD, on obtient dans un nouvel onglet la totalité des informations concernant cet élément​
  • Les Tris dans un TCD
    Pour définir un ordre d'affichage , il suffit de double cliquer sur l'entête d'un champ​
    Dans la boîte de dialogue "Champ dynamique", cliquez sur le bouton "Avancé"​
    Dans nouvelle boite de dialogue qui s'affiche ("Options avancées de champ dynamique") sélectionnez une des options de tri :​
    Manuel , Croissant ou Décroissant​
    Ensuite précisez si le tri du champ doit etre effectué par rapport à lui-même ou par rapport à un autre champ ( dans la liste déroulante "Sur le champ")​
  • Afficher le Top 10 d'une colonne
    Double cliquez sur l'entête d'un champ​
    Dans la boîte de dialogue "Champ dynamique", cliquez sur le bouton "Avancé"​
    Sélectionnez l'option de Top 10 dans la boite de dialogue "Options avancées de champ dynamique"​
    (Voir la partie droite de l'image précédente)​
  • Regrouper des éléments du TCD
    Sélectionnez les lignes à grouper dans le tableau croisé dynamique​
    Clic droit​
    Choisissez l'option "Grouper et afficher le détail"​
    Grouper​
    Une nouvelle colonne de regroupement est alors créée dans le TCD​
  • Compter le nombre de lignes dans le TCD
    Sub compterNombreLignesTCD()​
    Dim Pvt As pivotTable​
    Set Pvt = Worksheets("Feuil1").pivotTables("Tableau croisé dynamique1")​
    'tableRange1: plage contenant l'intégralité du rapport de tableau croisé dynamique, à l'exclusion des champs de page​
    msgBox Pvt.tableRange1.Rows.Count​
    'tableRange2 : plage contenant l'intégralité du rapport de tableau croisé dynamique,y compris les champs de page​
    msgBox Pvt.tableRange2.Rows.Count​
    End Sub​
  • Afficher la boite de dialogue de mise à jour du TCD et créer une copie du résultat dans une nouvelle feuille
    Sub updatePivot()​
    ' John Lacher​
    Application.screenUpdating = False​
    With Worksheets("TCD")​
    .pivotTables(1).tableRange2.Select​
    Application.Dialogs(xlDialogPivotTableWizard).Show​
    .pivotTables(1).tableRange2.Select​
    End With​
    With Worksheets("Feuil1")​
    Selection.Copy​
    .Activate​
    .Range("A4").pasteSpecial Paste:=xlValues​
    .usedRange.autoFormat xlColor2​
    End With​
    End Sub​
  • Actualiser 2 TCD en meme temps ( des solutions proposées par Dan )
    Vous pouvez créer le deuxième TCD à partir du premier - L'assistant propose cette option au début. Quand vous actualisez le premier, le deuxième s'actualisera également.​
    Vous pouvez aussi utiliser cette macro ( dans cet exemple , les 2 TCD sont dans la meme feuille )​
    with Activesheet​
    .pivotTables("Tableau croisé dynamique1").refreshTable​
    .pivotTables("Tableau croisé dynamique2").refreshTable​
    End with​
  • Boucler sur tous les TCD de la feuille active et afficher leur nom
    Dim Pvt As pivotTable​
    For Each Pvt In activeSheet.pivotTables​
    msgBox Pvt.Name​
    Next​
  • Mettre à jour un TCD dans une feuille protégée (un exemple proposé par Dan)
  • Comment modifier des champs dans un TCD , sans refaire le TCD : une démo de Myta
    Lien supprimé
  • Vérifier si un Graphique est issu d'un TCD ( renvoie Vrai ou Faux )
    msgBox Sheets("Graph1").hasPivotFields​
  • Vérifier si une cellule fait partie d'un TCD
    Dim Pvt As pivotTable​
    On Error Resume Next​
    Set Pvt = Range("E5").pivotTable​
    If Err.Number = 0 Then msgBox "La cellule fait partie du TCD : " & Pvt.Name​
  • Réafficher tous les éléments du champ "Pays"
    Dim x As Integer​
    With Worksheets("Feuil4").pivotTables("Tableau croisé dynamique1")​
    For x = 1 To .pivotFields("Pays").pivotItems.Count​
    .pivotFields("Pays").pivotItems.Item(x).Visible = True​
    Next​
    End With​
  • Appliquer la fonction Somme au champ "Clients" du premier TCD de la feuille active.
    activeSheet.pivotTables(1).pivotFields("Nombre de Clients").Function = xlSum​
    Les autres constantes disponibles pour la propriété Function :​
    xlAverage - Moyenne​
    xlCountNums - Nb​
    xlMin - Min​
    xlStDev - Ecartype​
    xlSum - Somme​
    xlVar - Var​
    xlCount - Nombre​
    xlMax - Max​
    xlProduct - Produit​
    xlStDevP - Ecartypep​
    xlUnknown -​
    xlVarP - Varp​
  • Modifier la mise en forme des cellules contenant les résultats, dans le premier TCD de la feuille active.
    With activeSheet.pivotTables(1).dataBodyRange​
    .pivotField.numberFormat = "# ##0.00"​
    .Interior.colorIndex = 4​
    End With​
  • Déclencher les liens hypertextes contenus dans les champs d'un TCD .
    Private Sub Worksheet_selectionChange(byVal Target As Range)​
    Dim Pvt As pivotTable​
    On Error Resume Next​
    Set Pvt = Target.pivotTable​
    If Err.Number = 0 And Target.Hyperlinks.Count = 1 Then Target.Hyperlinks(1).Follow​
    End Sub​
  • Masquer tous les champs dans le 1er TCD de la feuille active.
    Dim Pvt As pivotTable​
    Dim Pvf As pivotField​
    Set Pvt = activeSheet.pivotTables(1)​
    For Each Pvf In Pvt.pivotFields​
    Pvf.Orientation = xlHidden​
    Next Pvf​
  • Afficher le détail de tous les éléments d'un champ .
    Dim Pvt As pivotItem​
    For Each Pvt In activeSheet.pivotTables(1).pivotFields("Cible").pivotItems​
    Pvt.showDetail = True​
    Next​
  • Récupérer les éléments d'un champ
    Dim Pvt As pivotItem​
    For Each Pvt In activeSheet.pivotTables(1).pivotFields("Client").pivotItems​
    Debug.Print Pvt.Caption​
    Next​
  • Récupérer une valeur dans un TCD
    Exemple : Afficher le total des revenus provenant des ventes de pommes en janvier (Champ de données = Revenu, Produit = Pommes, Mois = Janvier)​
    msgbox activeSheet.pivotTables(1).getData("'Somme de Revenus' Pommes Janvier")​
  • Afficher le résultat d'un TCD dans une listbox
    Private Sub userForm_Initialize()​
    Dim Cell As Range​
    Dim Pvt As pivotTable​
    Dim Pvf As pivotField​
    listBox1.columnCount = 2​
    listBox1.columnWidths = "70;40"​
    Set Pvt = Sheets("Feuil4").pivotTables(1)​
    Set Pvf = Pvt.pivotFields(1)​
    For Each Cell In Pvf.dataRange​
    listBox1.addItem Cell​
    listBox1.List(listBox1.listCount - 1, 1) = _​
    Sheets("Feuil4").pivotTables(1).getData("'" & Pvt.dataFields(1).Name & "' " & Pvf.Name & " " & Cell)​
    Next Cell​
    End Sub​
  • Utiliser la fonction LIREDONNEESTABCROISDYNAMIQUE :Une démo de Dan
    Lien supprimé
  • TCD_EXTRACTOR : Extraire les résultats d'un TCD
    Cet exemple permet d'extraire le résultat d'un champ spécifique dans un tableau croisé dynamique .​
    Un userForm liste les TCD contenus dans tous les classeurs ouverts​
    Tous les paramètres du TCD sélectionné sont listés dans la boite de dialogue . L'utilisateur paramètre ses données puis lance la requète Afin de visualiser le résultat .​
    Option pour les utilisateurs de macros :​
    La procédure "Fabrique" un exemple de macro correspondant à la requete effectuée .​
    Il ne reste plus qu'a faire un copier/coller du Textbox vers un module du classeur contenant le TCD cible .​
    Une option permet de créer un module dans le classeur contenant le TCD , puis d'y ajouter la macro "Fabriquée"​
    Option pour les utilisateurs de formules :​
    La procédure "Fabrique" un exemple de fonction LIREDONNEESTABCROISDYNAMIQUE(tableau_croisé_dyn;nom) correspondant à la requete effectuée .​
    Il ne reste plus qu'a faire un copier/coller du Textbox vers la feuille contenant le TCD .​
    Lien supprimé
    Lien supprimé
  • Trier les données d'un champ par ordre croissant
    Clic droit sur le champ​
    Option "Paramètres de Champ"​
    Bouton "Avancé"​
    Sélectionne l'option "Croissant" dans la zone "Tri automatique"​
    Clique sur OK pour valider (pour les 2 boites de dialogue)​
  • Modifier la source d'un TCD
    Dim objCellule As Range​
    Dim Pvt As pivotTable​
    Set objCellule = thisWorkbook.Sheets("Feuil1").Range("A1:B20")​
    Set Pvt = Sheets("Feuil4").pivotTables("Tableau croisé dynamique1")​
    Pvt.pivotTableWizard sourceType:=xlDatabase, _​
    sourceData:=objCellule.Address(, , xlR1C1, True)​
Gérer les fichiers XML depuis Excel

Les fichiers XML (Extensible Markup Language) servent à stocker des données .Celles si sont écrites entre balises ou sous forme d'attributs. L'ensemble est rédigé sous forme d'arborescence.
XML permet de stocker tous types d'informations et surtout de séparer les données et leur présentation .
XML permet un échange entre des systèmes informatiques ,et un transfert des données ( le fond ) et de leurs structures( la forme)
Ainsi, une balise destinée à accueillir une date sera définie comme telle dans le schéma et par la suite sera en mesure de délivrer sa valeur à un programme sans soucis de conversion d'une chaîne de caractères en date.
XML est un dérivé du HTML. Le HTML est le langage de balisage des pages Web. Il décrit seulement l’apparence du contenu :Le HTML ne peut pas identifier que ces données désignent par exemple un titre ou une date..., contrairement au XML dont les balises se rapportent à la signification et à la structure.
  • Comment utiliser un fichier XML au sein d'Excel 2003
  • Créer un fichier XML
    Remarque : si un fichier XML portant le meme nom existe dans le répertoire , celui-ci sera écrasé​
    Sub creerFichierXML()​
    'Source : http://www.c2i.fr/code.asp?IDCode=21
    Dim objDOM As DOMDocument​
    Dim XNodeRoot As IXMLDOMElement, Xnode As IXMLDOMElement​
    Dim oPi As IXMLDOMProcessingInstruction​
    Set objDOM = New DOMDocument​
    objDOM.resolveExternals = True​
    Set oPi = objDOM.createProcessingInstruction("xml", "version='1.0' encoding='ISO-8859-1'")​
    Set oPi = objDOM.insertBefore(oPi, objDOM.childNodes.Item(0))​
    Set XNodeRoot = objDOM.createElement("noeudRacine")​
    objDOM.appendChild XNodeRoot​
    Set Xnode = objDOM.createElement("Balise1")​
    Xnode.setAttribute "At1", "Attribut1"​
    Xnode.Text = "la Texte 1"​
    XNodeRoot.appendChild Xnode​
    Set Xnode = objDOM.createElement("Balise1")​
    Xnode.setAttribute "At2", "Attribut2"​
    Xnode.Text = "Le texte 2"​
    XNodeRoot.appendChild Xnode​
    Set Xnode = objDOM.createElement("Balise1")​
    Xnode.setAttribute "At3", "Attribut3"​
    Xnode.Text = "Le texte 3"​
    XNodeRoot.appendChild Xnode​
    objDOM.Save thisWorkbook.Path & "\leFichier.xml"​
    Set XNodeRoot = Nothing​
    Set Xnode = Nothing​
    Set objDOM = Nothing​
    End Sub​
  • Insérer un commentaire dans un fichier XML
    Dim Cmt As IXMLDOMComment​
    Set Cmt = objDOM.createComment("mon commentaire")​
    Set Cmt = objDOM.insertBefore(Cmt, objDOM.childNodes.Item(0))​
  • Ajouter un élément dans le fichier XML
    Sub ajouterElementFichierXML()​
    Dim objElem As IXMLDOMElement​
    Dim xmlDoc As DOMDocument​
    Set xmlDoc = New DOMDocument​
    xmlDoc.async = False​
    xmlDoc.Load (thisWorkbook.Path & "\leDocument.xml")​
    Set objElem = xmlDoc.createElement("Balise1")​
    objElem.setAttribute "At4", "Attribut4"​
    objElem.Text = "le Texte 4"​
    xmlDoc.documentElement.appendChild objElem​
    xmlDoc.Save thisWorkbook.Path & "\leDocument.xml"​
    End Sub​
  • Attendre que le document soit totalement chargé en mémoire avant de travailler dessus
    xmlDoc.async = False​
  • Lire le contenu d'un fichier XML
    Option Explicit​
    Dim j As Long​
    Sub Test()​
    Dim xmlDoc As DOMDocument​
    Dim root As IXMLDOMElement​
    Set xmlDoc = New DOMDocument​
    xmlDoc.async = False​
    xmlDoc.Load thisWorkbook.Path & "\leFichier.xml"​
    Set root = xmlDoc.documentElement​
    Cells(1, 1) = root.baseName​
    j = 1​
    browseChildNodes root​
    j = 0​
    End Sub​
    Private Sub browseChildNodes(root_node As IXMLDOMNode)​
    Dim i As Long​
    For i = 0 To root_node.childNodes.Length - 1​
    If root_node.childNodes.Item(i).nodeType <> 3 Then​
    j = j + 1​
    Cells(j, 1) = root_node.childNodes.Item(i).baseName & "/" & root_node.childNodes.Item(i).Text​
    End If​
    browseChildNodes root_node.childNodes(i)​
    Next​
    End Sub​
  • Modifier un fichier XML par macro
    Sub modifierFichierXML()​
    Dim xmlDoc As DOMDocument​
    Dim Rt As IXMLDOMElement​
    Set xmlDoc = New DOMDocument​
    xmlDoc.async = False​
    xmlDoc.Load thisWorkbook.Path & "\leDocument.xml"​
    Set Rt = xmlDoc.documentElement​
    parseNodes Rt​
    xmlDoc.Save thisWorkbook.Path & "\leDocument.xml"​
    End Sub​
    Private Sub parseNodes(Rt_node As IXMLDOMNode)​
    Dim i As Long​
    For i = 0 To Rt_node.childNodes.Length - 1​
    If Rt_node.childNodes.Item(i).Text = "Le texte 2" Then _​
    Rt_node.childNodes.Item(i).Text = "la nouvelle donnée"​
    parseNodes Rt_node.childNodes(i)​
    Next​
    End Sub​
  • Remplacer un nœud dans un fichier XML .
    Sub remplacerNoeud()​
    Dim xmlDoc As New DOMDocument​
    Dim nodeRoot As IXMLDOMNode, Ancien As IXMLDOMNode, Nouveau As IXMLDOMNode​
    Dim nodeTemp As IXMLDOMNode, Anciens As IXMLDOMNodeList​
    Dim i As Integer​
    xmlDoc.async = False​
    With xmlDoc​
    .Load "C:\monFichier.xml"​
    Set nodeRoot = .documentElement​
    Set Anciens = nodeRoot.selectNodes("//noeud_Actuel") 'remplacement du noeud nommé "noeud_Actuel"​
    For Each Ancien In Anciens​
    Set Nouveau = .createElement("mise_A_Jour") 'le noeud de remplacement sera nommé "mise_A_Jour"​
    For Each nodeTemp In Ancien.Attributes 'Copie des attributs​
    Nouveau.Attributes.setNamedItem (nodeTemp.cloneNode(True))​
    Next​
    For Each nodeTemp In Ancien.childNodes 'Copie des attributs et mise à jour données​
    i = i + 1​
    nodeTemp.Text = "nouvelle donnée " & i​
    Nouveau.appendChild nodeTemp​
    Next​
    Ancien.parentNode.replaceChild Nouveau, Ancien​
    Next​
    .Save "C:\monFichier.xml"​
    End With​
    End Sub​
  • Suivre les statuts de chargement et d'analyse d'un fichier XML : une démo de Gizmo
    Lien supprimé
  • Exporter une table Access au format XML
    Sub exportTableAccess_XML()​
    Dim Cn As ADODB.Connection​
    Dim Rs As ADODB.Recordset​
    Dim Fichier As String​
    Fichier = "C:\Documents and Settings\michel\dossier\general\excel\dataBase.mdb"​
    Set Cn = New ADODB.Connection​
    Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _​
    Fichier & ";"​
    Set Rs = New ADODB.Recordset​
    With Rs​
    .activeConnection = Cn​
    .Open "SELECT * FROM Table1", , adOpenStatic, adLockOptimistic, adCmdText​
    End With​
    Rs.Save "C:\exportTableTemporaire.xml", adPersistXML​
    'autre possibilité​
    'Rs.Save "C:\tableTemporaire.xls", adPersistXML​
    Cn.Close​
    End Sub​
  • Réimporter le fichier XML dans la table Access
    Sub reimportXML_versBaseAccess()​
    Dim Rst As ADODB.Recordset​
    Dim Cn As ADODB.Connection​
    Dim Fichier As String​
    Fichier = "C:\Documents and Settings\michel\dossier\general\excel\dataBase.mdb"​
    Set Rst = New ADODB.Recordset​
    Rst.Open "C:\tableTemporaire.xml", , adOpenStatic, adLockBatchOptimistic, adCmdFile​
    'Rst.Open "C:\tableTemporaire.xls", , adOpenStatic, adLockBatchOptimistic, adCmdFile​
    Set Cn = New ADODB.Connection​
    With Cn​
    .Provider = "Microsoft.JET.OLEDB.4.0"​
    .Open Fichier​
    End With​
    Set Rst.activeConnection = Cn​
    Rst.updateBatch​
    Rst.Close​
    End Sub​
  • Les recommandations pour l'utilisation du langage XML
  • Gérer les erreurs liées à la manipulation des fichiers XML
    Cet exemple affiche un message lorsque le fichier XML ne peut pas etre chargé​
    Sub gestionnaireErreurs_MSXML()​
    Dim xmlDoc As MSXML2.DOMDocument​
    Dim parseErr As MSXML2.IXMLDOMParseError​
    Dim messageErreur As String​
    Set xmlDoc = New DOMDocument​
    xmlDoc.async = False​
    xmlDoc.Load thisWorkbook.Path & "\test.xml"​
    Set parseErr = xmlDoc.parseError​
    With parseErr​
    messageErreur = "Le fichier " & .URL & " ne peut pas etre chargé " & _​
    vbCrLf & .reason & "Code erreur : " & Hex(.errorCode)​
    End With​
    msgBox messageErreur​
    End Sub​
  • Boucler sur les différents attributs(nom, type, date, taille) d'un fichier XML
    Le format du fichier XML à parcourir :
    <FICHIERS>​
    <FICHIER nom="fichier.bmp" type="bmp" date="05/12/05 11:12" taille="12457" />​
    <FICHIER nom="test124.txt" type="txt" date="15/02/06 19:42" taille="454" />​
    <FICHIER nom="nouveau.ico" type="ico" date="14/01/06 00:54" taille="74714" />​
    <FICHIER nom="presentation.swf" type="swf" date="27/09/05 17:18" taille="5467207" />​
    </FICHIERS>​
    Sub bouclerSurAttributs_V01()​
    Dim xmlDoc As DOMDocument​
    Dim xmlElem As IXMLDOMElement​
    Set xmlDoc = New DOMDocument​
    xmlDoc.async = False​
    xmlDoc.Load "C:\Documents and Settings\michel\dossier\general\excel\leFichier.xml"​
    For Each xmlElem In xmlDoc.getElementsByTagName("FICHIER")​
    msgBox xmlElem.getAttribute("nom")​
    msgBox xmlElem.getAttribute("type")​
    'msgBox xmlElem.getAttribute("date")​
    'msgBox xmlElem.getAttribute("taille")​
    Next​
    End Sub​
    Une autre possibilité​
    Sub bouclerSurAttributs_V02()​
    Dim xmlDoc As DOMDocument​
    Dim i As Integer​
    Set xmlDoc = New DOMDocument​
    xmlDoc.async = False​
    xmlDoc.Load "C:\Documents and Settings\michel\dossier\general\excel\leFichier.xml"​
    For i = 0 To xmlDoc.getElementsByTagName("FICHIER").Length - 1​
    msgBox xmlDoc.getElementsByTagName("FICHIER").Item(i).Attributes.getNamedItem("nom").Text & " / " & _​
    xmlDoc.getElementsByTagName("FICHIER").Item(i).Attributes.getNamedItem("taille").Text​
    Next i​
    End Sub​
  • Extraire les données d'un fichier XML en appliquant un tri croissant sur un des attributs
    Voir le message du 15/02/2006 14:25:36​
  • Créer un fichier XML à partir d'un tableau Excel (La plage de cellules A1:F20)
    Option Explicit​
    Option Base 1​
    Sub tableauExcel_XML_V01()​
    Dim xmlDoc As MSXML2.DOMDocument​
    Dim xmLstring As String, Fichier As String, strQuote As String​
    Dim Lig As Integer, Col As Integer​
    Dim Attribut As Variant​
    strQuote = """"​
    Set xmlDoc = createObject("Microsoft.XMLDOM")​
    xmLstring = "<?xml version=""1.0"" encoding=""ISO-8859-1"" ?> "​
    xmLstring = xmLstring & "<FORM Name=" & strQuote _​
    & "Test Tableau Excel vers xml" & strQuote & "> "​
    Attribut = Array("Id", "Nom", "Date", "Type", "Url", "Adresse")​
    For Lig = 1 To 20 ' le tableau Excel contient 20 lignes (1 à 20)​
    xmLstring = xmLstring & "<CATEGORIE "​
    For Col = 1 To 6 'le Tableau Excel contient 6 colonnes (A à F)​
    xmLstring = xmLstring & Attribut(Col) & "=" & strQuote & Cells(Lig, Col) & strQuote & " "​
    Next​
    xmLstring = xmLstring & "></CATEGORIE>"​
    Next Lig​
    xmLstring = xmLstring & "</FORM>"​
    xmlDoc.loadXML xmLstring 'charger le fichier pour pouvoir l'enregistrer​
    Fichier = "C:\testExcel_XML.xml"​
    xmlDoc.Save (Fichier) 'Sauvegarder le fichier xml​
    End Sub​
  • Afficher le contenu d'un fichier XML dans la fenêtre d'éxécution VBE :
    Sub contenuFichierXML_dansFenetreExecution()​
    Dim xmlDoc As MSXML2.DOMDocument​
    Dim XMLRoot As Object​
    Set xmlDoc = New DOMDocument​
    xmlDoc.async = False​
    xmlDoc.Load thisWorkbook.Path & "\base xml\monFichier.xml"​
    Set XMLRoot = xmlDoc.documentElement​
    Debug.Print XMLRoot.XML​
    Set XMLRoot = Nothing​
    End Sub​
  • Afficher le contenu d'un fichier XML dans un Treeview
    Option Explicit​
    Dim oDoc As MSXML2.DOMDocument​
    Private Sub commandButton1_Click()​
    'La source :​
    'Auteur : Bnaya Eshet​
    Set oDoc = New DOMDocument​
    oDoc.async = False​
    oDoc.Load "C:\Documents and Settings\michel\dossier\general\excel\gerer fichiers XML\base xml\leDocument.xml"​
    treeView1.Nodes.Clear​
    addNode oDoc.documentElement​
    End Sub​
    Private Function addNode(byRef oElem As MSXML2.IXMLDOMNode, _​
    Optional byRef oTreeNode As MSComctlLib.Node)​
    Dim oNewNode As MSComctlLib.Node​
    Dim oNodeList As MSXML2.IXMLDOMNodeList​
    Dim i As Long​
    If oTreeNode Is Nothing Then​
    Set oNewNode = treeView1.Nodes.Add 'Creation du noeud racine​
    oNewNode.Expanded = True​
    Else​
    Set oNewNode = treeView1.Nodes.Add(oTreeNode, tvwChild) 'Ajout d'un noeud enfant​
    oNewNode.Expanded = True​
    End If​
    Select Case oElem.nodeType​
    Case MSXML2.NODE_ELEMENT 'type Element​
    oNewNode.Text = oElem.nodeName & " (" & getAttributes(oElem) & ")"​
    Set oNewNode.Tag = oElem​
    Case MSXML2.NODE_TEXT​
    oNewNode.Text = "Text: " & oElem.nodeValue​
    Set oNewNode.Tag = oElem​
    Case MSXML2.NODE_CDATA_SECTION​
    oNewNode.Text = "CDATA: " & oElem.nodeValue​
    Set oNewNode.Tag = oElem​
    Case Else​
    oNewNode.Text = oElem.nodeType & ": " & oElem.nodeName​
    Set oNewNode.Tag = oElem​
    End Select​
    Set oNodeList = oElem.childNodes 'boucle récursive pour ajouter tous les noeuds enfants​
    For i = 0 To oNodeList.Length - 1​
    addNode oNodeList.Item(i), oNewNode​
    Next i​
    End Function​
    Private Function getAttributes(byRef oElm As MSXML2.IXMLDOMNode) As String​
    Dim sAttr As String​
    Dim i As Long​
    sAttr = ""​
    For i = 0 To oElm.Attributes.Length - 1 'boucle sur tous les attributs​
    sAttr = sAttr & oElm.Attributes.Item(i).nodeName & "='" & oElm.Attributes.Item(i).nodeValue & "' "​
    Next i​
    getAttributes = sAttr​
    End Function​
  • Supprimer le noeud nommé "status" dans un fichier xml
    Sub supprimerNoeudConditionnel()​
    Dim xmlDoc As DOMDocument​
    Dim Nd As IXMLDOMNode​
    Set xmlDoc = New DOMDocument​
    xmlDoc.async = False​
    xmlDoc.Load thisWorkbook.Path & "\base xml\monFichier.xml"​
    For Each Nd In xmlDoc.documentElement.childNodes​
    'Debug.Print Nd.baseName​
    If Nd.baseName = "status" Then xmlDoc.documentElement.removeChild Nd​
    Next Nd​
    xmlDoc.Save thisWorkbook.Path & "\base xml\monFichier.xml"​
    End Sub​
  • Sauvegarder une feuille Excel au format XML
    Feuil1.saveAs Filename:="C:\maSauvegarde.xml", fileFormat:=xlXMLSpreadsheet​
  • Utiliser un fichier XML pour sauvegarder (et recharger) les paramètres d'objets créés dynamiquement dans un Userform
    Cet exemple permet :​
    D'ajouter et manipuler plusieurs Labels dynamiquement dans un Frame​
    Sauvegarder les parametres des labels dans un fichier XML , afin de pouvoir y revenir plus tard sans perdre la mise en forme (Nom , Position , Dimension , Texte, Couleur de texte, Taille des caracteres) .Le fichier xml est enregistré dans le meme répertoire que ce classeur .​
    Charger dans l'USF les parametres contenus dans le fichier xml .​
    Lien supprimé
Piloter Open Office à partir d'Excel

Ce lien n'existe plus


  • Piloter un publipostage Writer depuis Excel
    Ce lien n'existe plus
  • Récupérer les macros dans un classeur corrompu
Si vous constatez des erreurs dans la page n'hesitez pas à m'en faire part .
Toutes vos idees sont les bienvenues .
Michel , Mise à jour le 15 Septembre 2006
 
Dernière modification par un modérateur:

Discussions similaires

Réponses
3
Affichages
72