Office 365 Récupérer valeur cellule dans d'autres classeurs

Thibault123

XLDnaute Nouveau
Bonjour,

Je souhaite récupérer dans un fichier Excel des valeurs de cellules situées dans d'autres fichiers Excel.
Ces fichiers sont dans des dossiers différents. La cellule par contre est toujours située au même endroit (même nom d'onglet et même case).

J'ai mis en case A1 le début du chemin d'accès et dans d'autres cases la partie changeante du chemin (cf. photo jointe).
Ayant pas mal de valeurs à récupérer ainsi (plusieurs valeurs dans plusieurs fichiers), j'aimerais avoir une formule du style: $A$1&Ai&\[nom du fichier.xlsx]10. Onglet'!$G$2.
Je n'arrive pas à trouver la bonne combinaison avec les bons symboles au bon endroit.
Je pense qu'il doit y avoir une solution avec des formules. J'aimerais me passer de macro pour cela.
Petite précision : la formule doit pouvoir marcher avec les classeurs fermés.

Merci d'avance pour votre aide.
 

Fichiers joints

Ikito

XLDnaute Occasionnel
Bonjour Thibault123,

Sans macro, je ne pense vraiment pas que ce soit possible. Si quelqu'un a la solution ça m'intéresserait grandement, car je viens de galérer avec les fonction ADODB :)

Par contre, si tu finis par utiliser les Macros, dans ce cas je pourrai t'aider
 

Thibault123

XLDnaute Nouveau
Salut Ikito,
Merci de t'être arrêté sur mon post.

J'arrive avec les formules mais sans automatisme, i.e. en donnant le chemin complet comme montré dans l'exemple.
Mais dès que je cherche à remplacer des parties du chemin par les données écrites dans les cases A1 et Ai (avec des & ou des guillemets), cela ne fonctionne pas.
Je suis surpris que cela ne soit pas possible avec une formule.
Si impossible à faire avec des formules, je le ferai avec macro. Mais dans ce cas, il faudra m'aider à faire la macro en totalité car je ne pas assez bon pour le faire moi-même! ^^
 

Thibault123

XLDnaute Nouveau
Cela ne devrait pas t'apporter grand chose de plus comme le chemin ne fonctionnera pas de ton côté.
Mais bon je joins un fichier simplifié.
La formule écrite ainsi fonctionne. Mais il faudrait l'automatiser en remplaçant le début du chemin par A1 et la partie centrale par Ai.
 

Fichiers joints

Ikito

XLDnaute Occasionnel
J'avais la même problématique la semaine dernière que j'ai contournée via les macros, d'où ma demande.

Quelque chose du genre devrait fonctionner. En B3 :

