Créer un lien hypertexte stable

Nous avons vu récemment la difficulté – on pourrait même dire l’impossibilité – rencontrée quand on crée un lien hypertexte et que l’on désire par exemple que les chemins identifiant les classeurs s’actualisent pour les liens vers le classeur lui-même, et ne s’actualisent pas pour les liens vers d’autres classeurs.

Avec le réglage que nous avons vu dans la commande Outils – Options – Général, bouton « Options Web », case « Mettre à jour les liens lors de l’enregistrement », on a des liens qui soit s’actualisent toujours, soit ne s’actualisent jamais, lors du déplacement du classeur contenant les liens. Il est donc impossible de demander que seuls les liens « internes » soient actualisés lors du déplacement du classeur.

Le remède consiste à utiliser une fonction peu connue d’Excel, la fonction Lien.Hypertexte() :

Avec les formules présentées ci-dessus sous la forme de commentaires, nous avons enfin ce que nous voulions. En effet, les liens internes (tel celui de la cellule A3) font référence au classeur dans le répertoire actif, quel qu’il soit. Les liens externes (tel celui de la cellule B1) font référence à un classeur précis logé à un endroit précis sur un disque précis, et ne seront donc pas actualisés.

Remarque 1 – Seul problème potentiel : si l’on renomme le classeur, actuellement Hyper2.xls, cela ne modifiera pas son nom dans la formule de la cellule A3 puisque cet argument se trouve sous la forme de texte. Il faudrait donc, idéalement, remplacer ce nom par la référence à une cellule du classeur qui afficherait – de façon dynamique – le nom courant du classeur.

Remarque 2 – Il semblerait que cela ne marche pas quand le nom du classeur et ou de la feuille comporte au moins un espace, auquel cas la référence classeur-feuille est encadrée d'apostrophes. Exemple : =lien.hypertexte("'[LE nov-déc 2006.xls]Hyper 2'!$D$1","Aller en D1").

Un problème de lien hypertexte

Le 14 novembre, nous avons vu comment créer un lien hypertexte. Quatre jours plus tard, nous avons vu comment caler l’écran sur l’objectif du lien. Ce sont les deux seules fois où dans ce blog nous avons abordé le thème des liens hypertexte.

Nous allons aujourd’hui nous attaquer à un problème relatif à ces liens. Les deux copies d’écran ci-dessous indiquent les liens que nous avons définis dans les cellules B1 et A3.

Enregistrons le classeur puis, avec le Gestionnaire de fichiers de Windows, déplaçons-le à la racine du disque C: et enfin ouvrons-le dans Excel. Nous constatons avec horreur que les liens ont été modifiés, ce qui ne peut que provoquer des erreurs :

Qui est le fautif ? Encore une fois un réglage par défaut d’Excel dont on a le plus grand mal à imaginer le bien-fondé...

Il faut aller chercher dans Outils – Options – Général, cliquer dans le bouton
« Options Web » et enfin décocher la commande « Mettre à jour les liens lors de l’enregistrement », qui est cochée par défaut. Voici le résultat :

Dorénavant, les liens que vous définirez seront enfin stables !

Remarque 1 – Encore une fois, il s’agit d’un écran mal conçu ou mal traduit car – lorsque nous avons enregistré le classeur – il se trouvait bien sur le disque D: ! Les liens ont donc été actualisés non pas « lors de l’enregistre- ment », mais lors de la réouverture du classeur...

Remarque 2 – Après réflexion, la mise à jour par défaut était valable pour la référence au classeur lui-même, mais pas pour celle à d'autres classeurs. Ce qu'il faudrait en fait comme option, c'est que l'on puisse demander la mise à jour des liens uniquement pour les liens internes, c'est-à-dire les liens au classeur lui-même.

Masquer l’environnement Excel

Pour certaines applications, vous ne souhaitez pas laisser à l’utilisateur la possibilité d’accéder aux commandes usuelles d’Excel. Pour cela, il vous faut masquer la barre de menus, les barres d’outils, les en-têtes de ligne et de colonne, et le quadrillage. Au début, vous êtes dans la situation ci-dessous :

Après l’exécution de la macro VBA que nous avons écrite pour masquer l’environnement Excel, activée par le bouton ci-dessous, vous vous trouvez dans la situation suivante :

Voici la macro permettant d’atteindre notre objectif. Vous noterez que nous masquons aussi la barre d’outils « Dessin » que beaucoup d’utilisateurs laissent installée par défaut :

