Discussion :

[MarcelG]

Bonjour les amis du Forum,

Je m'interrogeais sur le processus à suivre pour sommer 2 tableaux structurés.

Une méthode consisterait à utiliser les variables ... Tableau

Soit
Au préalable, acquérir le code qui aboutit à la sommation de 2 variables tableau
Affecter une première variable tableau au premier tableau structuré, puis une seconde variable au second tableau.
Sommer les 2 variables tableau
Reverser ce tableau dans le tableau structuré final (celui-ci ayant été vidé au préalable)

Ce qui donne

Pour la fonction de concaténation de 2 variables tableau

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
Function ArrayPlusNew(ArrDep As Variant, Plus As Variant)
 
'ajoute l'array Plus à l'array ArrDep
'l'array d'arrivée est un nouvel Array
 
Dim ArrFinal
Dim i As Integer, j As Integer, k As Integer
 
k = 1
ReDim ArrFinal(1 To UBound(ArrDep, 1), 1 To UBound(ArrDep, 2) + UBound(Plus, 2))
For i = 1 To UBound(ArrDep, 1)
        For j = 1 To UBound(ArrDep, 2)
                ArrFinal(i, j) = ArrDep(i, j)
        Next j
        For j = UBound(ArrDep, 2) + 1 To UBound(ArrFinal, 2)
                ArrFinal(i, j) = Plus(i, k)
                k = k + 1
        Next j
        k = 1
Next i
 
ArrayPlusNew = ArrFinal
 
End Function

Pour 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
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
Option Explicit
 
Sub ConcatTabloStruc()
 
Dim Tblo1() As Variant, Tblo2() As Variant, TbloTot() As Variant
Dim Tblo As Variant, tb As Variant
 
'Vider le tableau de concaténation initial
Worksheets("Feuil3").Range("ConcatTablo").ListObject.DataBodyRange.Delete
 
'Pour sommer 2 Arrays, il est nécessaire que la 1ère dimension soit identique
'D'où le nombre de colonnes en 1ère dimension
With WorksheetFunction
        Tblo1 = .Transpose(Worksheets("Feuil1").Range("FirstTablo").Value)
        Tblo2 = .Transpose(Worksheets("Feuil2").Range("ScndTablo").Value)
        'Cellules vides de départ
        TbloTot = .Transpose(Worksheets("Feuil3").Range("A50000:B50000").Value)
End With
 
'Constitution d'un tableau de tableaux pour effectuer la boucle
'et sommer un à un les tableaux à partir du 2ème sur le 1er
Tblo = Array(Tblo1, Tblo2)
For Each tb In Tblo
        TbloTot = ArrayPlusNew(tb, TbloTot)
Next
 
'Suppression de la dernière ligne vide initialement TbloTot
ReDim Preserve TbloTot(1 To 2, 1 To UBound(TbloTot, 2) - 1)
 
Worksheets("Feuil3").Range("A2").Resize(UBound(TbloTot, 2), UBound(TbloTot, 1)).Value = WorksheetFunction.Transpose(TbloTot)
 
'Variables Tableau vidées
Erase Tblo1
Erase Tblo2
Erase TbloTot
 
MsgBox _
        Prompt:="La nouvelle concaténation contient" & Chr(13) & _
                                            Worksheets("Feuil3").Range("ConcatTablo").ListObject.DataBodyRange.Rows.Count & " enregistrements.", _
        Buttons:=vbInformation, _
        Title:="Résultat"
 
End Sub

