Récupérer une valeur à partir d'un autre classeur Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Récupérer à partir d'un classeur fermé

Lorsque pour des raisons de sécurité, les données sources sont conservées dans des classeurs externes, il est intéressant de savoir y accéder en toute transparence par le code VBA Excel.



Sur l'exemple illustré par la capture, l'utilisateur choisit un trimestre représenté par le nom d'un classeur à l'aide d'une liste déroulante. Aussitôt, la synthèse des ventes réalisées est importée dans une cellule située quelques cases plus bas. Pourtant ce classeur n'héberge aucune donnée. Le code VBA que nous allons construire se charge de pointer sur la cellule concernée dans le classeur externe représenté par le nom choisi, pour extraire l'information souhaitée.

Classeurs Excel à télécharger
Pour développer cette astuce, nous suggérons d'appuyer l'étude sur un classeur principal accompagné de classeurs externes hébergeant les données à importer.

• Télécharger le fichier compressé importer-classeur-ferme.rar en cliquant sur ce lien,
• Le décompresser dans le dossier de votre choix,

Comme vous le constatez, la décompression livre cinq classeurs Excel. Le classeur principal, devant importer les informations à partir des autres, est nommé synthese.xlsm. Les autres sont tous nommés Trimestre avec en suffixe, précisément le numéro du trimestre concerné.

• Double cliquer par exemple sur le classeur Trimestre1.xlsx pour l'ouvrir dans Excel,

Comme vous pouvez le voir, nous débouchons sur une feuille très simple. En cellule C6, elle consolide le bilan des ventes réalisées sur les trois mois du trimestre. Ces quatre classeurs externes sont tous formés de la même façon. Selon le choix de l'utilisateur depuis le classeur principal, c'est ce montant consolidé que nous devons importer, sans ouvrir le classeur externe, donc de façon transparente pour l'utilisateur.

La feuille de la consolidation
Maintenant, nous proposons de découvrir la feuille du classeur à partir de laquelle il s'agit de récupérer ces données externes, bien qu'appartenant à des classeurs fermés, qu'il n'est pas question d'ouvrir.

• Fermer le classeur en cliquant sur la croix de la fenêtre,
• Depuis le dossier de décompression, cliquer droit sur le fichier synthese.xlsm,
• Dans le menu contextuel, choisir l'option Propriétés,
• En bas de la boîte de dialogue, cocher la case nommée Débloquer,
• Valider en cliquant sur le bouton Ok,
• Puis, double cliquer sur le fichier synthese.xlsm pour l'ouvrir dans Excel,

Ce classeur n'est constitué que d'une feuille. Elle se nomme synthese. En cellule D4, l'utilisateur déploie une liste déroulante pour désigner l'un des classeurs externes, placés dans le dossier de l'application. Et c'est alors la synthèse du classeur ainsi défini, qui doit importer le bilan consolidé depuis sa cellule C6 dans la cellule D7 du classeur principal.

Importer au changement de choix
Le code VBA Excel doit automatiquement se déclencher au changement de valeur opéré dans la liste déroulante en cellule D4.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• Dans l'explorateur de projet sur la gauche, double cliquer sur l'élément Feuil1(Synthese),

Ainsi, nous affichons la feuille de code VBA associée à la feuille Excel. Nous y découvrons la procédure événementielle Worksheet_Change. Son code VBA se déclenchera au changement de valeur dans l'une des cellules de la feuille. Nous l'avions préconçue en choisissant l'élément Worksheet dans la liste de gauche, en haut à gauche de la feuille de code. L'élément Target passé en paramètre de cette procédure, représente la cellule concernée par le changement de valeur lorsque l'utilisateur opère une modification sur la feuille. Nous allons donc l'exploiter pour connaître la valeur choisie avec la liste déroulante.

La variable
Pour accéder aux cellules d'un classeur Excel fermé, nous avons besoin de reconstituer le chemin d'accès complet jusqu'à cette dite cellule. Il s'agit du chemin d'accès au classeur, associé au nom du classeur lui-même entre crochets, suivi du nom de la feuille cible pour atteindre la cellule à prélever après un point d'exclamation de séparation.

• Dans les bornes de la procédure événementielle, ajouter la déclaration de variable suivante :

...
Dim chemin As String
...

C'est dans cette variable que nous devons reconstituer ce chemin d'accès.

Vérifier la cellule modifiée
Pour ne pas surconsommer, nous devons vérifier la cellule changée pour déclencher le code VBA seulement lorsque la modification concerne la cellule D4.

• A la suite du code VBA, créer l'instruction conditionnelle suivante :

...
If Target.Address = "$D$4" Then

End If
...

Le critère est donc posé sur les références de la cellule interceptée (Target.Address) par la procédure événementielle. C'est ainsi que nous choisissons de poursuivre le traitement (Then) lorsqu'il s'agit bien de la cellule D4 ($D$4). Le cas échéant et implicitement, ce traitement est avorté.

Chemin jusqu'à la cellule externe
Selon les indications que nous avons formulées précédemment, nous devons construire la chaîne tortueuse permettant d'atteindre une cellule précise sur une feuille précise d'un classeur identifié, ici en l'occurrence par le choix de l'utilisateur avec la liste déroulante.

• Dans les bornes de l'instruction conditionnelle, ajouter la ligne VBA suivante :

...
chemin = "='" & ThisWorkbook.Path & "\[" & Target.Value & "]Bilan'!C6"
...

Selon la syntaxe d'Excel, ce chemin doit nécessairement est inscrit entre côtes. Comme nous sommes déjà dans des doubles côtes, nous utilisons la simple côté (') pour l'encadrement, avec la touche 4 en haut à gauche du clavier. La propriété Path de l'objet ThisWorkbook renseigne tout d'abord sur l'emplacement du classeur externe, soit le dossier de l'application active. Après un antislah (\) pour entrer dans le dossier, nous lui associons le nom du classeur choisi, entre crochets ([" & Target.Value & "]). Puis, par voie hiérarchique, nous désignons sa feuille que nous faisons suivre d'un point d'exclamation (Bilan'!), après avoir fermé la côte, pour enfin atteindre la cellule cible (C6) dont il s'agit de récupérer le bilan consolidé pour l'importer.

Importer la donnée brute
La variable chemin détient désormais le contenu que nous souhaitons afficher sur la feuille du classeur principal. Mais comme il s'agit d'un bilan calculé, nous allons aussi récupérer la formule qui ne peut pas fonctionner sur la destination, puisque les cellules sources ne sont plus là. Donc dans un deuxième temps, nous allons devoir transformer ce contenu importé en valeur pour le dissocier de sa formule d'origine.

• A la suite dans l'instruction conditionnelle, ajouter les deux lignes VBA suivantes :

...
[D7].Formula = chemin
[D7].Value = [D7].Value
...

On récupère la formule (Formula) emprisonnée dans la variable chemin. On écrase cette dernière par une égalité stricte ([D7].Value = [D7].Value) pour ne conserver que la valeur résultante, détachée de la formule.

Certes nous aurions pu nous abstenir de passer en intermédiaire par la variable chemin. Nous aurions pu effectuer une affectation directe. Mais c'est une question d'habitude et de logique car dans des développements plus denses, ces mêmes informations sont utiles à plusieurs reprises. Donc un stockage intermédiaire est alors justifié.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + Tab),
• Avec la liste déroulante en D4, choisir un classeur externe,

Comme vous pouvez l'apprécier, la donnée correspondante issue du classeur externe et fermé, est instantanément rapatriée dans le classeur en cours.