Як розірвати посилання в Excel

Спілкування є дуже корисною функцією в Excel. Адже дуже часто користувачам доводиться використовувати інформацію з інших файлів. Але в деяких ситуаціях вони можуть принести більше шкоди, ніж користі. Адже, наприклад, якщо відправити ці файли поштою, посилання не працюють. Сьогодні ми докладніше поговоримо про те, що робити, щоб уникнути такої проблеми.

Що таке зв'язки в Excel

Зв’язки в Excel дуже часто використовуються в поєднанні з такими функціями, як VPRотримати інформацію з іншої книги. Він може мати форму спеціального посилання, що містить адресу не тільки комірки, але і книги, в якій знаходяться дані. У підсумку таке посилання виглядає приблизно так: =VLOOKUP(A2;'[Продажі 2018.xlsx]Звіт'!$A:$F;4;0). Або, для простішого представлення, представте адресу в такій формі: ='[Продажі 2018.xlsx]Звіт'!$A1. Розберемо кожен з елементів посилання цього типу:

  1. [Продажі 2018.xlsx]. Цей фрагмент містить посилання на файл, з якого потрібно отримати інформацію. Його також називають джерелом.
  2. фотографії. Ми використали наступну назву, але це не та назва, яка має бути. У цьому блоці міститься назва аркуша, на якому потрібно знайти інформацію.
  3. $A:$F і $A1 – адреса комірки або діапазону, що містить дані, які містяться в цьому документі.

Власне, процес створення посилання на зовнішній документ називається посиланням. Після того, як ми прописали адресу комірки, що міститься в іншому файлі, вміст вкладки «Дані» змінюється. А саме, стає активною кнопка «Змінити підключення», за допомогою якої користувач може редагувати наявні підключення.

Суть проблеми

Як правило, ніяких додаткових труднощів при використанні посилань не виникає. Навіть якщо виникає ситуація, коли осередки змінюються, то всі посилання оновлюються автоматично. Але якщо ви вже перейменуєте саму книгу або перемістите її за іншою адресою, Excel стає безсилим. Таким чином, він створює таке повідомлення.

Як розірвати посилання в Excel

Тут у користувача є два можливих варіанти дій у цій ситуації. Він може натиснути «Продовжити», і тоді зміни не будуть оновлені, або він може натиснути кнопку «Змінити асоціації», за допомогою якої він зможе оновити їх вручну. Після того, як ми натиснемо цю кнопку, з'явиться додаткове вікно, в якому можна буде змінити посилання, вказавши, де в даний момент знаходиться правильний файл і як він називається.

Як розірвати посилання в Excel

Крім того, ви можете редагувати посилання за допомогою відповідної кнопки, розташованої на вкладці «Дані». Користувач також може дізнатися, що з’єднання розірване через помилку #LINK, яка з’являється, коли Excel не може отримати доступ до інформації, розташованої за певною адресою, через те, що сама адреса недійсна.

Як відключити в excel

Один із найпростіших способів вирішення описаної вище ситуації, якщо ви не можете самостійно оновити розташування файлу за посиланням, — це видалити саме посилання. Це особливо легко зробити, якщо документ містить лише одне посилання. Для цього необхідно виконати наступну послідовність дій:

  1. Відкрийте меню «Дані».
  2. Знаходимо розділ «Підключення», а там – опцію «Змінити підключення».
  3. Після цього натисніть «Від’єднати».

Якщо ви збираєтеся надіслати цю книгу іншій особі, настійно рекомендуємо зробити це заздалегідь. Адже після видалення посилань всі значення, які містяться в іншому документі, будуть автоматично завантажені в файл, використані у формулах, а замість адреси комірки інформація у відповідних комірках буде просто перетворена в значення .

Як від’єднати всі книги

Але якщо кількість посилань стає занадто великою, їх видалення вручну може зайняти багато часу. Щоб вирішити цю проблему за один раз, можна скористатися спеціальним макросом. Він знаходиться в надбудові VBA-Excel. Його потрібно активувати і перейти на однойменну вкладку. Там буде розділ «Посилання», в якому нам потрібно натиснути на кнопку «Розірвати всі посилання».

Як розірвати посилання в Excel

Код VBA

