Microsoft 365 Récupérer valeurs dans une cellule de plusieurs fichiers Excel situés dans dossier partagé d'entreprise

IlyesT36 · 15 Octobre 2020

Bonjour,

Etant tout nouveau sur ce forum, et novice en la matière, je publie aujourd'hui un message dont le sujet existe déjà sur ce site, mais pour lequel je ne comprend pas, ou bien les solutions ne sont pas forcément adapté à ma situation.

Dans le dossier partagé de mon entreprise, il existe un dossier contenant environ 1000 fichiers Excel, avec des formats de nom similaire, mais dont la longueur du titre varie. Dedans, chaque fichier Excel est composé d'une feuille d'environ 100 lignes, et quelques colonnes seulement.

L'objectif est le suivant : récupérer la valeur de 3 cellules environ par feuille, et donc par fichier Excel, et mettre les valeurs correspondantes à ces cellules dans un nouveau Excel, ou Word, ou même bloc note. Faire cette action pour les 1000 fichiers Excel, tout situés dans le même dossier partagé. Je dois donc récupérer 3 valeurs type "nom", "date" et "pays" par Excel, sois environ 3000 valeurs réparties sur 3 colonnes, ou bien de façon brute si c'est plus simple.

Le problème c'est que je n'ai jamais fait de VBA ou VBS, je n'y connais rien, j'essaie d'apprendre au fur et à mesure, et lire des documents dessus mais c'est assez long, il faut de la pratique, et je n'ai pas énormément de temps. Je sais qu'il faut faire une macro afin d'automatiser cette tache via une macro (toute autre solution sera la bienvenue), mais je sais pas comment cela fonctionne. Est ce que vous pourriez bien m'en expliquer la création ainsi que la solution de mon problème ? De ce fait, je pourrais apprendre de cette expérience et m'améliorer.

Je vous remercie par avance pour vos messages et le temps que vous avez pris pour lire ce poste.
Ilyes

danielco · 15 Octobre 2020

Bonjour,

Est-ce que ces trois valeurs sont dans les mêmes cellules dans tous les fichiers ? Indique lesquelles, ou indique de quelle façon les trouver. Est-ce que tous les fichiers de ce dossier sont à traiter. Sinon, comment identifier ceux à traiter. Est-ce que chaque classeur est composé d'une seule feuille ? Dans le cas contraire, indique le nom de la feuille à traiter.

Cordialement.

Daniel

IlyesT36 · 15 Octobre 2020

danielco à dit:
Bonjour,

Est-ce que ces trois valeurs sont dans les mêmes cellules dans tous les fichiers ? Indique lesquelles, ou indique de quelle façon les trouver. Est-ce que tous les fichiers de ce dossier sont à traiter. Sinon, comment identifier ceux à traiter. Est-ce que chaque classeur est composé d'une seule feuille ? Dans le cas contraire, indique le nom de la feuille à traiter.

Cordialement.

Daniel

Bonjour Daniel, et merci pour votre retour.

Je vais tenter de vous détailler l'architecture de mon dossier principal : il est composé de 3 dossiers, dans ces 3 dossiers, il y respectivement 100 Excels, 200 Excels et 600 Excels.

Dans le premier dossier d'Excel, les valeurs des cellules à récupérer sont dans les cellules suivantes : C12 C18 A205 ET C17.
Dans le second dossier d'Excel, les valeurs des cellules à récupérer sont dans les cellules suivantes : C12 C18 A173 ET C17
Dans le troisième dossier d'Excel, les valeurs des cellules à récupérer sont dans les cellules suivantes : C20 C27 ET C28.

Je m'étais trompé, il y a 3 à 4 valeurs à récupérer par fichier, en fonction des dossiers, dont le détail vient de vous être expliqué.

Même si la macro est "coupée" en 3, ou bien qu'il faudra faire 3 macros différentes, je n'ai aucun problème ou aucune objection dessus, cela me conviendra. Pareil s'il s'agit d'une solution dite "graphique". Tant que les données à récupérer sont faites de façon quasi-automatique, et permettent d'éviter une répétition 1000 fois pour 1000 Excels alors c'est très bien.

Effectivement, tous les fichiers de tous les dossiers sont à traiter. Oui, chaque classeur est composé d'une seule feuille.

Je vous remercie pour le temps que vous y accorderez.
Bien cordialement, Ilyes

danielco · 15 Octobre 2020

Ca se fait (assez) facilement avec Power Query (Récupérer et transformer). l'explication détaillée est assez longue. Il me faut un peu de temps pour expliquer une des trois opérations. je la poste dès que possible.

