[MichelXld]

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.

Lien vers la wiki page 1 :Les feuilles , Les graphiques , Les images , Les propriétés des classeurs , Les sauvegardes , Les formes automatiques , Aleatoire , Les barres d'outils et les barres de menus , Les boites de dialogues intégrées , Les classeurs .

Lien vers la wiki page 2 : Les userforms : Les Checkbox , Les Labels , Les combobox , Les Commandbutton , Les Listbox ,Les Multipages , Les Frames , Les Textbox , Les imagesList , Les Treeview , Les Listview , Les Images , Les Webbrowser , Les calendriers , Les progressbar , Les Spreadsheet , Les Chartspaces , Les commonDialog , Les MSFlexGrid.

Lien vers la wiki page 3 :Piloter d'autres applications depuis Excel , Piloter ( Word , Outlook , Power Point ) , Les fichiers texte

Lien vers la wiki page 4 : Les fonctions mathématiques et trigonométriques , Les impressions , Les temporisations , Les fonctions , Les evenements , Excel , Les cellules , Copier & Coller , Les dates et les calendriers , Les spécificités Macintosh, Générer des fichiers Flash , Open Office

Lien vers la wiki page 5 : Les formules Excel , Les audits de formules , Les répertoires et les fichiers .

Lien vers la wiki page 6 : Les doublons , Les tris et les filtres , Les variables , Piloter les fichiers fermés (Excel , Access ,les fichiers DBF) .

Lien vers la wiki page 7 : Les commentaires , La gestion des erreurs , L'aide en ligne Excel , Les recherches dans un classeur, Les tableaux , Les pages html et internet , Windows Media Player , Le PC et le systême d'exploitation ,Piloter Flash , les types de boucles.

Lien vers la wiki page 8 : Piloter MSN Messenger et Windows Messenger , Les objets dans le feuille , Les liens hypertextes , Les formats , Visual basic editor , Les chaines de caractères , Les modules de classe.

Lien vers la wiki page 9 : Les mises en forme conditionnelles , Les Tableaux et graphiques Croisés Dynamiques , Gérer les fichiers XML depuis Excel , Piloter Open Office depuis Excel.

Lien vers la wiki page 10 : Le Publipostage Word / Excel.

Lien vers la wiki page 11 : Utiliser la librairie Windows Image Acquisition Automation Library v2.0 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 )

  Le lien vers la zone de téléchargement du forum

• 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

  Le lien sur le forum XLD

• Une MEFC qui indique la date du plus proche (une démo de Monique)

  Le lien sur le forum XLD

  Le fichier zippé

• Appliquer une MEFC en fonction d'une date de début et une date de fin ( une démo de Gérard )

  Le lien sur le forum XLD

  Le fichier zippé

• 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."

  Le fichier zippé

• 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

  Le lien sur le forum XLD

  Le fichier zippé

• Regrouper des dates et des montants triés par mois ( une démo de Celeda)

  Le lien sur le forum XLD

  Le fichier zippé

• 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

  Le lien sur le forum XLD

  Le fichier zippé

  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.colorInde x = 3

  End Sub

• Masquer les sous totaux du champ "Noms" dans un TCD nommé "MonTCD"

  Sub cacheSousTotal()

  activeSheet.pivotTables("monTCD").pivotFields("Nom s").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

  Le lien sur le forum XLD

• 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

  Le lien sur le forum XLD

• 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

  'http://www.lacher.com/examples/lacher36.htm

  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)

  Le lien sur le forum XLD

• Comment modifier des champs dans un TCD , sans refaire le TCD : une démo de Myta

  Le lien sur le forum XLD

  Le fichier zippé

• 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").pi votItems

  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").p ivotItems

  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

  Le lien dans la zone de téléchargement

• 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;no m) correspondant à la requete effectuée .

  Il ne reste plus qu'a faire un copier/coller du Textbox vers la feuille contenant le TCD .

  Le lien sur le forum XLD

  Le fichier zippé

  Le fichier zippé, version xla

• 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

  Un exemple sur le site Microsoft

• 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)

  'Source : http://vb.developpez.com/faq/?page=Routines

  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

  Le lien sur le forum XLD

  Le fichier zippé

• 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

  Le lien Internet

• 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.xm l"

  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.xm l"

  For i = 0 To xmlDoc.getElementsByTagName("FICHIER").Length - 1

  msgBox xmlDoc.getElementsByTagName("FICHIER").Item(i).Att ributes.getNamedItem("nom").Text & " / " & _

  xmlDoc.getElementsByTagName("FICHIER").Item(i).Att ributes.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

  Le lien Internet

• 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 :

  'http://www.vbcode.com/Asp/showzip.asp?ZipFile=http://www.vbcode.com%2Fcode%2FDOMTree.zip&theID=2683

  '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 .

  Le lien sur le forum XLD

  Le fichier zippé

Piloter Open Office à partir d'Excel

Afficher la page

• Piloter un publipostage Writer depuis Excel

  Visualiser la macro

• Récupérer les macros dans un classeur corrompu

  Le lien sur le forum XLD

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