Extraction de valeurs de différents classeurs

Bonjour à vous,

Je suis bloqué sur un problème excel à mon travail, et sa résolution me ferait gagner beaucoup de temps ! J'ai essayé diverses choses sans succès, j'en viens donc à vous demander votre aide

J'accompagne différents patients, sur plusieurs mois. Pour chacun de ces patients j'ai un tableau sur lequel je note les différentes actions effectuées, ainsi que la date correspondante. J'ai une cellule en D3 qui donne comme valeur la date la plus récente.
J'utilise également untableau récapitulant l'ensemble des patients, ligne par ligne, et j'aimerais faire afficher automatiquement la valeur D3 du tableau correspondant, sans avoir à sélectionner manuellement le tableau à chaque nouveau patient.
En clair, je souhaiterais une formule qui aille chercher, en fonction du nom et du prénom de l'usager, la valeur D3 du classeur correspondant.
Vous trouverez en PJ un exemple éclairci des deux tableaux.

Pour informations :
- A chaque nouveau patient je créé un dossier patient, nommé "NOM prénom"
- Dans ce dossier se trouve le classeur où je note les différentes actions, nommé "Actions NOM Prénom.xlsx"
- L'adresse menant à un classeur patient se nomme "\\10.120.186.1\fs-0301\Utilisateurs\usagers\EQLAAT Ici\Usagers\Dossiers\Actions MARTIN Jean.xlsx" pour l'usager se prénommant par exemple MARTIN Jean.
- L'intitulé de la colonne donnant le NOM Prénom du patient s'appelle [@Usager]
- J'ai essayé différentes méthodes mais rien n'a fonctionné, voici la dernière en date :

='$J$3&[@Usager]&"\[Actions "&[@Usager]&".xlsx]"Feuil1'!$D$3

Avec J3 la cellule sur laquelle j'ai mis le début de l'adresse, pour raccourcir la longueur de la formule, et modifier l'adresse lorsque nos serveurs changent.
Cela me met un message d'erreur en me surlignant $J$3...
J'espère que ma demande est claire, difficile de tout synthétiser sans trop perdre d'information. Merci beaucoup pour votre aide !

PS : la semaine dernière j'ai rédigé un nouveau sujet sur ce forum, qui comportait cette problématique et une autre. L'autre a été résolue, et pour ne pas alourdir le sujet je l'ai noté comme résolu, et ai ouvert celui-ci.

bonjour,

