Comment gérer les fichiers contenus dans un dossier et ses sous-dossiers avec les liens hypertextes d'Excel ?

Dans ce tutoriel, nous allons découvrir comment automatiser la construction de liens hypertextes à partir de l’adresse d’un fichier ou d’une adresse internet saisie dans une cellule Excel.

Il s’agit en effet de répondre à la question posée par NIALA331 dans les commentaires de la vidéo « Comment lister les fichiers enregistrés dans un dossier et ses sous-dossiers sans VBA sur Excel ? » qui souhaite savoir comment transformer le chemin d’un fichier en lien hypertexte pour pouvoir ouvrir rapidement ce fichier.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Introduction

Rappelez-vous, il y a quelques semaines, nous avions vu qu’il était très simple de récupérer la liste des fichiers contenus dans un dossier ainsi que ses sous-dossiers en utilisant Power Query.

Maintenant, pour que ces résultats soient vraiment utiles et exploitables, il faudrait pouvoir être capable d’ouvrir directement ces fichiers depuis le classeur Excel d’un simple clic.

2. Créer un lien hypertexte

Pour accéder à un fichier ou une page web directement en cliquant sur une cellule Excel, nous pouvons insérer un lien hypertexte.

Un lien hypertexte permet donc de rendre un élément quelconque cliquable.

Sur Excel, il est possible de créer un lien hypertexte de trois manières différentes :

• Soit en affectant un lien hypertexte à la cellule,
• Soit en utilisant la fonction LIEN_HYPERTEXTE(),
• Soit en développant une petite macro-commande en VBA

Et pour commencer, nous allons voir comment créer un lien directement sur une cellule, ce qui est la méthode la plus rapide à mettre en place sur une cellule unique.

Il suffit en effet d’effectuer un clic droit sur la cellule, puis de sélectionner « Lien », sur le bas du menu contextuel.

Sur la fenêtre qui s’affiche, nous sélectionnons la première option « Fichier ou page web existant(e) », et nous saisissons le chemin d’accès au fichier dans le champ « Adresse » :

Ici, nous devons donc ressaisir le chemin à la main, en commençant par le contenu de la colonne « Dossier d’enregistrement », puis « Nom de fichier » :

Ensuite, nous validons en cliquant sur le bouton [OK].

Maintenant, nous pouvons identifier la présence du lien hypertexte par le texte bleu souligné :

Et bien entendu, si nous cliquons sur ce fichier, Excel l’ouvrira directement.

Ici, il s’agit directement d’un classeur Excel, c’est pourquoi celui-ci s’ouvre directement dans le tableur, mais si cela avait été un autre type de fichier, Excel aurait alors lancé l’application définie par défaut pour le type de fichier (Word, PowerPoint, Paint…).

Alors évidemment, comme vous avez pu le constater, ressaisir l’intégralité du chemin d’ouverture du fichier peut être rébarbatif.

Pourtant, un peu plus tôt je vous expliquais que cette méthode était la plus rapide à mettre en place, mais pour cela, il faut que le chemin d’accès soit accessible directement dans la cellule, afin de pouvoir en effectuer un copier-coller directement dans le champ Adresse :

Pour gagner encore un peu de temps, il est possible d’afficher la fenêtre en utilisant le raccourci clavier [Ctrl]+[k].

De plus, si le lien est bien formaté, Excel pourra créer le lien automatiquement :

3. Créer un lien avec la fonction LIEN_HYPERTEXTE

En revanche, pour créer une série de plusieurs liens, il sera plus rapide de générer le lien en utilisant la fonction LIEN_HYPERTEXTE().

Cette fonction permet d’insérer rapidement un lien, comme nous l’avons vu dans la partie précédente, mais sans avoir d’afficher la fenêtre de paramétrage.

Dans notre exemple, nous devrons concaténer le résultat des deux cellules en utilisant une esperluette :

=LIEN_HYPERTEXTE([@[Dossier  d''enregistrement]]&[@[Nom de fichier]])

De plus, étant donné que la base de données est présentée sous la forme d’un tableau Excel, il suffit de créer la formule une fois pour que celle-ci soit dupliquée sur toute la colonne.

Ensuite, pour améliorer la lisibilité des données, nous pouvons ajouter ce qu’Excel appelle un nom convivial, c’est-à-dire un texte présentant le lien, à la place de son chemin.

Par contre, l’inconvénient de cette méthode, c’est que celle-ci nécessite la création d’une colonne supplémentaire.

4. Automatiser la création de tous les liens de la colonne en VBA

Si nous souhaitons automatiser la création de liens hypertextes, directement dans une des colonnes existantes, nous devrons créer une petite macro en VBA, laquelle reposera sur les notions découvertes dans le tutoriel dédié à la création d’un sommaire automatique, que vous retrouverez en cliquant ici.

Pour que ce tutoriel soit accessible à tout le monde, nous allons revenir dans le détail sur aspect de la construction de cette macro, qui reste très sommaire.

Mais si vous souhaitez aller plus loin dans la découverte du langage VBA, vous pouvez évidemment consulter la formation disponible sur le blog en cliquant ici.

Pour commencer à créer une macro, nous allons lancer l’éditeur de code VBE (pour Visual Basic Editor), qui est l’éditeur de code intégré dans les différentes applications de la suite Office et qui permet de développer des macro-commandes en VBA.

Pour cela, nous appuyons simplement sur les touches [Alt]+[F11] :

VBE s’ouvre alors et nous allons commencer par créer un nouveau module.

Un module est une feuille de saisie de code dans laquelle nous allons pouvoir saisir nos macros et fonctions.

Pour insérer un nouveau module, nous nous rendons dans le menu Insertion > Module :

Une fois le module inséré, nous pouvons créer la macro en utilisant le mot-clé Sub, suivi du nom de la macro :

Sub creerLiens()
 
End Sub

Ici, nous allons passer en revue toutes les cellules de la colonne « Nom de fichier » du tableau nommé « Articles_01_2021 ».

Pour cela, nous créons une nouvelle variable que nous appelons c (pour « cellule ») et que nous typons en tant que Range, c’est-à-dire un objet de type plage de cellule.

    Dim c As Range

Cette variable c va nous permettre de mettre en place une boucle For Each :

    For Each c In [Articles_01_2021[Nom de fichier]]
        
    Next

Ensuite, pour créer le lien dans la cellule retournée par l’objet c, nous pouvons utiliser la méthode ActiveSheet.Hyperlinks.Add qui fonctionne de la manière suivante :

ActiveSheet.Hyperlinks.Add anchor:=c,  Address:=c.Offset(0, 2) & c, TextToDisplay:=c.Value

• ActiveSheet.Hyperlinks.Add : Ici, nous créons un lien hypertexte dans la feuille de calcul active,
• anchor:=c : nous insérons ce lien dans la cellule en cours, retournée par c
• Address:=c.Offset(0, 2) & c : pour créer un lien vers un fichier externe, nous devons utiliser le paramètre Address, en spécifiant le chemin complet du fichier que nous obtenons par la concaténation des cellules contenues dans les colonnes « Dossier d’enregistrement », puis « Nom de fichier » :
• TextToDisplay:=c.Value: et enfin, nous spécifions le texte que nous souhaitons afficher dans la cellule, ici le nom du fichier contenu dans la cellule c

Il ne reste plus qu’à tester la macro en appuyant sur la touche F5 pour constater que tous les liens sont bien insérés :