Importer une donnée d'un autre classeur portant le nom d'une cellule donnée

Bonjour,

Je travaille actuellement pour une association qui propose du cinéma pour les écoles et j'essaye de rénover leur système d'inscription.

J'ai peu de temps donc je dois utiliser l'existant.

J'ai 250 fichiers excel identiques à faire remplis d'informations basiques.

Je souhaite récupérer ces informations et les regrouper dans un tableau général, moins sexy mais plus pratique pour compiler les données.

L'idée est d'extraire certaines données pour remplir une ligne portant le nom de l'école.

Exemple : Ecole / Adresse / tel / nombre élève / film / ...

J'utilise actuellement : ='\\Chemin d'accès\[Nom_Ecole.xlsx]Fiche inscription'!$H$4 (par exemple)

Et j'arrive facilement à récupérer les données de mon école si le fichier excel se nomme "Nom_Ecole".

Sauf que... J'ai 250 écoles à traiter et cela veut dire que je dois remplir chaque formule à la main pour chaque colonne.

Alors j'ai eu une idée, toute ma colonne A contient les noms des écoles. L'idée serait de pouvoir faire ='\\Chemin d'accès\[A4.xlsx]Fiche inscription'!$H$4 et ensuite de faire défiler pour que tout se fasse automatiquement.

Car ma "fiche inscription" a la même structure pour toutes les écoles. Seul change le nom du fichier et le contenu des cellules.

Si quelqu'un a une idée je suis preneur.

Merci beaucoup pour votre temps et belle journée !

PS : je ne peux pas donner accès aux fichiers il y a des données sur des écoles et mon asso. En comptant sur votre compréhension.

Bonjour HogoFol,

Tu peux peut-être envoyer un "exemple" de petit fichier anonymisé ... !

Le fichier Gestion inscription doit recevoir à chaque ligne les informations des 200 fichiers écoles.

Un fichier école type est comme celui de l'école adelin malgrand.

Bonjour HugoFol

Merci pour le fichier... Ce sera plus simple pour commencer à t'aider !

Je regarde tes PJ et je reviens...

Bonjour HugoFol

J'ai regardé tes PJ...
J'aurais besoin de précision quant aux donnes que tu veux "lire" dans les fichiers "écoles" te l'endroit où elles doivent être placées dans ton fichier de récapitulation

PS/ Au besoin en MP, si cela te parait plus plus simple !

Bonjour HugoFol

J'ai fait quelques tests... Actuellement pour lire 10 "écoles" environ 5 secondes...
Cependant que je n'ai pas vraiment compris les données à lire pour réaliser la "copie" dans le fichier de récapitulation...

Pour l'instant

1. Je récupère le Nom, Mail, Tel du référent, le total pour chaque film de chaque cycle
2. Je copie ces infos en face de chaque école

La lecture et/ou la copie d'autres informations ne devraient pas changer le chrono actuel !
Reste à savoir maintenant ce que tu veux réellement lire et copier !

Bonjour,

Merci pour ton temps et ton aide.

J'ai sans doute mal précisé mon but. Nous allons disposer d'environ 250 fichiers excel d'inscription des écoles. Je compte mettre les 250 fichiers dans un même dossier et faire appel aux données des ces fichiers dans un immense tableau récapitulatif.

Pour appeler les données dans mon immense tableau j'utilise ='\\Chemin\[fichier.xlsx]Fiche inscription'!$Celulle

C'est toujours les mêmes cellules qui contiennent les informations (téléphone, nom, email,...) dans chaque fichier d'inscription donc toutes mes lignes de mon grand tableau sont quasiment identiques en terme de formule. Seul change le nom du fichier.

Du coup j'ai eu l'idée d'appeler les fichiers comme les écoles et de pré-rentrer les formules en attendant de recevoir les fichiers d'inscription.

Ce qui donnerait cela :

Je voudrais pouvoir trouver une formule "magique" si c'est possible pour dire à excel :

Dans les cellules A1 à A250 ya mes 250 nom d'école et du coup nom de fichiers.

J'aimerais importer ces noms dans ma formule ='\\Chemin\[A1.xlsx]Fiche inscription'!$Celulle1 et que A1 renvoi le nom de l'école et donc du fichier. Ainsi j'utilise le système de duplication automatique de Excel et le tour est joué.

Mais je ne sais pas si c'est possible.

Bonjour HugoFol,

A mon avis la méthode l'utilisation d'un liens vers chaque "fiche inscription école" n'est pas une bonne idée !

Je pense qu'il est plus adapté

• d'ouvrir chaque "fiche inscription école",
• de lire les informations "utiles",
• de refermer la fiche
• intégrer les informations "utiles" dans la récapitulation

Tout cela forcément, de manière automatique via VBA
C'est en tout cas, la solution que j'ai utilisé !

Reste à préciser maintenant quelles sont les données exactes à récupérer (en terme de références de cellules) sur chaque fiche ET l'endroit où elles doivent être placées sur le fichier de récapitulation.

Bonjour,