Pour pouvoir adresser une plage de cellules de manière dynamique (cad en construisant une adresse sur base d'information contenue dans des cellules), il faut utiliser INDIRECT. INDIRECT ne fonctionne malheureusement pas si on y fait référence à un classeur fermé. La solution passe par VBA ou powerquery.

Bonjour,

Merci beaucoup pour votre réponse. Je n'ai que quelques bases de programmation, que me conseillez-vous entre se mettre au VBA ou à Powerquery, concernant ma problématique ?

Merci d'avance :)

Bonsoir

voici un petit bout de code qui devrait faire l'affaire

Sub Macro1()
 Dim Dossier As String
 Dossier = Range("j3").Value 'ici le chemin vers tes fichiers patients
 If Dir(Dossier, vbDirectory) = "" Then
 MsgBox "Dossier n'existe pas..... voir valeur en j3"
 Exit Sub
 End If

 Dim Fichier As String

Derligne = Range("D" & Rows.Count).End(xlUp).Row
   For Each c In Range("D7:D" & Derligne)
        Fichier = c.Value & ".xlsx"

        If Dir(Dossier & Fichier) <> "" Then

        Set wb = Workbooks.Open(Dossier & "\" & Fichier)
        ma_valeur = wb.Sheets(1).Range("D3").Value
         wb.Close False
        c.Offset(0, 6).Value = ma_valeur
        Else
        c.Offset(0, 6).Value = "pas de fichier"
        End If

    Next

End Sub

Bonne continuation

Bonjour Fg2b,

Merci beaucoup pour ta réponse. Je n'arrive cependant pas à rajouter le chemin vers mon dossier patient, j'ai essayé avec ça :

 Dossier = Range(j3&[@Usager]&"\[Actions "&[@Usager]&".xlsx]"Feuil1').Value

mais ça me sort une erreur de compilation sur le [@Usager] : "attendu : séparateur de liste ou )". Je ne comprends pas comment lui donner l'adresse des fichiers recherchés.

PS : je n'ai jamais programmé. Je me suis renseigné sur ce que tu m'as envoyé, j'ai compris le fonctionnement global et les différentes étapes (je crois), mais dans le détail des commandes je suis perdu.

Bonjour,

l'erreur vient du fait que [@Usager] fait reference a une colonne d'un tableau (structure) mais ne peut pas correspondre a un chemin vers un fichier.

Pour pointer vers le bon dossier il faut en j3

\\10.120.186.1\fs-0301\Utilisateurs\loup.guernier\EQLAAT Ici\Usagers\Dossiers\

(vous n'avez pas supprimé les liaisons dans votre fichier 😉 )

er pour le fichier , on recupere le nom du patient qui est la valeur valeur dela cellule D7; D8 etc....

c'est la ligne

Fichier = c.Value & ".xlsx" 

et le chemin complet est donc Dossier & Fichier

If Dir(Dossier & Fichier) <> "" teste en fait l'existence du fichier,

Devant cette ligne mettez

 MsgBox  Dossier & Fichier 

et vous verrez vers quoi vous pointez

@ +

Bonjour,

Désolé du délais de réponse, j'ai (malheureusement) un temps limité au boulot me permettant de vaquer à cette optimisation de mes tableaux excel !

Merci beaucoup pour votre message qui me permet de mieux comprendre cette formule ! Je viens de me rendre compte que je n'avais pas besoin de la modifier pour l'utiliser

J'ai d'ailleurs utilisé votre ligne

 MsgBox  Dossier & Fichier

ce qui m'a bien donné les infos nécessaires (165 fois plus précisément ) pour me rendre compte qu'il manquait une info dans la direction du fichier.

Actuellement "Dossier" pointe sur [...]\Usagers\Dossiers\. Or, il y a ensuite un autre dossier au nom de chaque usager, et c'est dans ces fichiers individualisés là que se trouvent les classeurs. Cela fait donc [...]\Usagers\Dossiers\NOM Prénom\Nom Prénom.xlsx

J'ai essayé de modifier votre code pour rajouter ce dossier supplémentaire, mais cela ne fonctionne pas. J'imagine que c'est car le code est en 2 parties, et que la partie "personnalisation en fonction du nom" arrive en 2ème ? J'ai essayé de modifier la variable "Dossier" mais je n'ai pas réussi...

Merci pour votre précieuse aide

Bonjour,

Essayez qq chose comme ca

MsgBox  Dossier & c.value & Fichier

pour voir ou vous pointez

( C.value contient les nom de vos patients,

fichier c'est la meme chose mais on ajoute l'extension .xlsx )

Bon courage

Bonjour fg2b,

Merci pour votre réponse J'ai bidouillé un bon bout de temps pour modifier un peu le code, qui donne maintenant ceci :

Sub Macro1()
 Dim Dossier As String
 Dossier = Range("j4").Value 'ici le chemin vers tes fichiers patients
 If Dir(Dossier, vbDirectory) = "" Then
 MsgBox "Dossier n'existe pas..... voir valeur en j4"
 Exit Sub
 End If

 Dim Fichier As String

Derligne = Range("D" & Rows.Count).End(xlUp).Row
   For Each c In Range("D7:D" & Derligne)
        Fichier = "Actions" & Space(1) & c.Value & ".xlsx"

        MsgBox Dossier & Space(1) & c.Value & "\" & Fichier
        If Dir(Dossier & Space(1) & c.Value & "\" & Fichier) <> "" Then

        Set wb = Workbooks.Open(Dossier & Space(1) & c.Value & "\" & Fichier)
        ma_valeur = wb.Sheets(1).Range("D3").Value

         wb.Close False
        c.Offset(0, 6).Value = ma_valeur
        Else
        c.Offset(0, 6).Value = "pas de fichier"
        End If

    Next

End Sub

(Je suis passé à J4 au lieu de j3 pour le chemin de dossier, plus simple pour moi).

Avec

MsgBox Dossier & Space(1) & c.Value & "\" & Fichier

j'ai bien la bonne adresse pour trouver chaque classeur usager (enfin !). Cependant, ça me sort toujours "pas de fichier", cela me rend fou Car ça veut dire que

If Dir(Dossier & Space(1) & c.Value & "\" & Fichier) <> "" Then

renvoit un FAUX ? Je ne comprends pas pourquoi...

Merci pour votre aide

Bonjour,

Dir function (Visual Basic for Applications) | Microsoft Learn renvoie le chemin d'accès au dossier ou fichier désigné.

A mon avis ce sont vos espaces space(1) qui font tout planter. Entrez directement dans la cellule ou dans votre variable le chemin complet. Et utiliser Dossier & "\" & Fichier

A mon avis, ceci

Dossier & Space(1) & c.Value

Devrait plutot etre

Dossier & "\" & c.Value

Mais bon je ne comprends pas votre arborescence. De ce que je vois vous avez quelque chose comme ceci :

DossierParent (cellule J3) \ DossierMemeNomFichier \ Fichier.xlsx

Or vous vous avez écrit

DossierParent (cellule J3) [ESPACE] DossierMemeNomFichier \ Fichier.xlsx

Ce qui est bizarre quand on regarde le début du code.

Bonjour saboh, merci pour votre retour !

En fait mon arborescence est celle-ci, avec comme exemple MARTIN Jean :

\\10.120.186.1\fs-0301\Utilisateurs\loup.guernier\EQLAAT Ici\Usagers\Dossiers\MARTIN Jean\Actions MARTIN Jean.xlsx

L'espace que j'ai rajouté après "Actions" est nécessaire pour trouver le bon nom du classeur, sinon j'avais "ActionsMARTIN Jean" ce qui ne fonctionnait pas.

Le problème vient du fait que je ne peux pas simplement faire "Dossier & "\" & Fichier", car le dossier change à chaque usager. Il y a un Dossier principal, nommé sobrement "Dossiers", qui comprend tous les Dossiers usagers. Donc dans le détail, pour

Dossier & Space(1) & c.Value & "\" & Fichier

- Dossier renvoi jusqu'au dossier principal "Dossiers" (avec J4)

- Space(1) & c.Value renvoi au dossier usager, ici "MARTIN Jean"

- Fichier renvoi au classeur usager, ici "Actions MARTIN Jean.xlsx"

Normalement avec mon MsgBox j'ai vérifié que l'adressage était bien le bon, c'est pour ça que je ne comprends pas que la fonction Dir ne trouve pas le classeur...

bonjour,

À ma connaissance, dir ne fonctionne pas avec des adresses réseaux. Il faut passer par un "mapping d'adresse" c.à.d assigner une lettre de périphérique de stockage (par exemple H:, I:) parmi celles qui sont libres, au serveur.

Bonjour H2So4,

Merci pour votre retour !!

J'ai eu peur car je ne pense pas avoir les droits nécessaires pour faire cela, mais finalement en regardant de plus près je viens de voir qu'il y a déjà une lettre d'attribuée, J. Cela donne donc "J:\Utilisateurs\" au lieu de "\\10.120.186.1\fs-0301\Utilisateurs". Cela fonctionne avec les formules de lien hypertexte, cela m'ouvre bien mon classeur avec J:\ !

Cependant cela n'a pas changé, j'ai toujours le retour "pas de fichier"

bonjour,

Cela fonctionne avec les formules de lien hypertexte, cela m'ouvre bien mon classeur avec J:\ !

Cependant cela n'a pas changé, j'ai toujours le retour "pas de fichier"

Si cela fonctionne avec le lien hypertexte, mais pas avec la macro, c'est qu'il y a un problème avec la création du nom de fichier dans la macro. Peux-tu mettre un copier/coller du lien hypertexte (avec la lettre j:\) et indiquer quelles sont les parties variables ? je devrais pouvoir retrouver cette information en relisant tout ce qui a déjà été écrit à ce sujet, mais ce serait plus simple pour moi si tu pouvais répéter cette information ici.

Le lien hypertexte qui fonctionne, pour MARTIN Jean par exemple, est celui-ci :

J:\Utilisateurs\loup.guernier\EQLAAT Ici\Usagers\Dossiers\MARTIN Jean\Actions MARTIN Jean.xlsx

Les parties variables sont uniquement "MARTIN Jean". Pour chacun des usagers cela fonctionne ainsi.

Ces informations sont contenues dans c.Value si je comprends bien (cf fg2b).

Bonjour,

Et en écrivant

If Dir(replace("J:\Utilisateurs\loup.guernier\EQLAAT Ici\Usagers\Dossiers\XXXXX\Actions XXXXX.xlsx","XXXXX", c.Value)) <> "" Then

Avez-vous toujours l'erreur ?

ci-après le code du sub pour ce que je propose :

(je vous ai rajouté les dim manquants)

Sub Macro1()
  Dim Dossier As String
  Dossier = Range("j4").Value                    'ici le chemin vers tes fichiers patients
  If Dir(Dossier, vbDirectory) = "" Then
    MsgBox "Dossier n'existe pas..... voir valeur en j4"
    Exit Sub
  End If

  Dim Fichier As String
  Dim c As Range, Derligne As Long, wb As Workbook, ma_valeur As String

  Derligne = Range("D" & Rows.Count).End(xlUp).Row
  For Each c In Range("D7:D" & Derligne)
    Fichier = Replace("J:\Utilisateurs\loup.guernier\EQLAAT Ici\Usagers\Dossiers\XXXXX\Actions XXXXX.xlsx", "XXXXX", c.Value)

    MsgBox Fichier
    If Dir(Fichier) <> "" Then

      Set wb = Workbooks.Open(Fichier)
      ma_valeur = wb.Sheets(1).Range("D3").Value
      wb.Close False
      c.Offset(0, 6).Value = ma_valeur
    Else
      c.Offset(0, 6).Value = "pas de fichier"
    End If
  Next c
End Sub

re,

Edit : oups désolé Saboh12617, téléscopage.

en faisant ceci je n'ai pas de problème

mettre en J3

J:\Utilisateurs\loup.guernier\EQLAAT Ici\Usagers\Dossiers\

remplacer ton code par celui-ci

Sub Macro1()
    Dim Dossier As String
    Dossier = Range("j3").Value 'ici le chemin vers tes fichiers patients
    If Dir(Dossier, vbDirectory) = "" Then
        MsgBox "Dossier " & Dossier & " n'existe pas..... voir valeur en j3"
        Exit Sub
    End If

    Dim Fichier As String

    Derligne = Range("D" & Rows.Count).End(xlUp).Row
    For Each c In Range("D7:D" & Derligne)
        Fichier = Dossier & c.Value & "\Actions " & c.Value & ".xlsx"

        'MsgBox Fichier
        If Dir(Fichier) <> "" Then

            Set wb = Workbooks.Open(Fichier)
            ma_valeur = wb.Sheets(1).Range("D3").Value

            wb.Close False
            c.Offset(0, 6).Value = ma_valeur
        Else
            c.Offset(0, 6).Value = "pas de fichier"
        End If

    Next

End Sub

Hallelujah !

Cela fonctionne ! Je viens tout juste d'essayer la dernière mouture, et cela me renvoi les bons résultats ! Merci beaucoup h2So4 et Saboh !!

Petite demande bonus : dans l'idéal j'aimerais ne pas avoir à modifier mes 160 tableaux un par un pour créer à chaque fois une cellule D3 qui contienne MAX(D7:D100). Y'aurait-il donc moyen de modifier

a_valeur = wb.Sheets(1).Range("D3").Value

en quelque chose qui calcul automatiquement le max ? J'ai essayé en remplaçant par

a_valeur = wb.Sheets(1).Application.WorksheetFunction.Max("D7:D100").Value

Mais cela me renvoi un message d'erreur... J'ai essayé en enlevant les "" mais cela n'a pas marché non plus. J'ai bidouillé des trucs mais rien n'y fait, je n'ai pas réussi !

Pourriez-vous m'aider dans cette nouvelle problématique ? J'ai hésité à créer un nouveau sujet amis je me suis dit que ça n'était pas un problème très compliqué pour vous et qu'en 1 message ça serait réglé, mais si besoin je peux recréer un sujet

Merci beaucoup par avance

Et une très belle journée à vous !

Vous y étiez quasiment :

a_valeur = wb.Sheets(1).Application.WorksheetFunction.Max("D7:D100")
' qu'on peut simplifier/améliorer en
a_valeur = WorksheetFunction.Max(wb.Sheets(1).Range("D7:D100"))

Car WorksheetFunction.Max method (Excel) | Microsoft Learn vous renvoie directement le résultat (càd la "Valeur").

.Valeur ne s'applique qu'aux cellules (objets Range/Cells en VBA).

Bonne fin de journée

Bonjour Saboh,

Eh bien tout fonctionne, c'est juste parfait !

Merci pour cette explication sur mon erreur, je n'ai pas fini d'apprendre en me lançant dans le VBA !

Une très belle journée à vous