Import données fichier externe et répartir par onglet

[KIM]

Bonjour le forum, bonjour les ami(e)s,
Dans le fichier joint RechercherColorierCondition_v1.xlsm, Jean-Marie et Jacky67 m'ont aider à identifier et colorier les références de l'onglet Base selon une condition des données de cette référence dans les onglets de détails. Les données de ces onglets sont liées à un seul onglet "References" du fichier externe DetailsRef.xlsm.
Ce fichier externe est issu d'une extraction d'une base de données, Il peut y avoir de nouvelles références ou des réfrences qui disparaissent.
Est-il possible de lire, à partir du fichier RechercherColorierCondition_v1.xlsm, les données de l'onglet "References" du fichier externe DetailsRef.xlsm, créer les onglets qui correspondent au 1er mot de la col C et répartir les données correspondantes (Col B, C, F, K, O) dans les col A, B, C, D, E de chaque onglet concerné avec les titres de chaque col à partir de la ligne 2. ensuite rajouter une col F = E-(D-C)?
SI l'onglet existe déjà dans le fichier RechercherColorierCondition_v1.xlsm, le supprimer et le recréer avec les nouvelles données.
Je vous remercie par avance de votre aide.
Ci-joints les 2 fichiers
Bien amicalement
KIM

 

[chris]

Bonjour

Pourrais-tu mettre ton profil à jour avec des fichiers xlsm tu aies encore Excel XP...

Et le solutions des versions actuelles diffèrent...

 

[ChTi160]

Bonjour KIM
Bonjour le Fil,Bonjour le Forum
Première question Lol
les fichiers sont ils dans le même dossier ?
si Non connais tu le Chemin d’Accès.
Merci
Bonne fin de Journée
Amicalement
Jean marie

 

[KIM]

Re bonjour Jean-Marie,
bonjour le fil et le forum,
Merci d'avance Jean-Marie.
Normalement les 2 fichiers sont dans le même répertoire.
Amicalement
KIM

 

[chris]

Bonjour

Je laisse Chti160 (que je salue ) te faire une routine VBA complète.

Sur 2010 avec l'add on PowerQuery on pourrait traiter en requête + TCD (donc à faire une fois pour toute).

Resterait 3 lignes de VBA pour supprimer les onglets de la fois passée et reventiler le TCD en onglets par un simple .ShowPages

A noter que PowerQuery est totalement intégré à Excel à partir de 2016, ce pourquoi je propose cette alternative

 

[KIM]

Bonjour Chris, et le fil
Merci pour tes conseils, en effet j'utilise Office 2010, mais je préfère ne pas utiliser une solution spécifique office2010 pour pouvoir partager la solution avec d'autres utilisateurs.
Merci d'avoir pris le temps de regarder.
Bien amicalement
KIM

 

[ChTi160]

Re KIM ,Chris

Chris rien n empêche que tu proposes cette façon de faire , que je suis incapable de faire d'ailleurs lol
moi de mon coté ,je vois ce que je peux bidouiller lol
Ici on partage !
Merci par avance
Bonne fin de Journée
Amicalement
Jean marie

 

[chris]

Re

... je préfère ne pas utiliser une solution spécifique office2010 pour pouvoir partager la solution avec d'autres utilisateurs...

Elle n'est pas compatible avec les vieilles versions mais avec toutes les nouvelles...

Chris rien n empêche que tu proposes cette façon de faire...

Commencer par redéfinir la plage à utiliser dans DetailsRef car les titres des colonnes sont nécessaires et la plage Format ne les inclut pas.
Depuis un onglet vierge du classeur où doit se faire la synthèse :

