Discussion :

[Druddy66]

Bonjour,

Je reviens sur une solution qui avait été résolu dans : https://www.developpez.net/forums/d1...lation-donnees

Je me suis servi de ce code (excellent et clair) pour mon sujet mais pour mon cas je n'ai pas la copie des données correspondantes à l'onglet mais toutes les données et pas de suppression des anciennes lignes dans les onglets.

Auriez-vous la solution à mon problème ?

Je vous remercie par avance,

[Pierre Fauconnier]

Salut.


Perso, je préfèrerais que tu expliques ce que tu souhaites réaliser et ce que tu as au départ. Aller trifouiller dans un code construit pour un classeur conçu différemment du tien ne va amener que des misères...

Que souhaites-tu réaliser au départ du tableau présent sur la feuille Source (C'est un tableau structuré, c'est déjà très bien, mais il conviendra de le renommer... Tableau1343 => )

[Druddy66]

Bonjour Pierre,

Merci pour ce retour.
En effet, à partir du tableau, il y a la colonne "Site Code" et j'aimerais ventiler les données (lignes) vers des onglets par "Site Code" avec les données correspondantes au site.
Ces données seront mises à jour chaque semaine et il faudrait supprimer les anciennes pour les updater si l'onglet existe.
Sinon créer un onglet, s'il n'existe pas dans la colonne "Site Code" et lui ajouter les données correspondantes ou supprimer l'onglet si le "Site Code" n'existe plus dans la colonne.

Je ne sais pas si c'est plus clair.

Je vous remercie par avance,

[Pierre Fauconnier]

Ok.

Quelle est ta version d'Excel?

[Druddy66]

j'ai une version 2002 entreprise

[Pierre Fauconnier]

