XL 2021 [Résolu] Récupérer informations dans plusieurs classeurs et les centraliser dans un autre

[hijackfr]

Bonjour,
et comme de coutume à cette époque, meilleurs vœux à tous.

J'ai dans un dossier Onedrive (on va dire qu'il se trouve dans OneDrive\Delta\Recups\2024) plusieurs classeurs tous avec la même architecture au niveau des feuilles composant le classeur :
- Config; Janvier; Février, etc jusqu'à décembre et une feuille annexe nommée Total. Je m'ens ers pour comptabiliser les heures de récupération faite.
Les classeurs sont nommés par exemple comme suit : Test1_Recup_2024.xlsm; Test2_Recup_2024.xlsm; etc

J'aimerais récupérer, de manière simple et automatique, le nombre d'heures faites chaque mois et qui se trouve en cellule J44 (le total d'heures d'un mois se reporte sur le mois suivant) et les transposer dans un tableau se trouvant dans un classeur autre et composé comme suit :

Personnes

Code agent

Janvier

Février

Mars

Avril

Mai

Juin

Juillet

Août

Septembre

Octobre

Novembre

Décembre

Test1

1

32:45

33:45

24:15

26:45

27:45

28:45

44:45

44:45

55:00

55:00

55:00

6:00

Test2

2

30:30

30:30

35:00

35:00

33:00

33:00

40:00

40:00

40:00

40:00

40:00

31:30

Test3

3

4:30

6:00

7:00

7:00

9:30

17:30

24:30

31:30

32:30

25:30

30:00

3:30

Test4

4

72:07

52:07

53:37

54:37

54:37

56:37

57:37

59:07

60:07

63:07

63:37

56:37

Test5

5

2:07

2:07

17:07

18:37

20:37

18:37

34:37

34:37

35:37

36:37

36:37

37:37

Test6

6

21:00

22:00

23:00

20:00

21:00

27:30

34:30

41:30

41:30

33:30

30:30

28:30

Pour l'instant, je remplis mon tableau en pointant à la main chaque cellule, donc chaque je galère à refaire ce tableau.

Merci de votre aide et des idées que vous auriez pour automatiser tout cela.

Cordialement,
Hijack

 

[nat54]

Bonjour,
@chris va te dire : utilise PowerQuery ^^

 

[chris]

Bonjour
Une proposition PowerQuery

Placer tous les fichiers dans un même dossier

Indiquer le chemin dans la cellule en jaune

Paramétrer PowerQuery pour gère ce chemin passé en paramétre : Lancer PowerQuery, Fichier, Options et paramètres, Options de requête, partie GLOBAL : Confidentialité, Toujours ignorer les paramètres de niveau de confidentialité. Fermer PowerQuery.

Actualiser le tableau résultant (clic droit ou Données, Actualiser tout)

J'ai récupéré le nom de l'agent dans le nom du fichier mais sinon à préciser

 

[Cousinhub]

Bonjour,
Bonjour Chris
[Comme je l'avais également fait, j'y ajoute ma version, sous PQ également]