Je pensais pouvoir éviter de passer par du VBA mais si tu penses que mon idée n'est pas faisable ce sera sans doute la meilleure solution.

Les informations que je dois récupérer dans chaque fichier inscription :

Le cinéma choisi par l'école / le mail de l'école / le nom du référent / le numéro du référent / le mail du référent / les horaires de l'école / les jours où elle ne souhaite pas de projection cinéma / Le nombre de classe pour chaque cycle (I II III) / le nombre d'élève pour chaque cycle (I II III) / Le nombre d'élève par film (3 films par période) par période (3 périodes).

J'affiche tout cela dans mon tableau qui a pour première colonne le nom de l'école puis des colonnes sur la localité de l'école (car des fois l'école ne choisit pas de cinéma et on peut lui proposer le plus proche).

Tout ce que j'ai besoin de récupérer ce trouve dans la feuille "inscription globale" du classeur. Pour les feuilles "cinema" et "séances" ce sera du traitement de données pour plus tard.

Bonjour HugoFol,

Je n'aime pas trop utiliser les liens... ils sont souvent très "gourmand" en ressources (processeur et mémoire), de plus ils ne peuvent pas palier à l'absence éventuelle du fichier vers lequel ils pointent.
Et dans ton cas 250 fois ces inconvénients seraient la source potentielle, de pas mal déboires.
L'avantage de passer par VBA dans ce cas et (ton cas) c'est la rapidité, la possibilité de vérifier l'existence du fichier et tout cela presque instantanément... !

Maintenant que j'ai approximativement les infos à récupérer, je vais voir si j'arrive à trouver où les placer...

Bonjour,

Tu as raison, j'ai commencé à me pencher sur une solution sur VBA.

Merci beaucoup pour tes conseils, je ne veux pas te forcer à continuer à m'aider.

Bonjour HogoFol

Je ne t'abandonne pas... et je ne me sens pas "forcé" de t'aider, j'ai juste un peu de retard

Je te prépare une première ébauche et en fonction des éléments que j'ai actuellement (cf post d'hier) dans le courant de la journée

Bonjour HugoFol,

Avec un peu de retard, voici où j'en suis de mes cogitations sur ton fichier

1- Je sais récupérer les informations de chaque école (mail, infos du référent...) PS/pas de mail école c'est normal je n'ai rien mis dans ce champ

2- Je sais aussi récupérer les choix de non-projection, le nombre de classes par cycle

3- Maintenant quelques questions (sur cette 3ème figure)

1. La zone total jaune est bien la somme des colonnes vertes ?
2. La zone total orange est bien la somme des colonnes bleues ?

Pourrais-tu m'envoyer une "fiche-école exemple" dans laquelle tu colories les autres données à récupérer, ainsi que le fichier de récapitulation dont les colonnes colorées indiquent la position des données lues dans la "fiche-école" ?

...

Je poursuis mes investigations et questions...

Confirmes-tu dans ces figures, la concordance des colonnes "fiche-école" vs "récapitulation" ?

Bonjour HugoFol

J'ai amélioré le temps d'exécution, actuellement 10 fiches/secondes, soit cas réel (avec tes 250 écoles) environ 25 secondes !

Mais j'attends ta confirmation sur mes dernières questions !

Bonjour,

Désolé pour mes réponses tardives. Je me suis mis à fond dans VBA pour faire mes petits tests.

Actuellement ça fonctionne pas mal :

Dim Fichier As String
Dim Chemin As String
Dim Ecole As Workbook
Dim Decole As Worksheet
Dim Inscription As Workbook
Dim Dinscription As Worksheet
Dim Nomvide As Boolean
Dim Index As Integer
Dim Projection As String

Application.ScreenUpdating = False 'masque les rafraîchissements d'écran
Set Inscription = ThisWorkbook 'définit le classeur destination Ecole et cinéma
Set Dinscription = Inscription.Sheets(1) 'définit la feuille de destination inscription globale
Nomvide = False

Index = 4 'le nom des écoles commence en A4
Chemin = "\\FOL-PROFILS.folannecy.local\Redirections$\hugo_eb\Documents\collège au cinema 2022-2023\Fiches écoles\" 'chemin d'accès au fichier école
Fichier = Dir(Chemin & Dinscription.Range("A" & CStr(Index)).Value & ".xlsx")

Do While Nomvide = False 'Tant que les cellules nom d'école ne sont pas vides on analyse les fichiers

If Len(Fichier) > 0 Then 'on vérifie si le fichier de l'école existe bien

'on supprime les données dans les cellules sources pour eviter les doublons
Dinscription.Range("E" & CStr(Index) & ":O" & CStr(Index)).ClearContents 
Dinscription.Range("Q" & CStr(Index) & ":S" & CStr(Index)).ClearContents
Dinscription.Range("U" & CStr(Index) & ":W" & CStr(Index)).ClearContents
Dinscription.Range("Z" & CStr(Index) & ":AB" & CStr(Index)).ClearContents
Dinscription.Range("AB" & CStr(Index) & ":AE" & CStr(Index)).ClearContents
Dinscription.Range("AE" & CStr(Index) & ":AH" & CStr(Index)).ClearContents
Dinscription.Range("AH" & CStr(Index) & ":AJ" & CStr(Index)).ClearContents

