Avez-vous besoin de partager vos fichiers Excel? Avez-vous besoin d'utiliser vos fichiers Excel aux différents endroits? En avez-vous assez des messages vous demandant "d'actualiser les liens" ou vous informant que "les liens externes sont indisponibles"? Vous êtes au bon endroit car vous trouverez ici la solution à toutes ces situations: supprimer les liaisons Excel.
Les liaisons externes dans des fichiers Excel
Qu'est-ce "une liaison" en Excel? Il s'agit d'un lien que différents Classeurs peuvent avoir entre eux – le plus simple sera de l'illustrer sur un exemple:
Lier les fichiers Excel entre eux est une pratique courante qui, dans la plupart de situations, est pratique, facile, parfois indispensable. Elle vous permet principalement de garder vos données à jour même si elles viennent de différents Classeurs.
Mais cette solution peut être également très limitante: si les fichiers externes auxquels votre fichier Excel est lié ne sont pas disponibles, au mieux vous vous en sortez avec des messages d'avertissement, au pire le contenu qui n'est pas mis à jour faussera vos calculs. La solution?
Remplacer les liens par les valeurs avant de partager/déplacer un fichier Excel
Pour éviter les problèmes lors de partage, déplacement, envoi par email,… d'un fichier Excel, vous pouvez remplacer les valeurs liés (provenant d'autres Classeurs donc) par les valeurs "normales". Votre fichier ne sera plus lié à d'autres fichiers Excel et il sera possible de l'utiliser sans devoir se demander quelle valeur est obsolète et laquelle ne l'est pas.
Du point de vue pratique, cette solution peut être très fastidieuse si vous devez l'appliquer manuellement – trouver les cellules qui contiennent des liens, les remplacer, ne rien oublier… Car vous ne pouvez pas simplement sélectionner toutes les cellules de la Feuille et les "copier / coller des valeurs" – car vous perdriez toutes vos formules (même celles qui n'utilisent pas des données externes)!
Heureusement, VBA est là pour tout automatiser et éviter "l'erreur humaine".
VBA: supprimer les liens vers d'autres fichiers Excel
Le code VBA qui suit est très pratique, surtout si d'autres personnes sont susceptibles de lire votre fichier. Rompre les liaisons existantes va prévenir la situation où le fichier essaie de récupérer des données dans d'autres Classeurs auquel il n'a pas accès – car dans une telle situation, votre Classeur est inutilisable.
En rompant les liaisons, le contenu des cellules qui provenait des autres Classeurs est remplacé par des valeurs. D'un côté vous perdez la possibilité de tenir le fichier à jour automatiquement mais de l'autre côté, vous évitez les fenêtres pop-up vous demandant s'il faut essayer de mettre les données à jour. Et, ce qui peut être important également, vous ne montrez pas au monde la structure des dossiers de votre ordinateur. Ce qui, à nos jours, ne peut pas faire du mal du point de vue de la sécurité.
Et voici le résultat une fois les liaisons rompues:
Procédure VBA pour supprimer toutes les liaisons vers d'autres Classeurs Excel
Voici le code source VBA qui vous permettra de supprimer toutes les liaisons que le Classeur en question a avec d'autres Classeurs.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Sub SupprimerLiaisons() 'par Excel-Malin.com ( https://excel-malin.com/ ) Dim Liaisons As Variant Liaisons = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks) If IsEmpty(Liaisons) = True Then Exit Sub For LiaisonsTrouvee = 1 To UBound(Liaisons) ActiveWorkbook.BreakLink _ Name:=Liaisons(LiaisonsTrouvee), _ Type:=xlLinkTypeExcelLinks Next LiaisonsTrouvee End Sub |
Il est, bien sûr possible de modifier cette procédure pour que le Classeur "à nettoyer" soit utilisé comme argument en remplaçant ActiveWorkbook.
Ainsi, vous pouvez utiliser cette macro également pour des "Classeurs classiques" (fichiers .XLSX) sans devoir les modifier.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Sub SupprimerLiaisons(MonClasseur As Workbook) 'par Excel-Malin.com ( https://excel-malin.com/ ) Dim Liaisons As Variant Liaisons = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks) If IsEmpty(Liaisons) = True Then Exit Sub For LiaisonsTrouvee = 1 To UBound(Liaisons) MonClasseur.BreakLink _ Name:=Liaisons(LiaisonsTrouvee), _ Type:=xlLinkTypeExcelLinks Next LiaisonsTrouvee End Sub |
Pour information 2: la page sur la méthode .LinkSources sur le site de Microsoft
Pour aller plus loin en VBA
J'espère que ce code va bien vous servir. Et voici quelques articles sur VBA qui pourraient vous être utiles également…
- Liste de toutes les fonctions disponibles en VBA
- Utilisation de RECHERCHEV en VBA
- Envoyer un email avec Excel (via VBA)
- Trouver la dernière cellule non-vide
- Comment débloquer les fichiers XLSM provenant d'email ou d'internet
11 commentaires sur “VBA: supprimer les liaisons Excel”
Bonjour,
Lorsque je fais, par macro et la méthode Copy … paste d'une plage, d'un fichier source vers les 22 autres fichiers destinations (avec une boucle), Excel ( le vilain!) crée automatiquement une liaison avec ce fichier source, mais seulement dans le ou les fichiers acceptant cette modification. Evidemment, plus tard, quand j'ouvre un fichier dest j'ai droit à la fenêtre ( "activer les liaisons"….) Avec ta macro, je peux résoudre le problème en "supprimant les liens créés". J'ai aussi résolu le problème en modifiant la "source" par le fichier lui-même comme source. Ca me parait peu satisfaisant.
Mais je pense qu'il y a un "loup" dans ma macro. Peux-tu y regarder et me corriger ? Ci dessous la partie de macro concernée (qui fonctionne par ailleurs très bien).
'—————–
For Each cel In OS.Range("BA2:BA" & derligne)
If cel.Value = "aaa" Or cel.Value = "AAA" Then 'condition dans Source
lig = cel.Row 'n° lig
Set nom = OS.Range("C" & lig) 'Nom à chercher
Set Fiche = OS.Range("D" & lig & ":AW" & lig) 'Sélection à copier
Fiche.Copy
OD.Activate 'Classeur Destination
derl = OD.Range("C" & Rows.Count).End(xlUp).Row
Set LeNom = OD.Range("C2:C" & derl).Find(nom, LookAt:=xlWhole) 'Trouver le même nom
If LeNom Is Nothing Then 'si le nom cherché n'existe pas, chercher le second ("aaa")
On Error Resume Next
Else
'If LeNom = Nom Then 'si le nom est trouvé
lig2 = LeNom.Row ' sa ligne
Range("D" & lig2).Select 'Cellule Destination
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
End If
'—————–
Merci.
Bonjour,
J'ai un souci concernant des liaisons sur un classeur. A l'ouverture de ce fichier (qui est partagé avec d'autres utilisateurs pour information), si j'active le contenu, je dois passer alors une centaine de messages d'erreurs comme quoi Excel ne trouve pas le lien vers des fichiers.
Le problème est que ces fichiers ne sont pas des fichiers Excels mais d'autres fichiers (word, images, etc.). Donc il m'est impossible de trouver ces liaisons de manière simple.
Donc ma question est : comme pourrais je lister l'ensemble des liens que Excel souhaite mettre à jour et les supprimer ? Et pas seulement les liens vers d'autres classeurs.
Merci beaucoup, cela fait un moment que je cherche.
Cordialement
Pardon petite màj. Ce sont bien des fichiers Xcel sur lequel mon fichier pointe. Mais ceux ci ne s'affichent pas lorque l'on clique sur "Modifier les liaisons" ni lorsque je fais tourner votre macro. Par contre des messages d'erreur s'affichent lorsque l'on active le contenu. Je ne comprends pas
Bonjour!
Pareil, j'ai des liens vers d'autres fichiers .xlsm que je ne parviens pas à détruire…
j'ai copié votre code, je l'ai fait tourner, mais malgré tout, pas moyen :-S
Vous auriez une autre idée?
merci bcp!
Bonjour Sophie,
c'est assez difficile de dire sans avoir le cas précis sous les yeux.
1) vous avez fait tourner laquelle des deux macros? Si la première, êtes vous sûre que le fichier en question était actif au moment de faire tourner la macro?
2) le code supprime certains des liens ou il ne supprime rien du tout?
3) est-ce un PC (pas Mac)? Quelle version de Excel (Office)?
4) …
Comme vous voyez, les raisons peuvent être multiples.
Sinon, si la situation (et la nature de votre fichier) le permet, je peux éventuellement jeter un coup d'oeil pour voir pourquoi cela ne marche pas.
Pour cela, vous pouvez m'envoyer le fichier à contact@excel-malin.com
Cordialement, Martin
On peut remplacer 1 par UBound(Liaisons)
Bonjour Bilel,
je suppose que vous voulez dire
LBound(Liaisons)…En effet, c'est une bonne remarque!
Cordialement, Martin
Bonjour, je rebondi sur le sujet pour parler d'un problème similaire:
Dans un classeur, j'ai une feuille qui contiens des valeurs numériques (single ou double). J'importe cette feuille via une macro sans changer son nom (je précise que je delete la feuille existante avant la copie)
les autres feuilles de ce classeur font normalement référence a cette feuille de valeur numérique sheets("data") pour effectuer des calculs de base (valeur d'une cells + valeur d'une autre avec le résultat sur une feuille résultat sheets("result").
Problème: la cellule dans la feuille résultat perd sa ref et donne un : =#REF!$F$62 par exemple
comment mettre a jour cette feuille de résultat avec la feuille "data" nouvellement importé?
Merci
Bjour!
Macro fonctionne OK pour supprimer liaisons valides
Mais les liaisons "fantômes" vers des fichiers qui n'existent pas ou plus ne sont pas supprimées !
Peste…
Saluts amicaux
Chrigu
Bonjour,
Votre macro fonctionne super quand les liens pointent vers un fichier externe, merci
Par contre j'aimerais faire la même chose mais sur des liens internes qui utilisent tous la même fonction excell "RechercheV"
=RECHERCHEV($Q$20;Adresses!$A$2:$K$1000;5)Existe-t-il une solution simple pour tenter de résoudre mon soucis ?
Merci déjà d'avance pour l'aide
Bonjour Michel,
voici le code qui fera l'affaire. Il est prévu de tourner pour une sélection des cellules. Si vous voulez, vous pouvez l'adapter pour changer le choix des cellules pour lesquelles il doit tourner.
J'espère que cela vous aidera.
Cordialement, Martin