• onglet PowerQuery sur 2010 ou 2013, Données sur 2016 : A partir d'un fichier, A partir d'Excel, sélectionner le classeur puis la plage nommée (on pourrait utiliser l'onglet si le tableau démarrait en A1 et non en B2)
• Dérouler Charger pour prendre ChargerDans et sélectionner connexion uniquement
• Afficher le volet des requêtes (onglet PowerQuery sur 2010 ou 2013, Données sur 2016)
• Clic droit sur la requête, modifier
• Onglet transformer : utiliser la 1ère ligne pour les en-têtes (pas automatique du fait des colonnes disjointes)
• Sélectionner toutes les colonnes inutiles et clic droit, Supprimer
• Onglet ajouter une colonne :
  • ajouter une colonne personnalisée : donner un nom à cette colonne et mettre la formule correspondant à = E-(D-C) en utilisant les noms des colonnes
  • ajouter une autre colonne dupliquant Désignation (=[Désignation])
• Sélectionner la dernière colonne ajoutée et Onglet Transformer, Fractionner la colonne, délimiteur Espace, le plus à gauche.
  Cela répartit le contenu dans deux colonnes. Supprimer la seconde, nommer la 1ère, Famille par exemple.
  
• Fermer et charger. On revient à notre onglet vierge
  
• Insertion, TCD, Source de données externe, choisir connexion, sélectionner la requête qui apparait tout en haut
• Placer Famille en champ de page (filtre de rapport) et le reste en étiquettes de ligne
• Utiliser la disposition Tabulaire, sans sous-totaux et désactiver l'affichage des boutons développer réduire dans le TCD, formater les champs (nombres), nommer le TCD et l'onglet
  
• VBA :

Sub MAJ()
'
    For Each Ws In ThisWorkbook.Worksheets
        Application.DisplayAlerts = False
        If Ws.Name <> "Menu" And Ws.Name <> "Base" And Ws.Name <> "A B" And Ws.Name <> "Tout" Then Ws.Delete
    Next Ws
        Application.DisplayAlerts = True
  
    With Worksheets("Tout").PivotTables("TCD_Tout")
        .PivotCache.Refresh
        .ShowPages PageField:="Famille"
    End With
End Sub

 

[ChTi160]

Re
merci Chris
je viens de télécharger "PowerQuery" lol arff ca me semble pas évident mais .... on va y arriver
KIM
Autre question , j'ai récupéré les données j'en fait quoi lol
donne moi un exemple .
tu dis le premier mot de la colonne C ,peux tu m'expliquer ?
merci
Bonne fin de Soirée
Amicalement
jean marie

 

[chris]

Re

Je réponds à la place de KIM : le premier morceau de désignation : ABC, KL...

Je ne maîtrise pas encore tout de PowerQuery, loin s'en faut (qui d'ailleurs le pourrait !) mais si je peux répondre à es questions pour faciliter les premiers pas...

 

[KIM]

Bonsoir Jean-Marie, Bonsoir Chris,
@chris, je regarderai demain ta solution, Merci

@jean-marie,
Dans la col C, "Désignation", je récupère le 1ier mot de chaque Ligne et je les regroupe dans un onglet du même nom que ce 1er mot avec les col B, C, F, K, O) ensuite rajouter une col F = E-(D-C).
voir exemple de l'onglet ABC dans le fichier ci-joint.
1- Si le nom d'un onglet du fichier RechercherColorierCondition_v1 autre que Menu et Base ne fait plus partie de la liste des 1iers mots extrait du fichier DetailsRef, il faut la supprimer
2- Est-il possible de paramétrer le nombre de mot à extraire pour créer les onglets et rassembler les données dans le fichier RechercherColorierCondition_v1 ?
3- Dès que les onglets détails sont créer dans le fichier RechercherColorierCondition_v1, j'appliquerai ta macro que tu as développée hier.

Merci d'avance
Amicalement
KIM

 

[ChTi160]

Bonjour KIM
Bonjour Chris,Le Fil ,Le Forum
Une autre question lol
KIM ! tu dis :

1- Si le nom d'un onglet du fichier RechercherColorierCondition_v1 autre que Menu et Base ne fait plus partie de la liste des 1iers mots extrait du fichier DetailsRef, il faut la supprimer

Cela revient il a dire que l'on supprime toutes les feuilles du Fichier RechercherColorierCondition_v1
sauf "Menu et Base" et que l'on crée les nouvelles feuilles en fonction des Noms récupérés dans le fichier "DetailsRef" ?
Pourquoi premiers mots d'ailleurs ?
Merci par avance
Bonne Journée
Amicalement
Jean marie

 

[KIM]

Bonjour Jean marie,
Bonjour Chris et le forum,
Merci Jean-Marie de ta patience.
En effet :
- Oui, Dans le fichier RechercherColorierCondition_v1 , on supprime tout sauf "Menu et Base" et on crée les nouvelles feuilles en fonction des Noms récupérés dans le fichier "DetailsRef"
- Dans la col "Désignation" du fichier "DetailsRef" on récupère le 1ier mot de la cellule (séparateur espace) comme nom de la feuille à créer dans RechercherColorierCondition_v1 et on recopie les données (valeurs, sans formules) de ce 1er mot col B, C, F, K, O) dans l'onglet et rajouter une col calculée col F = E-(D-C) :
Ex : Désignation = KL MN oppp X100
le nom de l'onglet sera "KL".
J'aurai besoin, pour d'autres tableau de bord de prendre les 2 mots comme nom de l'onglet "KL MN" par ex. Si ce choix n'est pas paramétrable, il suffit peut être de modifier la ligne dans le code qui détermine le nom de l'onglet (à condition de connaitre l'astuce).
Merci de ton aide
Bonne journée
Amicalement
KIM

 

[ChTi160]

Re
Ok , j'y retourne ! Lol
Bonne journée
Amicalement
Jean marie

 

[Si...]

Bonjour

Importer, filtrer, créer/supprimer onglets, comparer, colorier … autant de difficultés ajoutées que d'étapes.

Pourquoi ne pas se contenter d'un seul classeur comme dans l'exemple joint ?
Dans l'onglet Menu, j'ai ajouté la création d'une liste pour filtrer les produits du stock (1 seul critère pour l'instant).