Remarque 1 – Avec cela, vous protégerez votre application contre l'utilisateur de base, mais pas contre un utilisateur confirmé d'Excel. Celui-ci en effet saura faire un clic droit dans la bande grisée puis passer par la commande
« Personnaliser » pour rétablir la barre de menus et les barres d’outils !

Remarque 2 – N'oubliez pas de créer le bouton associé à la macro, sinon il sera plus difficile de rétablir l'environnement nomral d'Excel.

Macro pour insertion de lignes

Un lecteur m’a posé le problème suivant. On a un certain nombre de valeurs en colonne A et l’on veut, chaque fois qu’une valeur est différente de la valeur suivante, insérer une ligne entre les deux valeurs.

Grosso modo, partant de l’état à gauche ci-dessus, on souhaite – après traitement par la macro – parvenir au résultat à droite ci-dessus.

Si vous êtes à l’aise dans les macros en VBA (Visual Basic for Applications), cela ne vous posera aucun problème. Voici ma solution :

Remarque – Telle qu’elle est écrite, cette macro suppose évidemment que le problème se pose dans la colonne A et que l’on démarre en A1. Si ce n’était pas le cas, il suffirait de remplacer Range("A1").Select par Range("Toto").Select où « Toto » serait le nom faisant référence à la cellule à partir de laquelle on souhaite démarrer le traitement.

Découvrez l'Analysis Collection

Dans mon dernier message, je vous ai présenté un des add-ins de Macro Systems. Voici la liste de leurs « Specialty Excel Add-ins », dans laquelle vous retrouvez le Link Finder en quatrième position :


Un virement de $ 189,95 vous permet d’acquérir l’Analysis Collection, qui comprend l’ensemble de ces add-ins, plus d’autres encore, 50 add-ins au total, ainsi que trois livres téléchargeables :
“Microsoft Excel Visual Basic Macros Made Easy”, pour l’apprentissage
“Microsoft Excel Visual Basic Macro Examples”, avec ses 1.200 macros
“Time Saving Microsoft Excel Solutions” résout de nombreux problèmes

Voici par exemple un écran représentant quelques-uns des 1.700 boutons que l’on peut récupérer grâce à l’add-in « Macro Button Assistant » :

Un add-in génial, le « Link Finder »

Excel est particulièrement pauvre en ce qui concerne la gestion des liens entre classeurs. La commande « Liaisons » du menu Edition est peu claire et sa fenêtre étriquée ne permet pas de bien identifier les liens existants :

En outre, l’identification – dans un classeur comportant de nombreuses feuilles – de toutes les cellules faisant référence à d’autres classeurs est un véritable travail de bénédictin. Sans parler du travail supplémentaire qui consistera peut-être ensuite à remplacer toutes ces références extérieures par les valeurs correspondantes si l’on souhaite « casser » les liens.

De nombreux liens externes parasites sont en effet créés de façon tout à fait involontaire par des utilisateurs qui copient un graphe d’un classeur à l’autre, qui coupent un bloc d’un classeur pour le coller dans un autre classeur, ...

Toutes ces recherches de liens deviennent nettement plus simples avec l’add-in « Link Finder » qui fait partie de l’add-in Spreadsheet Assistant développé par Macro Systems (www.add-ins.com). Son premier dialogue est le suivant :

Si vous validez le choix de la première option, le dialogue suivant vous permet de sélectionner le classeur dont vous voulez identifier les cellules liées, et de décider ce que vous ferez une fois les liens identifiés :

A mon avis, cet add-in à lui seul justifie l’achat du Spreadsheet Assistant dont il n’est pourtant que l’une des multiples macros complémentaires.

Trouver le classeur temporaire

Les utilisateurs, dans une entreprise, n’ont pas toujours le droit d’enregistrer leurs fichiers n’importe où sur leur PC : il n’ont souvent accès en écriture qu’à des endroits bien définis.

Microsoft réserve toujours sur votre PC un endroit pour loger les fichiers temporaires. La macro ci-dessous vous permet d’identifier son emplacement afin par exemple d’y enregistrer temporairement par macro certains résultats.


Remarque - N’oubliez pas- une fois que vous aurez recopié vos fichiers dans leur destination finale - d’ôter votre fichier de ce classeur temporaire, sinon il a tendance à enfler inutilement. De nombreux programmes en effet y logent des fichiers temporaires sans forcément avoir été programmés pour faire ensuite le nettoyage de ces fichiers