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

IlyesT36

XLDnaute Nouveau
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

XLDnaute Accro
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

XLDnaute Nouveau
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

XLDnaute Accro
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

XLDnaute Accro
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

XLDnaute Nouveau
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

XLDnaute Accro
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 à :
Capture d’écran 2020-10-15 185329.png
(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

XLDnaute Nouveau
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
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 à :
Voir 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 ;)
 

IlyesT36

XLDnaute Nouveau
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

XLDnaute Nouveau
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

XLDnaute Accro
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

XLDnaute Nouveau
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

XLDnaute Nouveau
p
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

XLDnaute Accro
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
 

danielco

XLDnaute Accro
p

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 :)
Voici une image :

Capture d’écran 2020-10-16 150453.png
Daniel
 

IlyesT36

XLDnaute Nouveau
Pour power query, je suis arrivé à l'étape 9, mais la suite je ne la comprend pas vraiment :/
En ce qui concerne la macro, j'ai bien compris le fonctionnement, mais à partir d'où il faut l'entrer, quand tu parles de feuille active ? une fois rentré, il y a un bouton qui se mets en place ou un autre mécanisme va permettre de faire afficher les résultats ?
Enfin, ou vont apparaitre les résultats ?

Merci à toi
 

danielco

XLDnaute Accro
Le plus simple est d'utiliser la macro. Charge le classeur joint. Mets le chemin du classeur en H10 et le nom de la feuille en H11. Appuie sur le bouton. les résultats sont en colonne A (C20), B (C27) et C (C28).

Daniel
 

Fichiers joints

IlyesT36

XLDnaute Nouveau
Le plus simple est d'utiliser la macro. Charge le classeur joint. Mets le chemin du classeur en H10 et le nom de la feuille en H11. Appuie sur le bouton. les résultats sont en colonne A (C20), B (C27) et C (C28).

Daniel
Bonjour Daniel,
De retour au travail, je tente de faire ce que tu m'a dit, mais sur quel fichier il faut que j'entre ces informations ? Désolé, je ne suis pas un expert dans le domaine, je n'ai pas compris, en fait les infos que tu m'a donné sont bien visible en cellule en H10 et H11, mais ou est ce que je dois les mettre ? Dans chaque fichier excel ? Ou bien dans le dossier contenant les excel ?

Enfin, si les cellules sont amenés à changer (disons que sur certains dossiers les cellules ne seront apparemment plus les mêmes) est ce possible d'en faire la modification dans la macro ? Je pourrais te communiquer ces infos dans quelques heures, seulement si le changement a lieu , ce n'est pas encore sur.
Merci pour tes précisions.
 

Créez un compte ou connectez vous pour répondre

Vous devez être membre afin de pouvoir répondre ici

Créer un compte

Créez un compte Excel Downloads. C'est simple!

Connexion

Vous avez déjà un compte? Connectez vous ici.

Haut Bas