Discussion :

[Philippe Tulliez]

Préambule
Ayant en projet l'écriture d'un nouveau tutoriel sur la méthode AdvancedFilter de l'objet Range et ayant besoin d'un petit coup de fouet pour l'écrire, j'ai décider de livrer des extraits de chapitre dans la rubrique Contribuez

Petit rappel de la syntaxe

ObjetRange.AdvancedFilter Action, CriteriaRange, CopyToRange, Unique

Où ObjetRange est la zone des données que l'on doit filtrer ou exporter
Les arguments
Action : Est soit la constante xlFilterInPlace pour un filtre sur place ou la constante xlFilterCopy pour une exportation
[CriteriaRange] : est la plage de cellules contenant la zone des critères
[CopyToRange] : est la cellule ou la plage de cellules à partir de laquelle l'exportation doit avoir lieu.
[Unique] : indique si la liste ne doit pas comporter de doublons

Pour en savoir plus sur les filtres avancés d'excel, à lire Les filtres avancés ou élaborés dans Excel

La méthode AdvancedFilter et l'objet ListObject (Tableau structuré)

Comment utiliser un objet ListObject comme source ?
La source est un objet Range et doit comporter la ligne des étiquettes de colonnes et les données. Il est donc très simple d'utiliser la propriété Range de l'objet ListObject comme zone des données

Organisation des données

• Zone des données se trouve sur la feuille nommée [Data] et est un tableau structuré (ListObject)
• Zone des critères est une plage nommée areaCriteria qui se trouve dans la feuille nommée [Parameter] cellules A1:A2
• Zone d'exportation est une cellule nommée areaTarget qui se trouve dans la feuille nommée [Cible] cellule A1

On utilise la propriété CodeName de la feuille nommée [Data] soit shtData et l'objet ListObject (zone des données) présent seul sur cette feuille sera désigné par son numéro dans la collection ListObjects soit ListObjects(1)
Pour savoir ce qu'est le CodeName d'une feuille, lire ce billet VBA – CodeName d’une feuille Excel

Illustration



Code de la procédure

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Sub AdvancedFilterWithListObject()
  Dim areaSource As Range
  Set areaSource = shtData.ListObjects(1).Range
  Range("AreaTarget").CurrentRegion.Clear ' Efface la précédente exportation
  areaSource.AdvancedFilter xlFilterCopy, Range("areaCriteria"), Range("areaTarget")
  Set areaSource = Nothing
End Sub

Résultat avec comme critère les références commençant par la lettre A (A*)


Résultat avec comme critère les références commençant par la lettre T (T*)


Que constate t'on ?
La ligne des totaux est également exportée parce-que la première lettre de la colonne concernée par le critère commence par la lettre T

Explication et correction
La propriété Range de l'objet ListObject renvoie un objet Range comprenant la ligne des titres + les lignes comprenant les données + la ligne des totaux si elle est affichée, comme illustré ci-dessous

Sans la ligne des totaux affichée


Avec la ligne des totaux affichée



Code de la procédure corrigée

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
Sub AdvancedFilterWithListObject_2()
  Dim areaSource As Range, oList As ListObject
  Set oList = shtData.ListObjects(1)
  Set areaSource = oList.Range
  Range("AreaTarget").CurrentRegion.Clear ' Efface la précédente exportation
  With areaSource
   ' Enlève une ligne à l'objet areaSource si la propriété ShowTotals de l'objet oLst est égal à True
    Set areaSource = .Resize(.Rows.Count - Abs(oList.ShowTotals))
  End With
  areaSource.AdvancedFilter xlFilterCopy, Range("areaCriteria"), Range("areaTarget")
  Set areaSource = Nothing: Set oList = Nothing
End Sub

[78chris]

Bonjour Philippe

Bonne idée .

Petite remarque : je n'ai jamais effacé le résultat précédent car Excel s'en charge...

J'ai répondu à une question (que je ne retrouve pas pour l'instant) où on devait extraire vers plusieurs tableaux structurés (selon le critère).

J'avais donc codé pour utiliser la ligne d'en-tête du tableau comme AreaTarget, puis resizé le tableau cible et reformater les colonnes, le filtre avancé écrasant les formats.

