Comment rompre les liens dans Excel

La communication est une fonctionnalité très utile dans Excel. Après tout, très souvent, les utilisateurs doivent utiliser des informations provenant d'autres fichiers. Mais dans certaines situations, ils peuvent faire plus de mal que de bien. Après tout, par exemple, si vous envoyez ces fichiers par courrier, les liens ne fonctionnent pas. Aujourd'hui, nous parlerons plus en détail de ce qu'il faut faire pour éviter un tel problème.

Quelles sont les relations dans Excel

Les relations dans Excel sont très souvent utilisées en conjonction avec des fonctions telles que VPRpour obtenir des informations d'un autre classeur. Il peut prendre la forme d'un lien spécial contenant l'adresse non seulement de la cellule, mais également du livre dans lequel se trouvent les données. En conséquence, un tel lien ressemble à ceci : =RECHERCHEV(A2;'[Ventes 2018.xlsx]Rapport'!$A:$F;4;0). Ou, pour une représentation plus simple, représentez l'adresse sous la forme suivante : ='[Ventes 2018.xlsx]Rapport'!$A1. Analysons chacun des éléments de lien de ce type :

1. [Ventes 2018.xlsx]. Ce fragment contient un lien vers le fichier à partir duquel vous souhaitez obtenir des informations. On l'appelle aussi la source.
2. Photos. Nous avons utilisé le nom suivant, mais ce n'est pas le nom qui devrait être. Ce bloc contient le nom de la feuille dans laquelle vous devez rechercher des informations.
3. $A:$F et $A1 – l'adresse d'une cellule ou d'une plage contenant des données contenues dans ce document.

En fait, le processus de création d'un lien vers un document externe est appelé liaison. Après avoir enregistré l'adresse de la cellule contenue dans un autre fichier, le contenu de l'onglet "Données" change. À savoir, le bouton "Modifier les connexions" devient actif, à l'aide duquel l'utilisateur peut modifier les connexions existantes.

L'essence du problème

En règle générale, aucune difficulté supplémentaire ne survient pour utiliser les liens. Même si une situation se présente dans laquelle les cellules changent, tous les liens sont automatiquement mis à jour. Mais si vous renommez déjà le classeur lui-même ou le déplacez vers une autre adresse, Excel devient impuissant. Par conséquent, il produit le message suivant.

Ici, l'utilisateur a deux options possibles pour savoir comment agir dans cette situation. Il peut cliquer sur "Continuer" et les modifications ne seront pas mises à jour, ou il peut cliquer sur le bouton "Modifier les associations", avec lequel il peut les mettre à jour manuellement. Après avoir cliqué sur ce bouton, une fenêtre supplémentaire apparaîtra dans laquelle il sera possible de modifier les liens, indiquant où se trouve actuellement le fichier correct et comment il s'appelle.

De plus, vous pouvez modifier les liens via le bouton correspondant situé sur l'onglet "Données". L'utilisateur peut également découvrir que la connexion est interrompue par l'erreur #LINK, qui apparaît lorsqu'Excel ne peut pas accéder aux informations situées à une adresse spécifique en raison du fait que l'adresse elle-même n'est pas valide.

Comment dissocier Excel

L'une des méthodes les plus simples pour résoudre la situation décrite ci-dessus si vous ne pouvez pas mettre à jour vous-même l'emplacement du fichier lié consiste à supprimer le lien lui-même. Ceci est particulièrement facile à faire si le document ne contient qu'un seul lien. Pour ce faire, vous devez effectuer la séquence d'étapes suivante :

1. Ouvrez le menu "Données".
2. Nous trouvons la section "Connexions", et là - l'option "Modifier les connexions".
3. Après cela, cliquez sur "Dissocier".

Si vous avez l'intention d'envoyer ce livre à une autre personne, il est fortement recommandé de le faire à l'avance. Après tout, après avoir supprimé les liens, toutes les valeurs contenues dans un autre document seront automatiquement chargées dans le fichier, utilisées dans les formules, et au lieu de l'adresse de la cellule, les informations des cellules correspondantes seront simplement transformées en valeurs .

Comment dissocier tous les livres

Mais si le nombre de liens devient trop important, leur suppression manuelle peut prendre beaucoup de temps. Pour résoudre ce problème en une seule fois, vous pouvez utiliser une macro spéciale. C'est dans l'addon VBA-Excel. Vous devez l'activer et vous rendre dans l'onglet du même nom. Il y aura une section "Liens", dans laquelle nous devons cliquer sur le bouton "Casser tous les liens".

Code VBA