Code testé.
(En fait, il s'agit, pour mon développement, de 3 fichiers différents ayant la même structure. Et je suis viscéralement opposé aux classeurs partagés.)

A noter.
Le processus est adaptable à 3, 4, 5, et plus tableaux structurés.

Je voulais vous faire part de cette proposition en attendant, bien entendu, vos judicieuses remarques.
Il pourrait y avoir en effet d'autres processus envisageables.
A vous lire donc.

Bon après-midi à tous.

[Philippe Tulliez]

Bonjour Marcel,
Tu pourrais-tout simplement ajouter la ligne des totaux et lire ensuite la cellule correspondante et pour tous tes tableaux

Exemple

Code :

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

Debug.Print Range("T_Personnel").ListObject.ListColumns("Salaire").Total

ensuite faire l'addition des résultats ainsi obtenu

[MarcelG]

Bonjour Pilippe,

Merci pour ton intervention.

Excuse-moi, mais je ne comprends pas la finalité de cette ligne de code.

Mon but est d'obtenir un tableau structuré final issu de l'empilement de plusieurs autres.
Si FirstTablo reporte 7 enregistrements et ScndTablo 28 enregistrements, alors je souhaiterais disposer d'un troisième tableau structuré ConcatTablo de 7 + 28 = 35 enregistrements.
(La dernière instruction MsgBox a pour seul but de vérifier la bonne exécution du code.)

[Philippe Tulliez]

Bonjour Marcel,
Désolé, j'ai répondu vite avant de partir et je n'avais pas compris que tu voulais créer un seul tableau constitué de plusieurs.
Le fait que tu évoques la somme de deux tableaux m'a induit en erreur.

[MarcelG]

Salut Philippe,

Pas grave, tu penses bien!

Peut-être, quand tu en auras le loisir, un avis?

[Philippe Tulliez]

Bonjour Marcel,
Si c'est faire de plusieurs tableaux en un seul, tu peux prendre des idées dans cette contribution La méthode AdvancedFilter et l'objet ListObject

Entretemps, j'ai aussi écrit une procédure qui écrit une variable tableau à la suite d'un tableau structuré existant mais je dois chercher.

[Malick]

Salut,

Si cela n'est pas trop tard, avec Power Query intégré à Excel 2016, cela est possible sans ligna de code.

[78chris]

Bonjour à tous

Salut,

Si cela n'est pas trop tard, avec Power Query intégré à Excel 2016, cela est possible sans ligna de code.

Je plussoie

[Pierre Fauconnier]

Salut Marcel,

Je plussoie Malick et Chris...

Powerquery permet de regrouper les tableaux d'un classeur, les tableaux des classeurs d'un dossier, ... avec conditions, exclusions, colonnes formulées, regroupements, et en ajoutant l'info du tableau source pour pouvoir filtrer dessus (notamment par TCD)

En VBA, copier un tableau "à la suite" d'un autre de même structure se réalise ainsi, dans la mesure où l'on a besoin de connaître la source des lignes

Code :

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

1
2
3
4
5
6
7
8
Function MergeTables(Source As ListObject, Target As ListObject, SourceColumnName As String, SourceName As String)
  Dim celTarget As Range
 
  Set celTarget = Target.Range(Target.ListRows.Count + 2, 1)
  Set celTarget = celTarget.Resize(Source.ListRows.Count, Source.ListColumns.Count)
  celTarget.Value = Source.DataBodyRange.Value
  celTarget.Offset(0, Target.ListColumns(SourceColumnName).Index - 1).Value = SourceName
End Function

Si on intègre cela dans une boucle pour passer en revue les fichiers dont les noms sont contenus dans un tableau structuré et utiliser le premier tableau de la première feuille, on peut utiliser le code qui précède dans la boucle qui scanne les fichiers du tableau...

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
Sub Fusion()
  Dim Filename As String
  Dim tFinal As ListObject
  Dim fRange As Range
  Dim wbSource As Workbook
  Dim tSource As ListObject
  Dim HasTotalrow As Boolean
 
  Application.ScreenUpdating = False
 
  ' Nettoyage tableau final
  Set tFinal = Range("t_Final").ListObject
  If Not tFinal.DataBodyRange Is Nothing Then tFinal.DataBodyRange.Delete
  HasTotalrow = Not tFinal.TotalsRowRange Is Nothing
  tFinal.ShowTotals = False
 
  For Each fRange In Range("t_Fichiers[Fichier]")
    Filename = ThisWorkbook.Path & "\" & fRange.Value
    Set wbSource = Workbooks.Open(Filename)
    ThisWorkbook.Activate
    Set tSource = wbSource.Worksheets(1).ListObjects(1)
    If Not tSource.DataBodyRange Is Nothing Then
      MergeTables tSource, tFinal, "Source", Filename
    End If
    wbSource.Close False
  Next
  tFinal.ShowTotals = HasTotalrow
  Application.ScreenUpdating = True
End Sub

Des fonctions génériques pour la manipulation des tableaux, notamment leur vidange, aideraient grandement. Perso, je me sers d'une classe personnalisée xlTable qui encapsule le ListObject et offre des "raccourcis de codage", mais une fonction à se garder au chaud permet de nettoyer rapidement un tableau structuré.

Code :

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

1
2
3
Function ClearTable(Table As ListObject)
  If Table.ListRows.Count > 0 Then Table.DataBodyRange.Delete
End Function

On pourrait tendre vers du plus générique, notamment avec une classe perso xlTable. La mienne prévoit déjà ces travaux de consolidation et s'appuie sur le code que je donne plus haut, mais on remarquera qu'il faut penser à la ligne de total du tableau qui pourrait venir perturber le tout.

[MarcelG]

Bonjour le Forum,

Merci à vous pour vos réponses précieuses.

Je regarde au plus tôt et ne manquerai pas de revenir.

Bonne semaine à tous.

[MarcelG]

Bonjour à tous,

Pierre,

Je m'y perds un peu.

Qu'entends-tu par

la source des lignes

Sauf erreur,
Dans la fonction, le 1er argument "Source" représente l'objet Tableau de chaque fichier.
Que représente alors "SourceColumnName"?

D'autant que, dans la procédure où la fonction est appelée, Source est en 3ème argument.

Dans mon applicatif, tous les fichiers à consolider ont la même ligne de titres et, bien entendu, les mêmes colonnes.
Tous les fichiers ont le même tableau structuré T_Base aux enregistrements près et doivent être consolidés dans le tableau "final" T_Reporting.

Excuse, mais j'ai adopté les tableaux structurés seulement depuis 6 mois. Je sais "Honte à Marcel".

Merci à toi.

[Pierre Fauconnier]

Salut Marcel,

La source des lignes, c'est simplement de savoir d'où elles viennent, c'est-à-dire de quel fichier ou de quel tableau. Ca permettra lors de l'analyse d'avoir cette dimension d'analyse...

Imaginons que tu fusionnes 3 tableaux, un pour les données de Paris, un autre pour celles de Strasbourg et un troisième de Bordeaux: SourceColumnName pourrait s'appeler "Ville" et pour chaque tableau importé, la macro ajouterait le nom de la ville dans cette colonne, permettant l'analyse "intervilles" (Bonjour Guy Lux )

Si tu n'as pas besoin de cette dimension, tu peux rendre les arguments optionnels et ne les traiter que s'ils ne sont pas vide, voire les supprimer purement et simplement.

Source représente bien le tableau d'origine T_Base de chaque fichier, et Target représente le tableau T_Reporting

[MarcelG]

Merci Pierre.

Je regarde au plus tôt. (Surbooké) et ne manquerai pas de revenir.

[MarcelG]

Bonjour le Forum,

Malick, Chris,

Pardon; j'ai tardé à vous remercier pour votre concours.
Pour ma formation, je compte aborder Power Query après consolidation de ma connaissance des tableaux structurés. (Pitié!)

Pierre,

Ok, j'ai compris (déjà rare!) et j'ai appliqué.

Un détail, au demeurant.
Dans la fonction, pour reporter le nom du classeur originel ("Source"), il te faut préciser

Code :

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

Cells(1,1)

J'ai testé l'adresse et, ce qui est logique vu le code, celle-ci reporte le nombre de colonnes du tableau d'origine.
Ce qui génère autant (-1) de répétitions et de nouvelles colonnes créées.
Donc

Code :

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

celTarget.Offset(0, Target.ListColumns(SourceColumnName).Index - 1).Cells(1, 1).Value = SourceName

Ou bien quelque chose m'échappe?

Au plaisir.

[78chris]

Bonjour

Malick, Chris,

Pardon; j'ai tardé à vous remercier pour votre concours.
Pour ma formation, je compte aborder Power Query après consolidation de ma connaissance des tableaux structurés. (Pitié!)

Si tu nommes tes Tableaux structurés avec un nom préfixé, par exemple T_

Cela se fait en 5 opérations très simples et de plus tout nouveau tableau structuré avec un nom préfixé de même, s'ajoutera automatiquement...

Donc la torture est très légère par rapport à VBA

[MarcelG]

Salut Chris,

D'accord, je regarderai.

Au sein de mon organisation, je ne peux faire autrement et j'exclus le partage de classeur.
Dans mon cas, il s'agit de classeurs d'exportation créés depuis l'applicatif que je développe.
Ce qui signifie que leur nombre est indéfini car croissant.
En conséquence, le balayage d'un dossier est obligatoire.

Merci à toi, et bonne journée.

[78chris]

RE

C'est aussi faisable à partir des classeurs d'un dossier...

[Pierre Fauconnier]

Pour abonder dans le sens de Chris, tu peux balayer un dossier avec Power Query puis filtrer si tu nommes tes fichiers de façon systématique




EDIt: Ma réponse a croisé celle de Chris

[Pierre Fauconnier]

Cela étant dit, PowerQuery génère un script mais qui réalise forcément les étapes par lesquelles tu passes en VBA, et n'y a pas vraiment de "torture" en VBA ( Chris ).

Je n'ai pas bien compris ce qui suit.

[...]
J'ai testé l'adresse et, ce qui est logique vu le code, celle-ci reporte le nombre de colonnes du tableau d'origine.
Ce qui génère autant (-1) de répétitions et de nouvelles colonnes créées.
Donc

Code :

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

celTarget.Offset(0, Target.ListColumns(SourceColumnName).Index - 1).Cells(1, 1).Value = SourceName

Ou bien quelque chose m'échappe?

Au plaisir.

Faut que je vérifie, car tu aurais bien raison

[MarcelG]

Salut Pierre,

J'ai du mal m'exprimer.

D'après moi,

Cette dernière ligne de la fonction Merge

Code :

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

celTarget.Offset(0, Target.ListColumns(SourceColumnName).Index - 1).Value = SourceName

doit être remplacée par

Code :

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

celTarget.Offset(0, Target.ListColumns(SourceColumnName).Index - 1).Cells(1, 1).Value = SourceName

Sinon la source (nom du fichier) est répétée.

J'ai testé dans la fenêtre débogage

Code :

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

?celTarget.Offset(0, Target.ListColumns(SourceColumnName).Index - 1).Address

Et j'ai obtenu une plage contenant le nombre de colonnes du tableau structuré d'origine.
Ce qui est, toujours d'après moI, logique vu la ligne de code.