Même si ce n'est pas un cas courant, il peut intéresser d'autres utilisateurs...

EDIT : j'ai retrouvé le fil. En fait le souci de l'utilisateur était notamment de garder les MFC définies dans le tableau et finalement je n'avais pas à toucher au format qui ne semblait pas poser de problème car celui de la source était OK.

[Philippe Tulliez]

Bonjour Chris,
Merci pour tes commentaires constructifs

Petite remarque : je n'ai jamais effacé le résultat précédent car Excel s'en charge..

Ce que tu dis est exact mais la raison pour laquelle j'efface les données avant l'exportation est la suivante.
Lorsque tu choisis comme zone d'exportation une cellule (par exemple A1), la méthode AdvancedFilter exporte toutes les colonnes et au passage suivant si les cellules n'ont pas été effacées, la zone d'exportation aura l'étiquette de la cellule A1 et n'exportera que cette colonne là

[EDIT]

J'ai répondu à une question (que je ne retrouve pas pour l'instant) où on devait extraire vers plusieurs tableaux structurés (selon le critère).

C'est l'objet de ma prochaine contribution. Utiliser un objet ListObject comme zone d'exportation

[78chris]

Re

Je n'avais pas pensé au cas d'une cellule unique. Je retiens l'info, merci.

J'attends la suite alors pour le point 2 (tu voulais un petit coup de fouet, alors j'y vais, lol !)

[Philippe Tulliez]

Bonjour Chris,

J'attends la suite alors pour le point 2 (tu voulais un petit coup de fouet, alors j'y vais, lol !)

et merci pour l'encouragement

Comment utiliser un objet ListObject comme cible ?

Organisation des données
Rien de changé concernant la zone des données (source), ni la zone des critères.
La zone d'exportation est également un ListObject et se trouve sur la feuille nommée [Cible List] et son CodeName est shtTarget
Nous utiliserons trois variables de type Range pour désigner les zones Data (areaSource), Critère (areaCriteria) et Exportation (areaTarget)
Illustration

Petit rappel

• areaSource : shtData.ListObjects(1) ' redimensionnée avec la propriété ShowTotals
• areaCriteria : Range("areaCriteria") ' Plage nommée dans la feuille [Parameter]
• areaTarget : shtTarget.ListObjects(1)

Procédure : Vue de la partie déclaration et assignation des valeurs aux variables de la procédure

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
  ' Déclaration des variables
  Dim oListSource As ListObject, oListTarget As ListObject
  Dim areaSource As Range, areaTarget As Range
  ' Attribution des références aux variables
  Set oListSource = shtData.ListObjects(1)
  Set oListTarget = shtTarget.ListObjects(1)
  Set areaSource = oListSource.Range
  With areaSource
   ' Enlève une ligne à l'objet areaSource si la propriété ShowTotals de l'objet oLst est égal à True
    Set areaSource = .Resize(.Rows.Count - Abs(oListSource.ShowTotals), 2)       ' Uniquement 2 colonnes)
  End With
  Set areaTarget = shtTarget.ListObjects(1).HeaderRowRange

Attention : Pour pouvoir effectuer le test avec la méthode Copy, nous ne prenons que deux colonnes de la source (voir ligne 10 du code ci-dessous)
Avec le code qui précède et alors que la méthode Copy fonctionne parfaitement

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

areaSource.Copy areaTarget

La méthode AdvancedFilter va générer une erreur

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

areaSource.AdvancedFilter xlFilterCopy, areaCriteria, areaTarget

Pourquoi ?
Première explication possible.

Un objet ListObject est divisé en plusieurs parties
Nous avons vu que la propriété Range renvoie la ligne des titres + les lignes des données + la lignes des totaux
La propriété HeaderRowRange renvoie la ligne des titres
La propriété DataBodyRange renvoie les lignes des données
Or lorsqu'une liste structurée n'a pas encore de données DataBodyRange est Nothing d'où l'erreur

Nous allons donc insérer une ligne juste avant l'exportation pour qu'il y ait au moins une ligne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
  oListTarget.ListRows.Add 1
  areaSource.AdvancedFilter xlFilterCopy, areaCriteria, areaTarget