Daniel

danielco · 15 Octobre 2020

Est-ce que tu connais Power Query ? Parce que, sinon, les explications vont être longues. j'ai commencé ci-desous mais j'ttends ta réponse pour savoir jusqu'à quel degré de détail je dois descendre.

Pour le dossier des C20 C27 ET C28 :

1. Clique sur l'onglet Données, Nouvelle requête, A partir d'un fichiier, A partir d'un dossier.
2. Entre le chemin du dossier et valide.
3.

Daniel

IlyesT36 · 15 Octobre 2020

danielco à dit:
Est-ce que tu connais Power Query ? Parce que, sinon, les explications vont être longues. j'ai commencé ci-desous mais j'ttends ta réponse pour savoir jusqu'à quel degré de détail je dois descendre.

Pour le dossier des C20 C27 ET C28 :

1. Clique sur l'onglet Données, Nouvelle requête, A partir d'un fichiier, A partir d'un dossier.
2. Entre le chemin du dossier et valide.
3.

Daniel

Merci beaucoup pour le début de réponse ! Non je ne le connais que de nom, mais si tu m’expliques je comprendrai, en général j’apprend assez vite

merci à toi !
cordialement

danielco · 15 Octobre 2020

Donc :
3. Clique sur la flèche du bouton "Combiner" et sur "Combiner et transformer les données".
Dans la fenêtre "Combiner les fichiers qui s'ouvre", conserve "Premier fichier" et clique sur la feuille contenant les données. Valide.
4. L’éditeur Power Query s'ouvre (après avoir analysé tes classeurs, donc ça peut prendre du temps), Dans la fenêtre des requêtes, fais un double clic sur "Transformer l'exemple de fichier".
5. (supprimé)
6. Clique sur l'onglet "Ajouter une colonne". Clique sur la flèche de "Colonne d'index" et sur "A partir de 1".
7. Clique sur "Colonne conditionnelle". En ajoutant les éléments, tu dois arriver à :

