Koppelingen verbreken in Excel

Communicatie is een zeer handige functie in Excel. Gebruikers moeten immers heel vaak informatie uit andere bestanden gebruiken. Maar in sommige situaties kunnen ze meer kwaad dan goed doen. Als u deze bestanden bijvoorbeeld per post verstuurt, werken de links niet. Vandaag zullen we in meer detail praten over wat te doen om een ​​dergelijk probleem te voorkomen.

Wat zijn relaties in Excel

Relaties in Excel worden heel vaak gebruikt in combinatie met functies zoals: VPRom informatie uit een andere werkmap te halen. Het kan de vorm aannemen van een speciale link die het adres bevat van niet alleen de cel, maar ook het boek waarin de gegevens zich bevinden. Als resultaat ziet zo'n link er ongeveer zo uit: =VLOOKUP(A2;'[Verkoop 2018.xlsx]Rapport'!$A:$F;4;0). Of, voor een eenvoudigere weergave, geef het adres weer in de volgende vorm: ='[Verkoop 2018.xlsx]Rapport'!$A1. Laten we elk van de linkelementen van dit type analyseren:

1. [Verkoop 2018.xlsx]. Dit fragment bevat een link naar het bestand waaruit u informatie wilt halen. Het wordt ook wel de bron genoemd.
2. Foto's. We gebruikten de volgende naam, maar dit is niet de naam die zou moeten zijn. Dit blok bevat de naam van het blad waarin u informatie moet zoeken.
3. $A:$F en $A1 – het adres van een cel of bereik met gegevens in dit document.

Eigenlijk wordt het proces van het maken van een link naar een extern document linken genoemd. Nadat we het adres van de cel in een ander bestand hebben geregistreerd, verandert de inhoud van het tabblad "Gegevens". De knop "Verbindingen wijzigen" wordt namelijk actief, met behulp waarvan de gebruiker de bestaande verbindingen kan bewerken.

De essentie van het probleem

In de regel ontstaan ​​er geen extra moeilijkheden om links te gebruiken. Ook als zich een situatie voordoet waarin de cellen veranderen, dan worden alle links automatisch bijgewerkt. Maar als u de werkmap zelf al hernoemt of naar een ander adres verplaatst, wordt Excel machteloos. Daarom produceert het de volgende melding.

Hier heeft de gebruiker twee mogelijke opties om in deze situatie te handelen. Hij kan op "Doorgaan" klikken en dan worden de wijzigingen niet bijgewerkt, of hij kan op de knop "Verbindingen wijzigen" klikken, waarmee hij ze handmatig kan bijwerken. Nadat we op deze knop hebben geklikt, verschijnt er een extra venster waarin het mogelijk is om de links te wijzigen, waarbij wordt aangegeven waar het juiste bestand zich op dit moment bevindt en hoe het heet.

Bovendien kunt u links bewerken via de overeenkomstige knop op het tabblad "Gegevens". De gebruiker kan er ook achter komen dat de verbinding is verbroken door de #LINK-fout, die verschijnt wanneer Excel geen toegang heeft tot informatie op een specifiek adres omdat het adres zelf ongeldig is.

Hoe ontkoppelen in Excel

Een van de eenvoudigste methoden om de hierboven beschreven situatie op te lossen als u de locatie van het gekoppelde bestand niet zelf kunt bijwerken, is door de link zelf te verwijderen. Dit is vooral gemakkelijk te doen als het document slechts één link bevat. Om dit te doen, moet u de volgende reeks stappen uitvoeren:

1. Open het menu "Gegevens".
2. We vinden de sectie "Verbindingen", en daar - de optie "Verbindingen wijzigen".
3. Klik daarna op "Ontkoppelen".

Als u van plan bent dit boek naar iemand anders te mailen, is het ten zeerste aan te raden dit van tevoren te doen. Immers, na het verwijderen van de koppelingen worden alle waarden die in een ander document zijn opgenomen automatisch in het bestand geladen, gebruikt in formules, en in plaats van het celadres wordt de informatie in de bijbehorende cellen eenvoudig omgezet in waarden .

Alle boeken ontkoppelen

Maar als het aantal links te groot wordt, kan het handmatig verwijderen ervan lang duren. Om dit probleem in één keer op te lossen, kun je een speciale macro gebruiken. Het zit in de VBA-Excel-add-on. U moet het activeren en naar het tabblad met dezelfde naam gaan. Er zal een sectie "Links" zijn, waarin we op de knop "Alle links breken" moeten klikken.

VBA-code