Якщо немає можливості активувати цей додаток, ви можете створити макрос самостійно. Для цього відкрийте редактор Visual Basic, натиснувши клавіші Alt + F11, і напишіть наступні рядки в полі введення коду.

Sub UnlinkWorkBooks()

    Dim WbLinks

    Dim і As Long

    Виберіть Case MsgBox(«Усі посилання на інші книги буде видалено з цього файлу, а формули, що посилаються на інші книги, буде замінено значеннями.» & vbCrLf & «Ви впевнені, що бажаєте продовжити?», 36, «Від’єднати?» )

    Справа 7′ №

        Exit Sub

    End Select

    WbLinks = ActiveWorkbook.LinkSources(Тип:=xlLinkTypeExcelLinks)

    Якщо Not IsEmpty(WbLinks) Тоді

        Для i = 1 To UBound (WbLinks)

            Ім’я ActiveWorkbook.BreakLink:=WbLinks(i), Тип:=xlLinkTypeExcelLinks

        МАЙБУТНІ

    ще

        MsgBox «У цьому файлі немає посилань на інші книги», 64, «Посилання на інші книги»

    End If

End Sub

Як розірвати зв'язки тільки в обраному діапазоні

Час від часу посилань стає дуже багато, і користувач боїться, що, видаливши одну з них, не вдасться повернути все, якщо якесь було зайвим. Але це проблема, якої легко уникнути. Для цього вам потрібно вибрати діапазон, в якому потрібно видалити посилання, а потім видалити їх. Для цього необхідно виконати таку послідовність дій:

  1. Виберіть набір даних, який потрібно змінити.
  2. Встановіть надбудову VBA-Excel, а потім перейдіть на відповідну вкладку.
  3. Далі знаходимо меню «Посилання» і натискаємо на кнопку «Розірвати посилання у вибраних діапазонах».

Як розірвати посилання в Excel

Після цього всі посилання у вибраному наборі клітинок будуть видалені.

Що робити, якщо зв'язки не порвалися

Все вищесказане звучить добре, але на практиці завжди є деякі нюанси. Наприклад, може бути ситуація, коли зв’язки не розриваються. У цьому випадку все одно з’являється діалогове вікно про те, що неможливо автоматично оновити посилання. Що робити в цій ситуації?

  1. По-перше, вам потрібно перевірити, чи міститься будь-яка інформація в названих діапазонах. Для цього натисніть комбінацію клавіш Ctrl + F3 або відкрийте вкладку «Формули» – «Менеджер імен». Якщо ім'я файлу повне, то його потрібно просто відредагувати або взагалі видалити. Перш ніж видаляти іменовані діапазони, вам потрібно скопіювати файл в інше місце, щоб ви могли повернутися до вихідної версії, якщо були зроблені неправильні кроки.
  2. Якщо ви не можете вирішити проблему, видаливши імена, ви можете перевірити умовне форматування. На клітинки іншої таблиці можна посилатися в правилах умовного форматування. Для цього знайдіть відповідний пункт на вкладці «Головна», а потім натисніть на кнопку «Керування файлами». Як розірвати посилання в Excel

    Зазвичай Excel не дає можливості вказати адреси інших робочих книг в умовному форматуванні, але це можливо, якщо ви посилаєтеся на іменований діапазон із посиланням на інший файл. Зазвичай, навіть після видалення посилання, воно залишається. Видалити таке посилання не проблема, оскільки воно фактично не працює. Тому нічого страшного не трапиться, якщо ви його видалите.

Ви також можете скористатися функцією «Перевірка даних», щоб дізнатися, чи немає непотрібних посилань. Посилання зазвичай залишаються, якщо використовується тип перевірки даних «Список». Але що робити, якщо стільників багато? Чи справді необхідно перевіряти кожну з них послідовно? Звичайно, ні. Адже це займе дуже багато часу. Тому потрібно використовувати спеціальний код, щоб істотно заощадити.

Варіант явний

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

' Автор : The_Prist(Щербаков Дмитро)

Професійна розробка додатків для MS Office будь-якої складності

' Проведення тренінгів по MS Excel

https://www.excel-vba.ru

' [електронна пошта захищена]

'WebMoney—R298726502453; Яндекс.Деньги — 41001332272872

' Призначення:

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