@hijackfr
Je suis parti du postulat que tous les fichiers étaient dans le même répertoire (à renseigner dans la cellule A2 de l'onglet "Paramètres"), et que le nom des fichiers comportait le mot "Récup"
Il faut également configurer PQ comme l'a décrit Chris, et tout pareil, pour la mise à jour
Bonne découverte de PQ
Edit, ajout des fichiers ayant servi à la requête

 

[hijackfr]

Bonjour,

merci pour vos réponses. Je vais regarder cela et reviens vers vous.
Les fichiers se trouvent tous dans le même dossier, petit problème peut être c'est un dossier Ondrive, donc j'y accède par plusieurs pc situés sur des lieux géographiques différents;
Est ce gênant?

 

[Cousinhub]

Re-,
Pour obtenir l'adresse exacte de ton OneDrive, te propose :
- Ruban Données, Obtenir des données, A partir d'un fichier, A partir d'un dossier

Tu sélectionnes ton répertoire "OneDrive"
Puis "Transformer les données"
L'éditeur PQ va s'ouvrir, et dans la barre de formules, tu pourras voir l'adresse de ton OneDrive
Il te suffit de le copier dans la cellule dédiée, et si tous les PC sont connectés au même, normalement, ça devrait le faire (Attention, comme je n'ai pas OneDrive, je ne fais que supputer, regarde dans ce fil, le demandeur avait réussi en page2)
Bon courage

 

[hijackfr]

Ok, merci, je regarde cela.
D'abord je teste vos solutions

J'ai une erreur de ce type

L'adresse que j'obtiens pour le dossier Onedrive est :
= Folder.Files("C:\Users\***\OneDrive\Delta\Recups\2024") où *** est mon nom
Dans la case de ton fichier proposé, je ne colle pas telle quelle cette adresse je suppose

 

[chris]

RE

Avec OneDrive il ya normalement une synchro automatique entre le PC et le drive sauf si es sur une autre machine et dans ce cas c'est Office online qui risque de s'ouvrir et cette version limitée ne saura pas compiler

 

[hijackfr]

A priori, je suis une grosse truffe. Je galère
Modification :
@Cousinhub avec ton fichier, j'arrive enfin à avoir les données souhaitées.
Merci

Par contre il me renvoie dans le début de la feuille Excel des choses comme cela

Nom

Janvier

Février

Mars

Avril

Mai

Juin

Juillet

Août

Septembre

Octobre

Novembre

Décembre

Janvier24

Feuil1

0​

1.12:00:00​

0,041666667​

0.17:00:00​

0,041666667​

1.01:45:00​

0,208333333​

1.18:45:00​

0,291666667​

0.20:30:00​

0,333333333​

1.11:30:00​

0,375​

1.23:30:00​

0,4375​

0.11:30:00​

0,541666667​

0.09:30:00​

0,572916667​

0.19:45:00​

0,579861111​

1.10:55:00​

0,625​

0.16:00:00​

0,875​

2.01:00:00​

0,958333333​

1.10:00:00​

1,104166667​

1.20:00:00​

1,1875​

1.06:30:00​

1,3125​

2.15:00:00​

1,416666667​

2.00:30:00​

1,5​

1.15:00:00​

1,567361111​

3.03:14:00​

1,625​

3.06:00:00​

1,729166667​

3.11:00:00​

2,114583333​

3.08:45:00​

2,166666667​

4.08:00:00​

2,359027778​

4.17:14:00​

2,725694444​

2.15:55:00​

3​

3.18:00:00​

Plus un nom marqué Mars, alors que dans le dossier je n'ai que les noms de personnes.

j'ai loupé quelque chose?

 

[Cousinhub]

Re-,
Euh, je viens de voir que tu avais modifié ton message de 13:55 à 14:20, ce qui fait que ni Chris (je suppose), ni moi n'avons vu ta dernière...
Privilégie un nouveau message
Donc, réponse demain?
Et dans tes réponses, précise sur quel fichier tu travailles, ça sera plus simple pour nous (à priori, sur celui de Chris)
As-tu essayé avec le mien?
Si bug, mets également des captures d'écran (de l'étape d'avant, qui fonctionne, et de l'étape en défaut)
T'inquiète, on va y arriver
Bonne soirée

 

[Cousinhub]

Re-,
Tu as encore modifié ton post....
Evite, ça fout le souk...
On voit ça demain...

 

[hijackfr]

Ok, désolé, pas de problème pour demain.
En plus des problèmes remontés dans mon dernier post (je travaille actuellement sur le tien, celui de Chris je n'y arrive pas) je rencontre un autre souci.
J'ai deux personnes dans la liste qui porte le même nom mais que je nommais comme suit:
- test.I_Recup_2024.xlsm
- test.O_Recup_2024.xlsm
Le fichier final ne prend en compte qu'un nom, test, et ajoute leurs heures.
Si je renomme les deux agents dans mon dossier 2024, je réactualise le feuille 2024 de ton fichier, les noms sont pris en compte mais pas dans la feuille Final

 

[Cousinhub]

Bonjour,
Dans tes fichiers perso, tu as une cellule prévue pour le prénom, mais qui renvoie à une cellule de l'onglet "Config", colonne A, et qui comporte les noms...
Tu es sûr de l'exacte structure de tes fichiers exemple?
Et dans ton post 9, je ne comprends pas du tout pourquoi tu as des heures à la place des noms (0,041666667 correspond à 1 heure)
A l'étape "Filtr2", est-ce que tu as bien quelque chose comme ça? (dans cet exemple, j'ai modifié le filtre dans la barre de formule, dans ton fichier, tu auras un filtre sur "Total heures <> null" (ça revient pratiquement au même, sauf s'il n'y a pas de formules en J44 d'un fichier quelconque)

Bonne journée

 

[hijackfr]

Bonjour,
pour le nom qui renvoie à la cellule de l'onglet "Config", j'avais une configuration disparate pour un fichier, le tableau comportant le nom et prénom avait plusieurs noms. J'ai rectifié, à priori c'était cela.
Merci encore.

Pour mon information personnelle et mon apprentissage, peux tu me détailler stp pas à pas comment tu as construit ton fichier?
La feuille Config, ok; la feuille 2024 je pense avoir compris, mais je ne saisis pas encore comment obtenir la feuille Final

 

[Cousinhub]

Re-,
J'ai un peu modifié le code :
- Non prise en compte du raccourci qui se crée lorsqu'un fichier est ouvert
(A savoir que si des modifications y sont apportées, elles ne sont prises en compte que si le fichier est enregistré)
- Filtre sur la ligne comportant "Heures restantes" (donc la ligne 44, normalement)
Pour mettre à jour dans ton fichier, tu copies ce code, tu ouvres la requête, puis dans l'éditeur avancé, tu remplaces l'ancien code par celui-ci.
Dans le langage M (langage de PQ), les commentaires sont précédés d'un double slash (en rouge ici)

let
    Source = Folder.Files(Dossier),
    // Définit le répertoire contenant les fichiers à traiter
    Filtr0 = Table.SelectRows(Source, each Text.Contains([Name], "Recup")  and not Text.StartsWith([Name], "~")),
    // On fait un filtre sur les fichiers dont le nom contient "Récup"
    // Si un de ces fichiers est ouvert, il y a alors un raccourci commençant par ~ , qu'on ne prend donc pas en compte
    Transform = Table.TransformColumns(Filtr0, { "Content", each Excel.Workbook(_, true )})[[Content]],
    // On transforme tous les fichiers en Table, et on ne conserve que la colonne Content
    Expand0 = Table.ExpandTableColumn(Transform, "Content", {"Name", "Data", "Item", "Kind"}, {"Name", "Data", "Item", "Kind"}),
    // On développe les fichiers afin d'avoir la liste des onglets, noms définis, Tableaux...
    Filtr1 = Table.SelectRows(Expand0, each ([Kind] = "Sheet") and ([Item] <> "Config" and [Item] <> "Total"))[[Item],[Data]],
    // On fait un filtre afin de ne conserver que les onglets ([Kind] = "Sheet") et dont le nom est différent de Config et Total
    // On ne conserve que les colonnes Item (pour avoir le nom des mois) et Data (contenant les données des onglets)
    Expand1 = Table.ExpandTableColumn(Filtr1, "Data", {"Column4", "Column5", "Column10"}, {"Column4", "Nom", "Total Heures"}),
    // On développe les Tables (et renomme la Column5 en Nom et Column6 en Total Heures)
    Filtr2 = Table.SelectRows(Expand1, each ([Column4] = "Nom :") or ([Nom] = "Heures restantes")),
    // On ne conserve que les lignes utiles : celle contenant le nom et la dernière ligne contenant la valeur de la cellule J44
    RemplHaut = Table.FillUp(Filtr2,{"Total Heures"}),
    // On remplit vers le haut, afin d'avoir toutes les données sur 1 ligne
    ModType = Table.TransformColumnTypes(RemplHaut,{{"Total Heures", type duration}}),
    // Transformation des heures en type Duration (nécessaire si somme supérieure à 24H)
    Filtr3 = Table.SelectRows(ModType, each ([Nom] <> "Heures restantes")),
    // Comme on avait les données en double, on ne conserve qu'une ligne
    SupprCol1 = Table.RemoveColumns(Filtr3,{"Column4"}),
    // On peut supprimer la Column4, qui ne contenait que l'information Nom :
    Pivot = Table.Pivot(SupprCol1, List.Distinct(SupprCol1[Item]), "Item", "Total Heures", List.Sum)
    // C'est ici que tout se passe. On pivote la colonne Item correspondant aux mois
    // Et on additionne toutes les heures pour chaque employé
    // Le format est donc en type duration J.HH.MM.SS, et lors du transfert dans la requête sur l'onglet
    // Il suffit d'adopter le format horaire [hh]:mm:ss dans les cellules pour avoir le total, même si >24H
in
    Pivot

Bonne découverte...
Et bon W-E