Ce n'est pas possible. Le classeur que tu as donné est un xlsm et il y a un tableau structuré dedans. Je parles de ta version d'Excel (ou d'Office si tu préfères). As-tu une version 365?

[Druddy66]

oui Excel pour office pro plus pardon

[Pierre Fauconnier]

Voilà comment je ferais la chose...

1. Supprimer les feuilles autres que "Source"
2. Créer la liste des sites sans doublons;
3. Préparer les feuilles qui vont recevoir les données ventilées;
4. Transférer les données;
5. Transformer les plages des données ventilées en tableaux structurés.

Pour cela, je vais créer des petites fonctions qui font "une seule chose" (créer un tableau, transférer une donnée, vérifier qu'une feuille existe, ...). La plupart de ces fonctions vont recevoir le tableau des sites sans doublons en argument. Vu que les fonctions sont très courtes, il me semble que le code parle par lui-même et se passe d'explications. N'hésite pas si tu as des questions, bien sûr.


1. Supprimer les feuilles autres que "Source"

On place Source en première position et on supprime les autres feuilles

Code :

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

1
2
3
4
5
6
7
8
9
10
Function DeleteSheets()
  Dim i As Long
 
  Application.DisplayAlerts = False
  If Worksheets.Count > 1 Then Worksheets("Source").Move before:=Worksheets(1)
  For i = 2 To Sheets.Count
    Sheets(2).Delete
  Next i
  Application.DisplayAlerts = True
End Function

2. Créer la liste des sites sans doublons¹

On va utiliser deux fonctions:

• une qui teste qu'une valeur est dans la liste
• une qui crée le tableau vba (array) avec les données sans doublons.

Voici la fonction qui teste qu'une valeur existe dans un tableau à une dimension. Elle reçoit le tableau et la valeur à tester, elle boucle jusqu'à trouver la valeur et renvoie un booléen

Code :

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

1
2
3
4
5
6
7
8
9
10
Function IsInArray(t, Value) As Boolean
  Dim i As Long, j As Long
 
  i = LBound(t)
  j = UBound(t)
  Do While i <= j And Not IsInArray
    IsInArray = (t(i) = Value)
    i = i + 1
  Loop
End Function

Voici la fonction qui crée le tableau vba en le peuplant avec les noms des sites

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
Function getSiteCodes()
  Dim r As Range
  Dim s
  ReDim t(0 To 0)
  Dim i As Long
 
  s = Range("t_data[site code]").Value
  For i = 1 To UBound(s)
    If Not IsInArray(t, s(i, 1)) Then
      If Not IsEmpty(t(0)) Then ReDim Preserve t(0 To UBound(t) + 1)
      t(UBound(t)) = s(i, 1)
    End If
  Next
  getSiteCodes = t
 
  Erase t
  Erase s
  Set r = Nothing
End Function

3. Préparer les feuilles qui vont recevoir les données ventilées

La préparation des feuilles nécessite 4 opérations:

1. créer la feuille;
2. la nommer avec le nom du site;
3. y copier les entêtes du tableau;
4. préparer la zone de critères pour l'extraction des données (Voir point suivant).

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Function PrepareSheets(SiteCodes)
  Dim i As Long
  Dim sh As Worksheet
 
  For i = LBound(SiteCodes) To UBound(SiteCodes)
    Set sh = Worksheets.Add(after:=Sheets(Sheets.Count))
    sh.Name = SiteCodes(i)
    sh.Range("a4").Resize(1, Range("t_Data").Columns.Count).Value = Range("t_Data[#Headers]").Value
    sh.Range("a1").Value = "Site Code"
  Next i
 
  Set sh = Nothing
End Function

4. Transférer les données

Pour transférer les données, on va utiliser un filtre avancé en VBA (voir le tuto de Philippe Tulliez). Cet outil permet de transférer très rapidement des données d'une feuille à l'autre sans devoir jouer avec des filtres automatiques, des lignes visibles, etc... Tout cela est bien compliqué à manipuler par rapport à la simplicité du filtre avancé.

Pour manipuler le filtre avancé, on a besoin de la plage source avec ses entêtes, d'une zone de critères (préparée au point précédent) et d'une zone d'arrivée pour y coller les données. Elle a été préparée lors de la création des feuilles en copiant l'entête du tableau de données. Dès lors, pour le transfert, on va boucler sur le tableau des sites, copier le nom du site dans la zone de critère puis utiliser le filtre avancé

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Function TransferData(SiteCodes)
  Dim i As Long
  Dim sh As Worksheet
 
  For i = LBound(SiteCodes) To UBound(SiteCodes)
    Set sh = Worksheets(SiteCodes(i))
    sh.Range("a2") = SiteCodes(i)
    Range("t_data[#all]").AdvancedFilter xlFilterCopy, sh.Range("a1:a2"), sh.Range("a5").Resize(1, Range("t_Data").Columns.Count)
  Next i
 
  Set sh = Nothing
End Function

5. Transformer les plages des données ventilées en tableaux structurés

A nouveau sur base du tableau des sites, on se place en A4 de chaque feuille et on transforme la plage courante en tableau structuré

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Function CreateListObjects(SiteCodes)
  Dim i As Long
  Dim sh As Worksheet
  Dim lo As ListObject
 
  For i = LBound(SiteCodes) To UBound(SiteCodes)
    Set sh = Worksheets(SiteCodes(i))
    Set lo = sh.ListObjects.Add(xlSrcRange, sh.Range("a5").CurrentRegion)
    lo.Name = "t_" & SiteCodes(i)
  Next i
 
  Set lo = Nothing
  Set sh = Nothing
End Function

Exécution

On a besoin d'une macro qui exécute tout cela en appelant les fonctions dans l'ordre souhaité. C'est cette macro que l'on actionnera pour réaliser l'ensemble de la manoeuvre.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub SplitData()
  Dim SiteCodes
 
  Application.ScreenUpdating = False
 
  SiteCodes = getSiteCodes
  DeleteSheets
  PrepareSheets SiteCodes
  TransferData SiteCodes
  CreateListObjects SiteCodes
  Worksheets("Source").Activate
 
  Application.ScreenUpdating = True
End Sub

C'est donc une autre manière de procéder que celle que tu as vue sur l'autre discussion. En codant de cette manière, tu peux tester calmement chaque fonction pour bien la décortiquer, la comprendre et donc pouvoir l'adapter à ton cas. Idéalement, on devrait variabiliser les emplacements pour coller les données et placer les critères, mais je pense que tu pourras adapter.

Voici le classeur avec le code pour que tu puisses exécuter cela. N'hésite pas à y aller pas à pas : SpitData.xlsm


Note que ce genre de code doit toujours être exécuté après avoir fait une copie du classeur original. Note également que l'on aurait pu travailler feuille par feuille (créer la première feuille, la préparer, transférer les données, transformer en tableau, puis passer à la suivante). C'est une autre façon de faire tout aussi valable.




¹ On aurait pu utiliser un dictionnaire comme dans la discussion que tu as fournie en lien, mais perso, je n'aime pas, surtout sur le forum, car ça fait utiliser une ressource NON VBA sans rien y comprendre. Même si l'exécution est un peu plus lente, je préfère du VBA pur.

[Druddy66]

Merci Pierre pour tout ce travail,

C'est une excellente approche que je vais mettre en place et tester.

Question : pour la partie :
3. Préparer les feuilles qui vont recevoir les données ventilées

La préparation des feuilles nécessite 4 opérations:
créer la feuille;
la nommer avec le nom du site;
y copier les entêtes du tableau;
préparer la zone de critères pour l'extraction des données (Voir point suivant).

La création de la feuille peut-elle être faite à partir d'un modèle ? Sachant qu'à la finale, il y aura 350 onglets, autant avoir la même trame ?
Ton approche m'a fait réfléchir et je vais l'utiliser pour le même fichier avec 2 autres ventilations (2 autres sources en plus) qui s’inséreront dans les onglets créés de la 1ère ventilation.

Mais déjà, je vais commencer par tes conseils

Merci encore

Je te remercie par avance,

[Pierre Fauconnier]

Oui, on peut utiliser un modèle. C'est une approche qui peut avoir son intérêt. Le principe général est de découper le projet en petits morceaux très digestes. Ca passe mieux. Si tu souhaites utiliser un modèle, tu devrais uniquement modifier le code à l'intérieur de la fonction qui prépare les feuilles. S'il y a plusieurs manipulations, ça peut être une bonne idée. Tu devras aussi adapter l'étape 1 qui supprime toutes les feuilles sauf la source, sans quoi ta feuille modèle passera elle aussi à la trappe.

On pourrait, lors de la création des feuilles, leur donner un nom VBA (un codename) qui serait préfixé. Ainsi, on pourrait supprimer uniquement les feuilles qui ont le codename préfixé. A réfléchir si tu devais avoir plusieurs feuilles à ne pas supprimer dans ton classeur.

[Raccourcix]

Bonjour

Je vous propose une autre approche :

Ajout d'un TCD avec vos données, le "Site Code" en ligne et en valeur (ici en colonne O de votre onglet "Source" (sans total en bas)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub Création_TCD()
    ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:="Tableau1343", _
        Version:=6).CreatePivotTable _
        TableDestination:="Source!R3C15", _
        TableName:="Tableau croisé dynamique1", _
        DefaultVersion:=6
    With ActiveSheet.PivotTables("Tableau croisé dynamique1")
        .ColumnGrand = False
        .AddDataField .PivotFields("Site Code"), "Nombre de Code", xlCount
        .PivotFields("Site Code").Orientation = xlRowField
    End With
End Sub

Puis on "double-clique" sur chaque cellule par macro en nommant la feuille obtenue suivant le "Site Code" associé

Code :

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

1
2
3
4
5
6
Sub Génère_Onglet()
    For Each Cellule In Range(ActiveSheet.PivotTables("Tableau croisé dynamique1").DataBodyRange.Address)
        Cellule.ShowDetail = True
        ActiveSheet.Name = Cellule.Offset(0, -1).Value
    Next Cellule
End Sub

Stéphane

[Pierre Fauconnier]

Salut Stéphane,

Chouette approche (), mais alors je me passe de la création du TCD par macro et je l'incorpore dans la conception du classeur. Après import des données, une simple mise à jour et le "double-clic" réalisés en VBA et c'est bon...

Perso, je n'ai jamais vu d'intérêt à créer le TCD par macro

Création du classeur:

1. Import des données dans le classeur par Power Query;
2. Création du TCD via Excel;
3. Ventilation par VBA.

Exploitation du classeur:

1. supprimer les feuilles;
2. actualiser la requête Power Query
3. actualiser le tcd
4. ventiler.

[78chris]

Bonjour à tous

Pour ma part je pense qu'un classeur des 350 onglets est un monstre donc une autre suggestion..

Sachant qu'on ne regarde jamais (ou très rarement ) 2 onglets à la fois, pourquoi ne pas ventiler à la demande par PowerQuery et/ou TCD.

On clique et on a les données, à jour, du site Code choisi

[Druddy66]

Bonjour et merci à vous,

Mais je commence à m'y perdre dans tous ces scripts.

Je vous propose de faire un récapitulatif du "projet" car vos retours m'ont permis d'être plus ambitieux sur mon projet initial et surtout plus constructif pour mes ingénieurs qui l'utiliseront.


En tout cas je suis touché par votre attention et j'apprécie énormément vos efforts sur mon projet.

J'apprécie le détail de Pierre tout comme ceux de John Mc Evee qui avait participé à la résolution du pb similaire :
https://www.developpez.net/forums/d1...ation-donnees/

Recopié est intéressant pour le projet mais si je demande de l'aide c'est surtout pour comprendre ce qui est proposé et ensuite l'adapter.

N'hésitez pas à abuser du "'" en commentaire.

C'est une information importante à la compréhesion pour de futures adaptations.

Merci encore à vous

Druddy

[Druddy66]

Bonjour à Tous,

Voilà le topo.

J'ai en référentiel :
- Un fichier excel Baseline (qui recense les équipements)
- Un fichier SiteContacts extrait d'un sharepoint qui génère un fichier query.iqy que je mets en forme
- Un fichier GrepLegLines extrait du sharepoint qui génère aussi un fichier query.iqy que je mets en forme

Ces 3 fichiers sont mis à jours tous les vendredis.

De ces 3 fichiers qui ont des informations différentes mais une clé commune "Site Code", j'aimerais centraliser toutes ces données à l'affichage lorsque l'on sélectionne un site (onglet : Modèle Fiche site code) .
En effet, des intervenants ne l'utiliseront qu'en lecture.
Au lieu de créer des onglet, n'afficher que le résultat lié au Site Code demandé me semble plus simple, non ?

Je vous joins le fichier : Pièce jointe 605901

J'ai lu que vous parliez de Power BI, des personnes m'en ont parlé mais je n'y connais rien hélas mais une solution pour me faire apprendre serait aussi la bienvenue.

En tout Merci à vous encore une fois pour l'intérêt que vous portez à ma requête.

[Druddy66]

Bonjour à Tous,

Pourriez-vous me dire si c'est possible de réaliser mon projet via Power BI avec les éléments que je vous ai transmis ?

Je vous remercie par avance,

[78chris]

Bonjour

Nous parlions essentiellement d'Excel, pas de PowerBI

Le souci de ton modèle est qu'on doit y superposer 4 tableaux résultats issus de requêtes et cela ne plait pas à Excel : j'avais fait quelque chose le 2/10 mais je récupérais des messages d'erreurs.

De toute façon la hauteur des tableaux étant variable cette superposition risque de provoquer des problèmes de chevauchement sauf à prévoir un grand nombre de lignes vides et masquer le superflu en fin d’actualisation.

[Pierre Fauconnier]

Salut Chris,

Je me disais la même chose, mais ne savais si, par la suite, on allait renvoyer chaque feuille splittée à chaque site.

Mais on pourrait effectivement, même dans ce cas, itérer sur les sites dans doublons pour actualiser power Query et envoyer la feuille résultat dans un classeur reprenant le nom du site...

[Raccourcix]

Merci Pierre pour le retour

Je me faisais la même réflexion concernant le nombre d'onglets.
Perso, le plus grand nombre que j'ai eu à traiter était d'environ 280 onglets, c'était impossible à utiliser.
Au final, une base de données, un peu de power query, des TCD avec des segments pour les piloter et l'utilisateur était bien plus satisfait qu'avec ses 280 feuilles.

Enfin, plutôt que d'envoyer chaque feuille à son site, je mets régulièrement mes bases sous Power BI, un fichier Excel pour gérer la Sécurité par ligne via les UPN
si on ne veut pas de Rapport power BI, j'envoie un fichier Excel de restitution avec des TCD (vides au départ car filtré sur un élément sans données) , chacun actualise et ne visualise que son périmètre. J'ai gagné de nombreuses heures de la sorte (ainsi qu'à tous mes collègues)

Stéphane

[Pierre Fauconnier]

En fait, lorsque j'ai proposé la solution, je ne savais pas qu'il y avait autant de sites, mais c'est effectivement ingérable, ne serait-ce que pour la navigation.

Je pense donc également que récupérer les données "à la demande" par un Power Query filtré ou Power BI est une solution bien plus intéressante. Mais on parle alors de conception d'un système d'information qui doit être réfléchi.

À Druddy de voir où il veut aller