Sub FindErrLink()

    'нам потрібно переглянути посилання Data -Change посилання на вихідний файл

    'і введіть ключові слова тут у нижньому регістрі (частина назви файлу)

    'зірочка просто замінює будь-яку кількість символів, тому вам не потрібно турбуватися про точну назву

    Const sToFndLink$ = “*продажі 2018*”

    Dim rr As Range, rc As Range, rres As Range, s$

    'визначте всі комірки з перевіркою даних

    On Error Resume Next

    Установіть rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)

    Якщо rr це ніщо, тоді

        MsgBox «На активному аркуші немає клітинок з перевіркою даних», vbInformation, «www.excel-vba.ru»

        Exit Sub

    End If

    При помилці GoTo 0

    'перевірити кожну клітинку на наявність посилань

    Для кожного rc In rr

        «Про всяк випадок ми пропускаємо помилки – таке теж може статися

        «але наші зв’язки мають бути без них, і вони обов’язково знайдуться

        s = «»

        On Error Resume Next

        s = rc.Validation.Formula1

        При помилці GoTo 0

        Знайдено – все збираємо в окремий асортимент

        Якщо LCase(s) Like sToFndLink Тоді

            Якщо rres - це нічого, тоді

                Встановіть rres = rc

            ще

                Встановити rres = Union(rc, rres)

            End If

        End If

    МАЙБУТНІ

    'якщо є зв'язок, виділити всі комірки з такими перевірками даних

    Якщо не rres, це нічого

        rres.Виберіть

' rres.Interior.Color = vbRed 'якщо ви хочете виділити кольором

    End If

End Sub

Необхідно зробити стандартний модуль в редакторі макросів, а потім вставити туди цей текст. Після цього викликаємо вікно макросу комбінацією клавіш Alt + F8, а потім вибираємо наш макрос і натискаємо на кнопку «Виконати». Використовуючи цей код, слід пам’ятати про кілька речей:

  1. Перш ніж шукати посилання, яке більше не актуальне, ви повинні спочатку визначити, як виглядає посилання, за допомогою якого воно створюється. Для цього зайдіть в меню «Дані» і знайдіть там пункт «Змінити посилання». Після цього потрібно подивитися назву файлу, і вказати його в лапках. Наприклад, так: Const sToFndLink$ = “*продажі 2018*”
  2. Можна написати назву не повністю, а просто замінити непотрібні символи зірочкою. А в лапках назву файлу пишіть маленькими літерами. У цьому випадку Excel знайде всі файли, які містять такий рядок у кінці.
  3. Цей код може перевіряти лише посилання на аркуші, який зараз активний.
  4. За допомогою цього макросу ви можете вибрати лише клітинки, які він знайшов. Ви повинні видалити все вручну. Це плюс, тому що ви можете ще раз все перевірити.
  5. Ви також можете зробити клітинки виділеними спеціальним кольором. Для цього приберіть апостроф перед цим рядком. rres.Interior.Color = vbRed

Зазвичай після того, як ви виконаєте кроки, описані в інструкціях вище, непотрібних підключень більше не повинно бути. Але якщо деякі з них є в документі, і ви не можете їх видалити з тих чи інших причин (типовий приклад - безпека даних на аркуші), то можна скористатися іншою послідовністю дій. Ця інструкція дійсна тільки для версій 2007 і вище.

  1. Створюємо резервну копію документа.
  2. Відкрийте цей документ за допомогою архіватора. Ви можете використовувати будь-який, який підтримує формат ZIP, але WinRar теж підійде, а також вбудований в Windows.
  3. У архіві, який з'явився, потрібно знайти папку xl, а потім відкрити externalLinks.
  4. Ця папка містить усі зовнішні посилання, кожне з яких відповідає файлу у формі externalLink1.xml. Всі вони лише пронумеровані, а тому користувач не має можливості зрозуміти, що це за підключення. Щоб зрозуміти, що за підключення, потрібно відкрити папку _rels, і подивитися там.
  5. Після цього ми видаляємо всі або певні посилання на основі даних, отриманих у файлі externalLinkX.xml.rels.
  6. Після цього відкриваємо наш файл за допомогою Excel. Там буде інформація про помилку на кшталт «Помилка в частині вмісту Книги». Даємо згоду. Після цього з’явиться інше діалогове вікно. Закриваємо.

Після цього всі посилання слід видалити.

залишити коментар