S'il n'est pas possible d'activer ce module complémentaire, vous pouvez créer vous-même une macro. Pour ce faire, ouvrez l'éditeur Visual Basic en appuyant sur les touches Alt + F11, et écrivez les lignes suivantes dans le champ de saisie de code.

Sous UnlinkWorkBooks()

    Dim WbLiens

    Dim et aussi longtemps

    Sélectionnez Case MsgBox ("Toutes les références à d'autres livres seront supprimées de ce fichier et les formules faisant référence à d'autres livres seront remplacées par des valeurs." & vbCrLf & "Êtes-vous sûr de vouloir continuer ?", 36, "Dissocier ?" )

    Cas 7′ Non

        Exit Sub

    End Select

    WbLinks = ActiveWorkbook.LinkSources(Type :=xlLinkTypeExcelLinks)

    Si non est vide (WbLinks) alors

        Pour i = 1 Vers UBound(WbLinks)

            ActiveWorkbook.BreakLink Nom :=WbLinks(i), Type :=xlLinkTypeExcelLinks

        Suivant

    d'autre

        MsgBox "Il n'y a pas de liens vers d'autres livres dans ce fichier.", 64, "Liens vers d'autres livres"

    Si fin

End Sub

Comment rompre les liens uniquement dans la plage sélectionnée

De temps en temps, le nombre de liens est très important, et l'utilisateur a peur qu'après avoir supprimé l'un d'entre eux, il ne soit pas possible de tout renvoyer si certains étaient superflus. Mais c'est un problème facile à éviter. Pour ce faire, vous devez sélectionner la plage dans laquelle supprimer les liens, puis les supprimer. Pour ce faire, vous devez effectuer la séquence d'actions suivante :

1. Sélectionnez le jeu de données à modifier.
2. Installez le module complémentaire VBA-Excel, puis accédez à l'onglet approprié.
3. Ensuite, nous trouvons le menu "Liens" et cliquez sur le bouton "Casser les liens dans les plages sélectionnées".

Après cela, tous les liens dans l'ensemble de cellules sélectionné seront supprimés.

Que faire si les liens ne sont pas rompus

Tout ce qui précède sonne bien, mais dans la pratique, il y a toujours des nuances. Par exemple, il peut y avoir une situation où les liens ne sont pas rompus. Dans ce cas, une boîte de dialogue apparaît toujours indiquant qu'il n'est pas possible de mettre à jour automatiquement les liens. que-faire dans cette situation?

1. Tout d'abord, vous devez vérifier si des informations sont contenues dans les plages nommées. Pour ce faire, appuyez sur la combinaison de touches Ctrl + F3 ou ouvrez l'onglet "Formules" - "Gestionnaire de noms". Si le nom du fichier est complet, il vous suffit de le modifier ou de le supprimer complètement. Avant de supprimer des plages nommées, vous devez copier le fichier à un autre emplacement afin de pouvoir revenir à la version d'origine si vous avez pris les mauvaises mesures.
2. Si vous ne pouvez pas résoudre le problème en supprimant les noms, vous pouvez vérifier la mise en forme conditionnelle. Les cellules d'un autre tableau peuvent être référencées dans des règles de mise en forme conditionnelle. Pour ce faire, recherchez l'élément correspondant dans l'onglet "Accueil", puis cliquez sur le bouton "Gestion des fichiers".

   Normalement, Excel ne vous donne pas la possibilité de donner l'adresse d'autres classeurs dans une mise en forme conditionnelle, mais vous le faites si vous faites référence à une plage nommée avec une référence à un autre fichier. Habituellement, même après la suppression du lien, le lien reste. Il n'y a aucun problème à supprimer un tel lien, car le lien ne fonctionne en fait pas. Par conséquent, rien de grave ne se produira si vous le supprimez.

Vous pouvez également utiliser la fonction "Data Check" pour savoir s'il existe des liens inutiles. Les liens restent généralement si le type de validation de données "Liste" est utilisé. Mais que faire s'il y a beaucoup de cellules ? Est-il vraiment nécessaire de vérifier chacun d'eux de manière séquentielle ? Bien sûr que non. Après tout, cela prendra beaucoup de temps. Par conséquent, vous devez utiliser un code spécial pour le sauvegarder de manière significative.

Option explicite

'—————————————————————————————

' Auteur : The_Prist(Shcherbakov Dmitry)

' Développement professionnel d'applications pour MS Office de toute complexité

' Animation de formations sur MS Excel

' https://www.excel-vba.ru

' [e-mail protégé]

'WebMoney—R298726502453; Yandex.Money — 41001332272872

' Objectif:

'—————————————————————————————