Code:
=CONCATENER($A$1;$A3;"\";"[E_0306_v8_8K_QC_report.xlsx]10. Quality KPI'!$G$2")
Essaye de ton côté, dis-moi ce qu'il en retourne.

EDIT : Peut-être ajouter =INDIRECT devant le tout. A tester.
 

Ikito

XLDnaute Occasionnel
Le chemin parait correct ? Il ne manque pas "\" ou " " à tout hasard ?

Si c'est une erreur #REF c'est la seule possibilité que j'entrevois
 

job75

XLDnaute Barbatruc
Bonjour Thibault123, Ikito,

Il faut entrer la formule de liaison "en dur" dans les cellules, donc utilisez cette macro :
VB:
Sub Liaison()
Dim chemin$, txt$, tablo, i&
chemin = [A1]
txt = "\[E_0306_v8_8K_QC_report.xlsx]10. Quality KPI'!$G$2"
With [A3].CurrentRegion.Resize(, 2)
    tablo = .Formula 'matrice, plus rapide
    For i = 1 To UBound(tablo)
        tablo(i, 2) = "='" & chemin & tablo(i, 1) & txt
    Next
    Application.DisplayAlerts = False
    .Formula = tablo
End With
End Sub
A+
 

Thibault123

XLDnaute Nouveau
Salut job75,

ça fonctionne. Merci.

Par contre je ne comprends pas tout dans la macro. Pourrais-tu l'expliquer un peu?
A quoi sert [A3].CurrentRegion.Resize(, 2) ?
Et Application.DisplayAlerts = False ?
Je pense avoir compris le reste.

Autre question que je ne pensais pas devoir demander, mais comme je vais devoir utiliser des macros et bien je ne sais pas le faire!
Mon besoin est plus complexe.
Le nom du document [E_0306_v8_8K_QC_report.xlsx] dépend de la valeur contenue dans la colonne B. J'ai joint un tableau exemple. Il y a plus de types que ça. C'est juste pour donner un exemple.
Je n'ai pas qu'une seule valeur à récupérer dans les fichiers "sources" mais un paquet (là encore j'ai limité dans mon exemple). Toutes ces valeurs sont dans l'onglet appelé 10. Quality KPI de chaque fichier source.
Pourrais-tu me donner la macro permettant de prendre en compte cette condition (selon le contenu de la colonne B)?

Merci d'avance pour ton aide.
 

Fichiers joints

job75

XLDnaute Barbatruc
BonjourThibault123, le forum,

Pour CurrentRegion touche F1 ou cherchez sur le web.

Application.DisplayAlerts = False évite l'ouverture d'une bôite de dialogue si le fichier source n'est pas trouvé.

Pour la nouvelle question revoyez la colonne B de votre fichier (ci-joint) et utilisez cette macro :
VB:
Sub Liaison1()
Dim chemin$, txt1$, txt2$, txt3, tablo, i&, x$
chemin = [A1] & "["
txt1 = "_report.xlsx]10. Quality KPI'!$G$2"
txt2 = "_report.xlsx]10. Quality KPI'!$I$58"
txt3 = "_report.xlsx]10. Quality KPI'!$K$30"
With [A3].CurrentRegion.Resize(, 5)
    tablo = .Formula 'matrice, plus rapide
    For i = 2 To UBound(tablo)
        x = tablo(i, 2)
        tablo(i, 3) = "='" & chemin & x & txt1
        tablo(i, 4) = "='" & chemin & x & txt2
        tablo(i, 5) = "='" & chemin & x & txt3
    Next
    Application.DisplayAlerts = False
    .Formula = tablo
End With
End Sub
Attention, une macro ne doit pas porter le nom d'une variable !!!

Bonne journée.
 

Fichiers joints

Thibault123

XLDnaute Nouveau
Merci pour cette nouvelle macro.

Pour CurrentRegion.resize, si je comprends bien il faut que je précise le nombre de colonne de mon tableau final dans lequel va se faire l'ensemble des calculs?

Dans la définition des variables, pourquoi faut-il mettre un symbole $ à la fin de txt1 et txt2, mais pas pour txt3?
Et pourquoi faut-il mettre un symbole & après le i?
Désolé de toutes ces questions, mais c'est pour comprendre.

J'ai corrigé car il manquait une partie du chemin avec les données de la colonne A.
De plus, on peut se passer de la variable x comme ci-dessous, non ?
Je ne sais pas ce qui est le mieux (avec ou sans la variable x) en terme d'efficacité et de rapidité d'exécution de la macro ?

Sub Liaison1()
Dim chemin$, txt1$, txt2$, txt3, tablo, i&
chemin = [A1]
txt1 = "_report.xlsx]10. Quality KPI'!$G$2"
txt2 = "_report.xlsx]10. Quality KPI'!$I$58"
txt3 = "_report.xlsx]10. Quality KPI'!$K$30"
With [A3].CurrentRegion.Resize(, 5)
tablo = .Formula 'matrice, plus rapide
For i = 2 To UBound(tablo)
tablo(i, 3) = "='" & chemin & tablo(i, 1) & "\[" & tablo(i, 2) & txt1
tablo(i, 4) = "='" & chemin & tablo(i, 1) & "\[" & tablo(i, 2) & txt2
tablo(i, 5) = "='" & chemin & tablo(i, 1) & "\[" & tablo(i, 2) & txt3
Next
Application.DisplayAlerts = False
.Formula = tablo
End With
End Sub
 

job75

XLDnaute Barbatruc
Oui j'avais oublié de concaténer tablo(i, 1) donc utilisez avec ce fichier (2) :
VB:
Sub Liaison1()
Dim chemin$, txt1$, txt2$, txt3$, tablo, i&, x$
chemin = [A1]
txt1 = "_report.xlsx]10. Quality KPI'!$G$2"
txt2 = "_report.xlsx]10. Quality KPI'!$I$58"
txt3 = "_report.xlsx]10. Quality KPI'!$K$30"
With [A3].CurrentRegion.Resize(, 5)
    tablo = .Formula 'matrice, plus rapide
    For i = 2 To UBound(tablo)
        x = "='" & chemin & tablo(i, 1) & "\[" & tablo(i, 2)
        tablo(i, 3) = x & txt1
        tablo(i, 4) = x & txt2
        tablo(i, 5) = x & txt3
    Next
    Application.DisplayAlerts = False
    .Formula = tablo
End With
End Sub
La variable x permet de gagner un peu de temps.

Les caractères "$" et "&" déclarent les variables "As String" et "As Long".
 

Fichiers joints

Thibault123

XLDnaute Nouveau
Merci pour ces explications et ces macros. Ton aide fût précieuse.
ça va me faire pas mal de lignes en tout mais je pense que j'arriverai à adapter avec les vraies données.
 

Thibault123

XLDnaute Nouveau
Bonjour,

Je suis de retour car je n'arrives pas à faire fonctionner la macro dans mon fichier (version simplifiée ci-jointe).
J'ai une erreur 1004 et je n'arrive pas à trouver l'erreur.
Merci d'avance pour votre aide.
 

Fichiers joints

job75

XLDnaute Barbatruc
Bonjour Thibault123,
VB:
Sub Recuperation()
Dim chemin$, txt1$, txt2$, txt3$, tablo, resu, i&, x$
chemin = [A1]
txt1 = "]10. Quality KPI'!$G$2"
txt2 = "]10. Quality KPI'!$H$2"
txt3 = "]10. Quality KPI'!$I$2"
tablo = [A3].CurrentRegion.Resize(, 4) 'matrice, plus rapide
With [A3].CurrentRegion.Columns(5).Resize(, 4)
    resu = .Formula 'matrice, plus rapide
    For i = 2 To UBound(tablo)
        x = "='" & chemin & tablo(i, 2) & "\[" & tablo(i, 4)
        resu(i, 1) = x & txt1
        resu(i, 3) = x & txt2
        resu(i, 4) = x & txt3
    Next
    Application.DisplayAlerts = False
    .Formula = resu 'restitution
End With
End Sub
A+
 

Fichiers joints

Thibault123

XLDnaute Nouveau
Merci d'être repassé par là job75.
Même avec cette macro, il y a toujours l'erreur 1004. C'est la ligne .Formula = resu qui pose problème. Idem qu'avec la précédente version où c'était la ligne .Formula = tablo qui posait problème.
 

Thibault123

XLDnaute Nouveau
Cela semble fonctionner effectivement.
Je testerai sur plus de valeurs.

Pourrais-tu stp me dire pourquoi tu mets les chiffres 4 ou 5 dans les fonctions suivantes?
tablo = [A3].CurrentRegion.Resize(, 4)
With [A3].CurrentRegion.Columns(5).Resize(, 4)

Question supplémentaire si tu le veux bien : comment faire pour que ces opérations de récupération de valeurs ne se fassent que si une date est précisée dans la colonne A? Si pas de date indiquée, laisser les cases de scores vides.
J'ai essayé mais sans succès.

Merci d'avance pour ton aide précieuse.
 

job75

XLDnaute Barbatruc
Cela semble fonctionner
ça marche ou ça ne marche pas, un point c'est tout !!!

tablo et resu font référence à 2 plages distinctes définies avec Resize (faites une recherche sur ce mot).

Pour la question supplémentaire (la dernière j'espère) :
VB:
Sub Recuperation()
Dim chemin$, txt1$, txt2$, txt3$, tablo, resu, i&, test As Boolean, x$
chemin = [A1]
txt1 = "]10. Quality KPI'!$G$2"
txt2 = "]10. Quality KPI'!$H$2"
txt3 = "]10. Quality KPI'!$I$2"
tablo = [A3].CurrentRegion.Resize(, 4) 'matrice, plus rapide
With [A3].CurrentRegion.Columns(5).Resize(, 4)
    resu = .Formula 'matrice, plus rapide
    For i = 2 To UBound(tablo)
        test = IsDate(tablo(i, 1))
        x = "='" & chemin & tablo(i, 2) & "\[" & tablo(i, 4)
        resu(i, 1) = IIf(test, x & txt1, "")
        resu(i, 3) = IIf(test, x & txt2, "")
        resu(i, 4) = IIf(test, x & txt3, "")
    Next
    Application.DisplayAlerts = False
    .Formula = resu 'restitution
End With
End Sub
 

Fichiers joints


Haut Bas