Als het niet mogelijk is om deze add-on te activeren, kunt u zelf een macro maken. Open hiervoor de Visual Basic-editor door op de toetsen Alt + F11 te drukken en schrijf de volgende regels in het code-invoerveld.

Sub OntkoppelWerkboeken()

    Dim WbLinks

    Dim en zo lang

    Selecteer Case MsgBox ("Alle verwijzingen naar andere boeken worden uit dit bestand verwijderd en formules die naar andere boeken verwijzen, worden vervangen door waarden." & vbCrLf & "Weet u zeker dat u wilt doorgaan?", 36, "Ontkoppelen?" )

    Geval 7′ Nee

        Exit Sub

    End Select

    WbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

    Indien niet leeg (WbLinks) dan:

        Voor i = 1 Naar UBound (WbLinks)

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

        Volgende

    anders

        MsgBox "Er zijn geen links naar andere boeken in dit bestand.", 64, "Links naar andere boeken"

    End If

End Sub

Alleen banden in het geselecteerde bereik verbreken

Van tijd tot tijd is het aantal links erg groot en de gebruiker is bang dat het na het verwijderen van een van hen niet mogelijk zal zijn om alles terug te sturen als sommige overbodig waren. Maar dit is een probleem dat gemakkelijk te vermijden is. Om dit te doen, moet u het bereik selecteren waarin u koppelingen wilt verwijderen en deze vervolgens verwijderen. Om dit te doen, moet u de volgende reeks acties uitvoeren:

1. Selecteer de dataset die moet worden gewijzigd.
2. Installeer de VBA-Excel-add-on en ga vervolgens naar het juiste tabblad.
3. Vervolgens vinden we het menu "Links" en klikken we op de knop "Links in de geselecteerde bereiken verbreken".

Daarna worden alle koppelingen in de geselecteerde set cellen verwijderd.

Wat te doen als de banden niet zijn verbroken?

Bovenstaande klinkt allemaal goed, maar in de praktijk zijn er altijd wel wat nuances. Er kan bijvoorbeeld een situatie zijn waarin de banden niet worden verbroken. In dit geval verschijnt nog steeds een dialoogvenster waarin staat dat het niet mogelijk is om de koppelingen automatisch bij te werken. Wat te doen in deze situatie?

1. Eerst moet u controleren of er informatie is opgenomen in de genoemde bereiken. Om dit te doen, drukt u op de toetsencombinatie Ctrl + F3 of opent u het tabblad "Formules" - "Name Manager". Als de bestandsnaam vol is, hoeft u deze alleen maar te bewerken of helemaal te verwijderen. Voordat u benoemde bereiken verwijdert, moet u het bestand naar een andere locatie kopiëren, zodat u kunt terugkeren naar de oorspronkelijke versie als de verkeerde stappen zijn genomen.
2. Als u het probleem niet kunt oplossen door namen te verwijderen, kunt u de voorwaardelijke opmaak controleren. Er kan naar cellen in een andere tabel worden verwezen in voorwaardelijke opmaakregels. Om dit te doen, zoekt u het overeenkomstige item op het tabblad "Home" en klikt u vervolgens op de knop "Bestandsbeheer".

   Normaal gesproken geeft Excel u niet de mogelijkheid om het adres van andere werkmappen in voorwaardelijke opmaak te geven, maar u doet dat wel als u naar een benoemd bereik verwijst met een verwijzing naar een ander bestand. Meestal, zelfs nadat de link is verwijderd, blijft de link bestaan. Het is geen probleem om zo'n link te verwijderen, want de link werkt namelijk niet. Daarom zal er niets ergs gebeuren als u het verwijdert.

U kunt ook de functie "Data Check" gebruiken om te zien of er onnodige links zijn. Links blijven meestal behouden als het type "Lijst" van gegevensvalidatie wordt gebruikt. Maar wat te doen als er veel cellen zijn? Is het echt nodig om ze allemaal achter elkaar te controleren? Natuurlijk niet. Het zal immers erg lang duren. Daarom moet u een speciale code gebruiken om deze aanzienlijk op te slaan.

Optie Expliciet

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

' Auteur: The_Prist (Shcherbakov Dmitry)

' Professionele ontwikkeling van applicaties voor MS Office van elke complexiteit

' Trainingen geven op MS Excel

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

' [e-mail beveiligd]

'WebMoney-R298726502453; Yandex.Geld — 41001332272872

' Doel:

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