Application.Workbooks.Open (Chemin & Fichier) 'ouvre le fichier portant le nom de l'école
Set Ecole = ActiveWorkbook 'définit le classeur source avec les donnees de l'école
Set Decole = Ecole.Sheets(1) 'définit la feuille qui contient les données de l'école

Decole.Range("C4").Copy 'on copie le nom de cinéma choisi
Dinscription.Range("E" & CStr(Index)).PasteSpecial (xlPasteValues) 'on le met dans le tableau de données

Decole.Range("C8").Copy 'on copie les informations personnelles de l'école
Dinscription.Range("F" & CStr(Index)).PasteSpecial (xlPasteValues) 'on le met dans le tableau de données

Decole.Range("C9").Copy 'on copie les informations personnelles de l'école
Dinscription.Range("G" & CStr(Index)).PasteSpecial (xlPasteValues) 'on le met dans le tableau de données

Decole.Range("C10").Copy 'on copie les informations personnelles de l'école
Dinscription.Range("H" & CStr(Index)).PasteSpecial (xlPasteValues) 'on le met dans le tableau de données

Decole.Range("C11").Copy 'on copie les informations personnelles de l'école
Dinscription.Range("I" & CStr(Index)).PasteSpecial (xlPasteValues) 'on le met dans le tableau de données

Decole.Range("H4").Copy 'horaire ouverture ecole
Dinscription.Range("J" & CStr(Index)).PasteSpecial (xlPasteValues)

Decole.Range("J4").Copy 'horaire fermeture ecole
Dinscription.Range("K" & CStr(Index)).PasteSpecial (xlPasteValues)

Decole.Range("H6").Copy 'horaire ecole mercredi
Dinscription.Range("L" & CStr(Index)).PasteSpecial (xlPasteValues)

Projection = Decole.Range("G9").Value & " " & Decole.Range("I9").Value 'pas de projection voulu 1
Dinscription.Range("M" & CStr(Index)).Value = Projection

Projection = Decole.Range("G10").Value & " " & Decole.Range("I10").Value 'pas de projection voulu 2
Dinscription.Range("N" & CStr(Index)).Value = Projection

Projection = Decole.Range("G11").Value & " " & Decole.Range("I11").Value 'pas de projection voulu 3
Dinscription.Range("O" & CStr(Index)).Value = Projection

Decole.Range("D44").Copy 'nombre classe cycle I
Dinscription.Range("Q" & CStr(Index)).PasteSpecial (xlPasteValues)

Decole.Range("F44").Copy 'nombre classe cycle II
Dinscription.Range("R" & CStr(Index)).PasteSpecial (xlPasteValues)

Decole.Range("H44").Copy 'nombre classe cycle III
Dinscription.Range("S" & CStr(Index)).PasteSpecial (xlPasteValues)

Decole.Range("D25").Copy 'nombre élève cycle I
Dinscription.Range("U" & CStr(Index)).PasteSpecial (xlPasteValues)

Decole.Range("D33").Copy 'nombre élève cycle II
Dinscription.Range("V" & CStr(Index)).PasteSpecial (xlPasteValues)

Decole.Range("D41").Copy 'nombre élève cycle III
Dinscription.Range("W" & CStr(Index)).PasteSpecial (xlPasteValues)

Ecole.Close SaveChanges = False 'ferme le fichier de l'école sans enregister les changements

End If

Index = Index + 1
Fichier = Dir(Chemin & Dinscription.Range("A" & CStr(Index)).Value & ".xlsx")

If Dinscription.Range("A" & CStr(Index)).Value = "" Then Nomvide = True 'Si la cellule suivante est vide on arrête la boucle

Loop

Inscription.Save 'enregistre le fichier destination contenant les données
Application.ScreenUpdating = True 'affiche les rafraîchissements d'écran
Text1 = "Fin du traitement des données !"

C'est peut être pas le code le plus beau et optimisé du monde mais j'arrive très bien à récupérer les informations dans mon classeur.

J'attends juste de mon collègue qu'il renomme un fichier reçu sous le nom de l'école lorsqu'il le reçoit par email.

Pour répondre à tes posts avec les images :

1) Oui pour les totaux des couleurs

2) Oui pour la concordance ligne collone avec le orange bleu et vert

Bonjour HugoFol,

Bravo pour le code !
J'ai utilisé quant à moi une approche un peu différente, qui je pense devrais être plus rapide en temps d'exécution. Je t'explique la méthode par la suite. Je dois pour l'instant intégrer tes "confirmations", je te joints une démo de l'état actuel de la réalisation...

(en MP)

Bonne visualisation, dis-moi ce que tu en penses !

Merci pour tes retours !
Je vais mettre le sujet en résolu, cela aidera, je l'espère, d'autres personnes

Bonjour HugoFol,

Merci à toi pour

Merci pour tes retours !

et ce traitement sympathique de ton besoin !