(en cliquant deux fois sur "Ajouter une clause". Valide.
8. Filtre les valeurs "1".
9. Clique sur la requête ayant le nom de ta colonne
10. Supprime les colonnes Index et personnalisée, Source.Name, Column1, Column2.
11. Clique sur l'onglet Fichier et sur Fermer et "Charger dans" et "Table"
Fin.

Daniel

IlyesT36 · 15 Octobre 2020

danielco à dit:
Est-ce que tu connais Power Query ? Parce que, sinon, les explications vont être longues. j'ai commencé ci-desous mais j'ttends ta réponse pour savoir jusqu'à quel degré de détail je dois descendre.

Pour le dossier des C20 C27 ET C28 :

1. Clique sur l'onglet Données, Nouvelle requête, A partir d'un fichiier, A partir d'un dossier.
2. Entre le chemin du dossier et valide.
3.

Daniel

danielco à dit:
Donc :
3. Clique sur la flèche du bouton "Combiner" et sur "Combiner et transformer les données".
Dans la fenêtre "Combiner les fichiers qui s'ouvre", conserve "Premier fichier" et clique sur la feuille contenant les données. Valide.
4. L’éditeur Power Query s'ouvre (après avoir analysé tes classeurs, donc ça peut prendre du temps), Dans la fenêtre des requêtes, fais un double clic sur "Transformer l'exemple de fichier".
5. (supprimé)
6. Clique sur l'onglet "Ajouter une colonne". Clique sur la flèche de "Colonne d'index" et sur "A partir de 1".
7. Clique sur "Colonne conditionnelle". En ajoutant les éléments, tu dois arriver à :
Regarde la pièce jointe 1081634
(en cliquant deux fois sur "Ajouter une clause". Valide.
8. Filtre les valeurs "1".
9. Clique sur la requête ayant le nom de ta colonne
10. Supprime les colonnes Index et personnalisée, Source.Name, Column1, Column2.
11. Clique sur l'onglet Fichier et sur Fermer et "Charger dans" et "Table"
Fin.

Daniel

Super ! Je te remercie beaucoup, je vais essayé cette solution dans la soirée en rentrant chez moi en essayant de bien comprendre la démarche et je reviens vers toi d’ici à demain dans la matinée.
Merci encore

danielco · 15 Octobre 2020

N'hésite pas à demander des précisions si tu as un problème.

Daniel

IlyesT36 · 16 Octobre 2020

danielco à dit:
N'hésite pas à demander des précisions si tu as un problème.

Daniel

Bonjour Daniel,

Je reviens vers toi, l'environnement de power query est plutôt simple d'utilisation, donc niveau explication technique, pas de soucis. En revanche, au niveau de la 3ème étape, un message d'erreur apparait : Connexion, Nous avons rencontré une erreur lors de la tentative de connexion. "Détails : « La table externe n'est pas dans le format attendu. »".

Etrange, car le chemin du dossier en question a bien été validé, mais la connexion semble impossible. Serais-tu à quoi ce problème est dû, une idée ?

Merci de ton aide.
Ilyes

IlyesT36 · 16 Octobre 2020

danielco à dit:
N'hésite pas à demander des précisions si tu as un problème.

Daniel

Serait-ce lié à la version d'Excel qui a été utilisé au moment de créer tous ces fichiers ? J'avais oublié de préciser que les fichiers Excel en question sont de la version Microsoft Excel 97-2003 (.XLS); des fichiers créer il y a plus de 20 ans environ.

danielco · 16 Octobre 2020

Là, je n'en sais rien. Il y a des spécialistes Power Query sur ce forum. Il est aussi possible que le nombre de classeurs soit aussi en cause. Fais un essai dans un dossier de test avec quelques classeurs. Tu devrais aussi ouvrir une autre discussion en précisant bien POWER QUERY dans le titre et dans le champ "Tags". Sinon, je peux regarder une solution par macro.

Daniel

IlyesT36 · 16 Octobre 2020

danielco à dit:
Là, je n'en sais rien. Il y a des spécialistes Power Query sur ce forum. Il est aussi possible que le nombre de classeurs soit aussi en cause. Fais un essai dans un dossier de test avec quelques classeurs. Tu devrais aussi ouvrir une autre discussion en précisant bien POWER QUERY dans le titre et dans le champ "Tags". Sinon, je peux regarder une solution par macro.

Daniel

Merci pour ton retour,

Aucun souci, j'essaye et reviens vers toi rapidement. Pour la macro, je suis également prêt, tant que la solution est fonctionnelle, toute piste est envisageable et me conviendra.
Ilyes

IlyesT36 · 16 Octobre 2020

p

danielco à dit:
Là, je n'en sais rien. Il y a des spécialistes Power Query sur ce forum. Il est aussi possible que le nombre de classeurs soit aussi en cause. Fais un essai dans un dossier de test avec quelques classeurs. Tu devrais aussi ouvrir une autre discussion en précisant bien POWER QUERY dans le titre et dans le champ "Tags". Sinon, je peux regarder une solution par macro.

Daniel

petit update : J'ai déplacer la fichiers dans un autre dossier, environ 20 excel, et cela fonctionne. Je suis maintenant à l'étape 4, mais je ne suis pas sur de faire la bonne matip, car certaines sections n'apparaissent pas comme tu l'a écrit, j'ai l'impression ... Si tu as des captures écrans ce serait parfait je pense

danielco · 16 Octobre 2020

Pour les cellules C20, C27 et C28, essaie :

VB:

Sub C20C27C28()
  Dim Chemin As String, Fich As String, Ligne As Long
  Application.ScreenUpdating = False
  Chemin = "C:\Users\dcola\OneDrive\mpfe\aaa"
  Fich = Dir(Chemin & "\*.*")
  Do While Fich <> ""
    Ligne = Ligne + 1
    Cells(Ligne, 1).Value = ExecuteExcel4Macro("'" & Chemin & "\[" & Fich & "]Feuil1'!R20C3")
    Cells(Ligne, 2).Value = ExecuteExcel4Macro("'" & Chemin & "\[" & Fich & "]Feuil1'!R27C3")
    Cells(Ligne, 3).Value = ExecuteExcel4Macro("'" & Chemin & "\[" & Fich & "]Feuil1'!R28C3")
    Fich = Dir
  Loop
  Application.ScreenUpdating = True
End Sub

La macro écrit sur la feuille active.

Daniel

Microsoft 365 Récupérer valeurs dans une cellule de plusieurs fichiers Excel situés dans dossier partagé d'entreprise

IlyesT36

XLDnaute Nouveau

danielco

XLDnaute Accro

IlyesT36

XLDnaute Nouveau

danielco

XLDnaute Accro

danielco

XLDnaute Accro

IlyesT36

XLDnaute Nouveau

danielco

XLDnaute Accro

IlyesT36

XLDnaute Nouveau

danielco

XLDnaute Accro

IlyesT36

XLDnaute Nouveau

IlyesT36

XLDnaute Nouveau

danielco

XLDnaute Accro

IlyesT36

XLDnaute Nouveau

IlyesT36

XLDnaute Nouveau

danielco

XLDnaute Accro

Discussions similaires