Que constate t'on ?



Qu'il y a bien eu exportation mais que la dernière ligne est hors du tableau structuré comme illustré ci-dessous

Comment résoudre ce problème ?
Redimensionnement du nombre de lignes de l'objet cible qui correspond au nombre de lignes à exporter

Ce qu'il faut savoir pour exporter
Le redimensionnement des lignes n'est pas suffisant. Il faut supprimer l'affichage des totaux ainsi que les filtres s'il y en a.
Pour effectuer ces opérations, j'ai créé une fonction qui s'en charge et renvoie la valeur de la propriété ShowTotals avant le redimensionnement pour permettre de réafficher celle-ci le cas échéant.

Code de cette fonction

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Private Function ListObjectResize(oListTable As ListObject, NumberOfRows As Long) As Boolean
  ' Author : http://philippe.tulliez.be
  ' Version : 1.0
  ' Create Date : 10/05/2017
  ' Arguments
  ' oListTable (ListObject) Table a redimensionner
  ' NumberOfRows (Long)     Nombre de lignes à prévoir
  ' Redimensionne oListTable en fonction de NumberOfRows
  '   renvoie la valeur de la propriété ShowTotal
  ' Supprime les données existantes, les filtres et l'affichage des totaux
  Dim flagTotal As Boolean
  If NumberOfRows = 0 Then MsgBox "Argument NumberOfRows:=0", vbCritical, "ListObjectResize"
  With oListTable
   If Not .AutoFilter Is Nothing Then .AutoFilter.ShowAllData       ' Supprime les filtres
   flagTotal = .ShowTotals: .ShowTotals = False                     ' Supprime l'affichage des totaux
   If Not .DataBodyRange Is Nothing Then .DataBodyRange.Rows.Delete ' Supprime toutes les lignes du tableau nommé "tbltarget"
  .ListRows.Add                                                     ' Insère une ligne
   ' Redimensionne la table
  .Resize Range(Cells(.Range.Row, .Range.Column), Cells(.Range.Row + NumberOfRows, .Range.Column + .Range.Columns.Count - 1))
  End With
  ListObjectResize = flagTotal ' Renvoie la valeur de la propriété ShowTotals
End Function

Comment invoquer cette fonction ? VariableBoolean = ListObject, NombreDeLignes soit

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

isTotalRow = ListObjectResize(oListTarget, 3)

Oui mais comment savoir combien de lignes seront exportées suivant les critères ?
Autrement dit, comment remplacer la constante 3 de l'exemple ci-dessus par le nombre réel de lignes à exporter suivant les critères.

L'astuce consiste à utiliser la fonction BDNBVAL de la catégorie Base de données.
Il se fait que l'ensemble des fonctions de cette catégorie, travaille de la même façon que le filtre avancé d'excel soit une zone des données et une zone des critères.
Nous placerons donc la formule dans une cellule de la feuille [Parameter] et que nous nommerons NumberRowsToExport

Voir illustration ci-dessous


Procédure complète

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Sub AdvancedFilterWithListObject()
  ' http://philippe.tulliez.be
  ' Déclaration des variables
  Dim oListSource As ListObject, oListTarget As ListObject
  Dim areaSource As Range, areaTarget As Range, areaCriteria As Range
  Dim isTotalRow As Boolean
  Dim nbRows As Long
  ' Attribution des références aux variables
  Set oListSource = shtData.ListObjects(1)
  Set oListTarget = shtTarget.ListObjects(1)
  Set areaCriteria = Range("areaCriteria")
  Set areaTarget = shtTarget.ListObjects(1).HeaderRowRange
  Set areaSource = oListSource.Range
  ' -- Redimensionnement de l'objet areaSource en fonction de la propriété ShowTotals de l'objet oLst
  With areaSource
    Set areaSource = .Resize(.Rows.Count - Abs(oListSource.ShowTotals))
  End With
  ' Exportation
  nbRows = Range("NumberRowsToExport")
  If nbRows Then
          isTotalRow = ListObjectResize(oListTarget, nbRows)
          areaSource.AdvancedFilter xlFilterCopy, areaCriteria, areaTarget
          oListTarget.ShowTotals = isTotalRow
    Else
      MsgBox "pas de ligne à exporter", vbCritical
  End If
  ' Fin de procédure
  Set areaSource = Nothing: Set areaTarget = Nothing: Set areaCriteria = Nothing
  Set oListSource = Nothing: Set oListTarget = Nothing