Sub ZoekErrLink()

    'we moeten kijken in de link Data -Change links naar het bronbestand'

    'en zet de trefwoorden hier in kleine letters (een deel van de bestandsnaam)

    'asterisk vervangt gewoon een willekeurig aantal tekens, zodat u zich geen zorgen hoeft te maken over de exacte naam

    Const sToFndLink$ = "*verkoop 2018*"

    Dim rr als bereik, rc als bereik, rres als bereik, s$

    'definieer alle cellen met gegevensvalidatie'

    On Error Resume Next

    Stel rr = ActiveSheet.UsedRange.SpecialCells (xlCellTypeAllValidation) in

    Als rr niets is, dan

        MsgBox "Er zijn geen cellen met gegevensvalidatie op het actieve blad", vbInformation, "www.excel-vba.ru"

        Exit Sub

    End If

    On Error GoTo 0

    'controleer elke cel op links'

    Voor elke rc In rr

        'voor het geval dat we fouten overslaan - dit kan ook gebeuren'

        'maar onze connecties moeten zonder hen zijn en ze zullen zeker worden gevonden

        z = «»

        On Error Resume Next

        s = rc.Validatie.Formule1

        On Error GoTo 0

        'gevonden – we verzamelen alles in een apart assortiment'

        Als LCase(s) sToFndLink leuk vinden, dan

            Als rres niets is, dan

                Stel rres = rc . in

            anders

                Stel rres = Unie in (rc, rres)

            End If

        End If

    Volgende

    'als er een verbinding is, selecteer dan alle cellen met dergelijke gegevenscontroles'

    Als niet rres niets is, dan

        rres.Selecteer

' rres.Interior.Color = vbRed 'als je wilt markeren met kleur

    End If

End Sub

Het is noodzakelijk om in de macro-editor een standaardmodule te maken en deze tekst daar in te voegen. Roep daarna het macrovenster op met de toetsencombinatie Alt + F8, selecteer vervolgens onze macro en klik op de knop "Uitvoeren". Er zijn een paar dingen om in gedachten te houden bij het gebruik van deze code:

1. Voordat u een link zoekt die niet meer relevant is, moet u eerst bepalen hoe de link waarmee deze is gemaakt eruitziet. Ga hiervoor naar het menu "Gegevens" en zoek daar het item "Links wijzigen". Daarna moet u naar de bestandsnaam kijken en deze tussen aanhalingstekens specificeren. Bijvoorbeeld als volgt: Const sToFndLink$ = "*verkoop 2018*"
2. Het is mogelijk om de naam niet voluit te schrijven, maar overbodige tekens te vervangen door een asterisk. En schrijf tussen aanhalingstekens de bestandsnaam in kleine letters. In dit geval vindt Excel aan het einde alle bestanden die zo'n string bevatten.
3. Deze code kan alleen controleren op links in het blad dat momenteel actief is.
4. Met deze macro kunt u alleen de cellen selecteren die hij heeft gevonden. Je moet alles handmatig verwijderen. Dit is een pluspunt, want je kunt alles nog een keer dubbelchecken.
5. U kunt de cellen ook in een speciale kleur markeren. Om dit te doen, verwijdert u de apostrof voor deze regel. rres.Interieur.Kleur = vbRood

Nadat u de stappen hebt voltooid die in de bovenstaande instructies zijn beschreven, zouden er normaal gesproken geen onnodige verbindingen meer moeten zijn. Maar als er enkele in het document staan ​​en u kunt ze om de een of andere reden niet verwijderen (een typisch voorbeeld is de beveiliging van gegevens in een blad), dan kunt u een andere volgorde van acties gebruiken. Deze instructie is alleen geldig voor versies 2007 en hoger.

1. We maken een reservekopie van het document.
2. Open dit document met de archiver. Je kunt alles gebruiken dat het ZIP-formaat ondersteunt, maar WinRar zal ook werken, evenals degene die in Windows is ingebouwd.
3. In het archief dat verschijnt, moet u de xl-map vinden en vervolgens externalLinks openen.
4. Deze map bevat alle externe links, die elk overeenkomen met een bestand van de vorm externalLink1.xml. Ze zijn allemaal alleen genummerd en daarom heeft de gebruiker niet de mogelijkheid om te begrijpen wat voor soort verbinding dit is. Om te begrijpen wat voor soort verbinding, moet je de map _rels openen en daar kijken.
5. Daarna verwijderen we alle of specifieke links, op basis van wat we leren in het bestand externalLinkX.xml.rels.
6. Daarna openen we ons bestand met Excel. Er zal informatie zijn over een fout zoals "Fout in een deel van de inhoud in het Boek." Wij geven toestemming. Daarna verschijnt een ander dialoogvenster. Wij sluiten het.

Daarna moeten alle links worden verwijderd.