Sous FindErrLink()

    'nous devons regarder dans le lien Data -Change links vers le fichier source

    'et mettre les mots-clés ici en minuscules (partie du nom du fichier)

    'l'astérisque remplace simplement n'importe quel nombre de caractères afin que vous n'ayez pas à vous soucier du nom exact

    Const sToFndLink$ = "*ventes 2018*"

    Dim rr Comme plage, rc Comme plage, rres Comme plage, s$

    'définir toutes les cellules avec la validation des données

    On Error Resume Next

    Définir rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)

    Si rr n'est rien alors

        MsgBox "Il n'y a pas de cellules avec validation des données sur la feuille active", vbInformation, "www.excel-vba.ru"

        Exit Sub

    Si fin

    En cas d'erreur GoTo 0

    'vérifier chaque cellule pour les liens

    Pour chaque rc dans rr

        'juste au cas où, nous ignorons les erreurs - cela peut aussi arriver

        'mais nos relations doivent être sans eux et ils seront certainement trouvés

        s = ""

        On Error Resume Next

        s = rc.Validation.Formule1

        En cas d'erreur GoTo 0

        'trouvé - nous collectons tout dans une gamme séparée

        Si LCase(s) Like sToFndLink Alors

            Si rres n'est rien alors

                Définir rres = rc

            d'autre

                Définir rres = Union (rc, rres)

            Si fin

        Si fin

    Suivant

    's'il y a une connexion, sélectionnez toutes les cellules avec de telles vérifications de données

    Si non rres n'est rien alors

        rres.Sélectionner

' rres.Interior.Color = vbRed ' si vous voulez mettre en évidence avec de la couleur

    Si fin

End Sub

Il faut faire un module standard dans l'éditeur de macros, puis y insérer ce texte. Après cela, appelez la fenêtre de macro en utilisant la combinaison de touches Alt + F8, puis sélectionnez notre macro et cliquez sur le bouton "Exécuter". Il y a quelques points à garder à l'esprit lors de l'utilisation de ce code :

1. Avant de rechercher un lien qui n'est plus pertinent, vous devez d'abord déterminer à quoi ressemble le lien par lequel il est créé. Pour ce faire, allez dans le menu "Données" et recherchez-y l'élément "Modifier les liens". Après cela, vous devez regarder le nom du fichier et le spécifier entre guillemets. Par exemple, comme ceci : Const sToFndLink$ = "*ventes 2018*"
2. Il est possible d'écrire le nom pas en entier, mais simplement de remplacer les caractères inutiles par un astérisque. Et entre guillemets, écrivez le nom du fichier en minuscules. Dans ce cas, Excel trouvera tous les fichiers contenant une telle chaîne à la fin.
3. Ce code est uniquement capable de vérifier les liens dans la feuille actuellement active.
4. Avec cette macro, vous ne pouvez sélectionner que les cellules qu'elle a trouvées. Vous devez tout supprimer manuellement. C'est un plus, car vous pouvez tout revérifier à nouveau.
5. Vous pouvez également mettre les cellules en surbrillance dans une couleur spéciale. Pour ce faire, supprimez l'apostrophe avant cette ligne. rres.Interior.Color = vbRed

Habituellement, après avoir suivi les étapes décrites dans les instructions ci-dessus, il ne devrait plus y avoir de connexions inutiles. Mais s'il y en a dans le document et que vous ne parvenez pas à les supprimer pour une raison ou une autre (un exemple typique est la sécurité des données dans une feuille), vous pouvez utiliser une séquence d'actions différente. Cette instruction n'est valable que pour les versions 2007 et supérieures.

1. Nous créons une copie de sauvegarde du document.
2. Ouvrez ce document à l'aide de l'archiveur. Vous pouvez utiliser n'importe quel format prenant en charge le format ZIP, mais WinRar fonctionnera également, ainsi que celui intégré à Windows.
3. Dans l'archive qui apparaît, vous devez trouver le dossier xl, puis ouvrir externalLinks.
4. Ce dossier contient tous les liens externes, chacun correspondant à un fichier de la forme externalLink1.xml. Tous ne sont que numérotés et l'utilisateur n'a donc pas la possibilité de comprendre de quel type de connexion il s'agit. Pour comprendre quel type de connexion, vous devez ouvrir le dossier _rels et y regarder.
5. Après cela, nous supprimons tous les liens ou des liens spécifiques, en fonction de ce que nous apprenons dans le fichier externalLinkX.xml.rels.
6. Après cela, nous ouvrons notre fichier à l'aide d'Excel. Il y aura des informations sur une erreur comme "Erreur dans une partie du contenu du livre". Nous donnons notre consentement. Après cela, une autre boîte de dialogue apparaîtra. Nous le fermons.

Après cela, tous les liens doivent être supprimés.