End Sub

Classeur de démonstration

[Transitoire]

Bonsoir,
avec mes remerciements pour ce cours "Magistral"
Cordialement

[Philippe Tulliez]

Bonjour Transitoire,
Je te remercie pour ton message congratulant et ton appréciation pour ces billets.
Le prochain billet qui traitera de l'exportation partielle de colonnes en tirant profit des formules du tableau structuré est pratiquement terminé.

[Philippe Tulliez]

Préambule
Avant d’aborder le sujet qui traitera de l'exportation partielle de colonnes en tirant profit des formules du tableau structuré nous allons voir comment créer un code de quelques lignes pour consulter les mouvements d’un article en ayant comme plage source un objet ListObject (tableau structuré) et comme plage cible un objet Range.
Cette procédure est basée sur la méthode AdvancedFilter de l’objet Range.

Scénario
Le but est de consulter les mouvements de stock (Achats, Ventes ou les deux) pour un article défini dans une feuille nommée Consultation

Illustration
Présentation des trois feuilles utilisées pour l’exportation


N’est pas repris sur cette illustration
La feuille nommée Article qui contient un tableau structuré nommé T_Article et dont la première colonne nommée Ref sert de référence à la liste déroulante de la cellule E2
Cette feuille sert également de référence à la fonction RECHERCHEV présente dans les cellules I2:I3 de la feuille [Consultation] pour aller chercher les informations (Libellé, Prix). de l’article sélectionné mais cette partie est hors sujet pour ce billet qui ne concerne que la partie exportation.

Organisation des données
Pour l'utilisation du filtre avancé (méthode AdvancedFilter)

La zone des données : Tableau structuré seul présent dans la feuille dont la propriété CodeName est shtMvt
La zone des critères : Plage nommée areaCriteria avec comme portabilité la feuille de consultation
La zone cible : plage nommée areaTarget

La sélection dynamique des critères

La sélection des critères est faite dans une plage de cellules nommée pQuestion (plage E2:E3) présente dans la feuille de Consultation
E2 : La référence de l’article : sélectionné grâce à une liste déroulante avec comme formule

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=INDIRECT("T_Article[Ref]")

E3 : Le type de mouvement : est le résultat de la sélection dans une liste déroulante des valeurs "Vente", "Achat", "Achat/Vente" présentent dans la feuille nommée [Param]

Code de la procédure
Le code est contenu dans la procédure événementielle nommée Worksheet_Change de la feuille [Consultation]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
Private Sub Worksheet_Change(ByVal Target As Range)
  ' Ne réagit à l'événement que s'il y a changement dans la plage nommée "pQuestion"
  If Not Application.Intersect(Target, Range("pQuestion")) Is Nothing Then
         Application.EnableEvents = False   ' Désactive l'interception d'événements
         ' Exportation des données suivant critères
         shtMvt.ListObjects(1).Range.AdvancedFilter xlFilterCopy, Range("areacriteria"), Range("areatarget")
         Application.EnableEvents = True   ' Active l'interception d'événements
  End If
End Sub

A lire
Pour bien comprendre

Les filtres avancés : Les filtres avancés ou élaborés dans Excel
Les tableaux structurés : Apprendre à utiliser les tableaux structurés Excel : création, manipulations et avantages(1)
La propriété CodeName de l'objet Worksheet : VBA – CodeName d’une feuille Excel

Classeur à télécharger
le classeur ayant servi à préparer cette contribution

[EDIT]
Attention Avant de déposer mon classeur; j'ai effacé par erreur la ligne qui désactive la procédure événementielle (Ligne 4), et j'ai effectué un copier/coller sans modifier la valeur de l'argument (True)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Application.EnableEvents = False   ' Désactive l'interception d'événements

Même s'il n'y a pas de risque, pour ceux qui ont téléchargé le classeur il y a lieu de faire cette modification.

L'erreur est corrigée dans le code et j'ai joint un nouveau classeur.