Appeler données classeur externe

Le 02/05/2024 à 21:09
Bonjour,

Je souhaiterais appeler le résultat d'une cellule contenue dans un classeur externe.

Je pensais que cela était possible par formule, j'ai besoin de faire varier le nom en jouant avec les dates.
J'ai donc tenter cette formule :
="'G:\***\"&ANNEE($G$1)&"\"&TEXTE($G$1;"AA.MM")&"\["&TEXTE($G$1;"AA.MM")&" - Fiche.xlsm]Feuil1'!$P$40"
Mais cela me sort du texte.

Pouvez-vous m'aider ?
Merci par avance
Cdt
Le 03/05/2024 à 09:44

Bonjour,

avec la fonction INDIRECT ?

=INDIRECT("'G:\***\"&ANNEE($G$1)&"\"&TEXTE($G$1;"AA.MM")&"\["&TEXTE($G$1;"AA.MM")&" - Fiche.xlsm]Feuil1'!$P$40")

Mais INDIRECT implique que le classeur en question soit ouvert.

Sinon Power Query peut aider mais je ne sais pas comment ça marche.

Le 03/05/2024 à 10:42

Bonjour,

Avec une macro Vba ce serait plus simple :

Placer la formule dans une variable selon le contenu des cellules

a$ = "='" & chemin & "[" & classeur & "]feuille'!$A$1" ' chemin, classeur, feuille = contenu des cellules avec mise en forme
Application.DisplayAlerts = False ' Pas de message d'alerte (sinon affiche la fenêtre choix de fichier)
Cells(x,y).Formula = a$           ' Place la formule de liaison
Application.DisplayAlerts = True  ' Rétablit les alertes

' Retourne True si erreur ("#REF!" ...) = Fichier absent ou autre
If IsError(Cells(x,y)) Then
    ' traiter l'erreur
else
    ' La cellule x,y contient le résultat
End if

J'utilise une méthode à peu près identique dans mes applis et cela fonctionne bien.

Bonne journée

Le 03/05/2024 à 14:22

Bonjour,

Doux Rêveur : MerciC'est juste dommage que cela nécessite que le classeur soit ouvert. Dans ce cas précis, ça ne m'arrange pas

Eric_angers : Merci ! Alors j'ai tenté d'adapter le code (avec le peu d'aisances que j'ai sur VBA) c'est... raté.

Voici ma tentative :

Sub RecupInfo()
Dim chemin, classeur As Long

chemin = "G:\******* " & Year(Range("G1")) & "\" & Format(Range("G1"), "YY.MM") & "\"
classeur = Format(Range("G1"), "YY.MM") & " - Fiche.xlsm"

a$ = "='" & chemin & "[" & classeur & "]Clément'!$P$40" ' chemin, classeur, feuille = contenu des cellules avec mise en forme
Application.DisplayAlerts = False ' Pas de message d'alerte (sinon affiche la fenêtre choix de fichier)
Cells(x, y).Formula = a$          ' Place la formule de liaison
Application.DisplayAlerts = True  ' Rétablit les alertes

' Retourne True si erreur ("#REF!" ...) = Fichier absent ou autre
If IsError(Cells(x, y)) Then
    ' traiter l'erreur
    MsgBox ("marche pas")
Else
    ' La cellule x,y contient le résultat
    MsgBox ("ok")
End If

End Sub

Merci encore pour le temps accordé

Cdt

Le 05/05/2024 à 11:34

Bonjour

Définir la variable "classeur" comme Long et y affecter du texte provoque une erreur, tu as dû t'en rendre compte en exécutant !!

Dim chemin, classeur simplement ou Dim chemin as string, classeur as string

Dans Cells(x,y), il faut remplacer x & y par les coordonnées de la cellule qui recevra le résultat (exemple Cells(1,1))

Pour tester la procédure, il faut l'exécuter pas à pas et vérifier que les variables sont correctement positionnées. Pointer avec la souris ou ajouter des espions.

Voici un exemple de chaîne finale (utilisée dans mon appli "Gestion de ses comptes et Budget prévisionnel annuel")

"='D:\Mes Documents\Compta\[Compta 2023.xlsm]Janvier'!$E$2"

On va chercher dans la feuille "Janvier" du classeur "Compta 2023.xlsm" placé dans le dossier "D:\Mes Documents\Compta\" le contenu de la cellule E2

Pour avoir un modèle de chaîne correcte, il suffit de faire l'extraction en manuel et de récupérer la chaîne de liaison :

1 - Ouvrir les 2 classeurs : Classeur A = celui qui reçoit le résultat, Classeur B = classeur à explorer

2 - Dans une cellule du classeur A : Taper = puis aller dans le classeur B pointer la cellule à récupérer (avec la souris) et valider. Le contenu de la cellule pointée doit apparaître.

3 - Refermer le classeur B : la formule dans le classeur A contient maintenant le chemin complet.

4 - Se servir de cette chaîne comme modèle pour rédiger la macro.

Normalement cela devrait fonctionner

Autre infos : Si le chemin contient des apostrophes, il faut les doubler (chemin=Replace(Chemin, "'", "''", 1)

A+

Le 07/05/2024 à 09:44

Bonjour Eric,

Définir la variable "classeur" comme Long et y affecter du texte provoque une erreur, tu as dû t'en rendre compte en exécutant !!

Absolument pas L'un comme l'autre ne fonctionnait pas, peut-être à cause du Cells(x,y) que je n'avais pas pensé à adapter.

En tout cas, grâce à tes indications, j'ai pu réussir à récupérer automatiquement la valeur de la cellule contenue dans mon classeur externe fermé !

Très pratique ce code !!

Merci encore.

Cdt

Le 07/05/2024 à 09:54

Bonjour,

Si cela fonctionne c'est parfait.

Bonne journée

Le 07/05/2024 à 21:51

Bonjour Eric,

J'ai fais quelques adaptations pour ma situation, et j'ai une interrogation par rapport à la procédure en cas d'échec.

Voici le code adapté :

Sub RecupInfo()
Dim chemin, classeur As String

chemin = "G:\************ " & Year(Range("T39")) & "\" & Format(Range("T39"), "YY.MM") & "\"
classeur = Format(Range("T39"), "YY.MM") & " - Fiche.xlsm"
feuille = ThisWorkbook.ActiveSheet.Name

a$ = "='" & chemin & "[" & classeur & "]" & feuille & "'!$P$40" ' chemin, classeur, feuille = contenu des cellules avec mise en forme
Application.DisplayAlerts = False ' Pas de message d'alerte (sinon affiche la fenêtre choix de fichier)
Cells(40, 20).Formula = a$          ' Place la formule de liaison
Application.DisplayAlerts = True  ' Rétablit les alertes

' Retourne True si erreur ("#REF!" ...) = Fichier absent ou autre
If IsError(Cells(40, 20)) Then
    ' traiter l'erreur
Exit Sub

Else
    ' La cellule x,y contient le résultat
End If

End Sub

Ce code est placé dans un module.

J'ai également récupérer une macro pour faire une boucle sur mes 4 feuilles :

Sub BoucleRecupInfo()

Dim i As Integer, feuilles
Application.ScreenUpdating = False

    feuilles = Split("nomdelafeuille;nomdelafeuille;nomdelafeuille;nomdelafeuille", ";")
    For i = 0 To 3
        Worksheets(feuilles(i)).Activate
        RecupInfo
    Next i

End Sub

Donc tout ça fonctionne, sauf que lorsque l'une des feuilles n'existe pas, une boite de dialogue s'ouvre et il me demande de choisir une feuille parmi celles existantes.

Je clique sur "annuler" et le code plante "Mémoire insuffisante".

Un #REF! s'inscrit en Cells(40,20)

J'ai fais plusieurs tentatives pour déclencher un Exit Sub lorsque la feuille n'existe pas, mais sans succès !

En faisant le pas à pas je remarque que le problème se situe à partir de

Cells(40, 20).Formula = a$          ' Place la formule de liaison

Et donc j'ai tenté de placer ce code juste avant :

If IsError(a$) Then
Exit Sub
End If

Mais ça ne donne rien, il continue de chercher après la feuille.

Une idée pour résoudre ceci ?

Merci

Cdt

Le 08/05/2024 à 08:38

Bonjour,

Dans ton code, tu active le feuille "xx" du classeur actif et tu vas chercher, dans un classeur fermé, une valeur dans une feuille de même nom !

C'est bien ça ? car si on lit ton code c'est pas clair du tout.

Concernant l'absence de feuille : normalement on utilise cette méthode lorsque l'on est sûr que la feuille existe.

Si on est pas sûr de l'existence de la feuille, il vaut mieux ouvrir le classeur et vérifier sa présence. Dans ce cas la formule devient inutile, on extrait l'info et on referme le classeur.

Si tu veux garder ta méthode, le choix de la feuille, si non trouvée, est inévitable.

Pour éviter le plantage il suffit de traiter l'erreur :

Sub RecupInfo()
Dim chemin As String, classeur As String
Dim i

chemin = "G:\************ " & Year(Range("T39")) & "\" & Format(Range("T39"), "YY.MM") & "\"
classeur = Format(Range("T39"), "YY.MM") & " - Fiche.xlsm"
feuille = ThisWorkbook.ActiveSheet.Name

a$ = "='" & chemin & "[" & classeur & "]" & feuille & "'!$P$40" ' chemin, classeur, feuille = contenu des cellules avec mise en forme
Application.DisplayAlerts = False ' Pas de message d'alerte (sinon affiche la fenêtre choix de fichier si absent)
On Error Resume Next
Cells(40, 20).Formula = a$        ' Place la formule de liaison
i = Err.Number: a$ = Err.Description
On Error Goto 0
Application.DisplayAlerts = True  ' Rétablit les alertes
If i <> 0 then
     ' Erreur dans la formule ou autre (Formule incorrecte, feuille absente et Annulation sur choix ...)
     MsgBox "Erreur : " & i & " " & a$, vbCritical
     Cells(40,20)="Erreur"
     Exit Sub
End If

' Retourne True si erreur ("#REF!" ...) = Fichier absent ou autre
If IsError(Cells(40, 20)) Then
    ' traiter l'erreur
Exit Sub

Else
    ' La cellule x,y contient le résultat
End If

End Sub

A+

Le 08/05/2024 à 12:49

Bonjour Eric,

Oui tu as bien compris, désolé pour l'absence d'indications.

Je me sers de ta méthode pour récupérer des variables (heures) de salariés. Chaque mois, je duplique le fichier, et dans ce fichier, 1 feuille = 1 salarié. (+ feuilles de BDD/Récap...)

Il s'agissait donc ici de récupérer une information sur plusieurs mois en arrière. Mais le code plantait dans le cas où le salarié n'avait pas assez d'ancienneté dans l'entreprise.

Mais grâce à ton code corrigé, c'est passé

Le code me demande toujours quelle feuille il doit choisir, je clique sur "Annuler", et l'Exit Sub finit bien par se déclencher

Vraiment content du résultat. Un grand merci pour ton aide, encore une fois.

Bien cordialement,

Rechercher des sujets similaires à "appeler donnees classeur externe"