Как разорвать ссылки в Excel

Общение — очень полезная функция Excel. Ведь очень часто пользователям приходится использовать информацию из других файлов. Но в некоторых ситуациях они могут принести больше вреда, чем пользы. Ведь если, например, отправить эти файлы по почте, ссылки не работают. Сегодня мы более подробно поговорим о том, что делать, чтобы избежать такой проблемы.

Что такое отношения в Excel

Отношения в Excel очень часто используются в сочетании с такими функциями, как ВПРчтобы получить информацию из другой книги. Он может иметь вид специальной ссылки, содержащей адрес не только ячейки, но и книги, в которой находятся данные. В результате такая ссылка выглядит примерно так: =ВПР(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, и в поле ввода кода напишите следующие строки.

Подраздел ОтсоединитьWorkBooks()

    Дим WbLinks

    Тусклый и такой же длинный

    Выберите Case MsgBox("Все ссылки на другие книги будут удалены из этого файла, а формулы, относящиеся к другим книгам, будут заменены значениями." & vbCrLf & "Вы уверены, что хотите продолжить?", 36, "Отключить?" )

    Случай 7’ Нет

        Exit Sub

    End Select

    WbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

    Если не 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

' Цель:

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

Подпрограмма 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)

    Если рр — это ничего, тогда

        MsgBox «На активном листе нет ячеек с проверкой данных», vbInformation, «www.excel-vba.ru»

        Exit Sub

    End If

    По ошибке GoTo 0

    'проверяем каждую ячейку на наличие ссылок

    Для каждого жк в р-р

        'на всякий случай пропускаем ошибки – такое тоже может случиться

        'но наши связи должны быть без них и они обязательно найдутся

        с = «»

        On Error Resume Next

        s = rc.Validation.Formula1

        По ошибке GoTo 0

        'нашел – собираем все в отдельный диапазон

        Если LCase(ы) похожи на 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. Там будет информация об ошибке типа «Ошибка в части содержания в Книге». Даем согласие. После этого появится еще один диалог. Мы закрываем его.

После этого все ссылки следует удалить.

Оставьте комментарий