Excel эта книга содержит связи с другими источниками данных как отключить
Связь – это очень полезная возможность Excel. Ведь очень часто пользователям приходится использовать информацию из других файлов. Но в некоторых ситуациях они способны скорее нанести вред, чем принести пользу. Ведь, к примеру, если отправлять эти файлы по почте, ссылки оказываются нерабочими. Сегодня мы более подробно поговорим о том, что сделать для избежания такой проблемы.
Что такое связи в Excel
Связи в Excel очень часто используются вместе с такими функциями, как ВПР, чтобы получить информацию из другой книги. Она может иметь вид специальной ссылки, которая содержит адрес не только ячейки, но и книги, в которой данные расположены. В результате, такая ссылка имеет приблизительно такой вид: =ВПР(A2;'[Продажи 2018.xlsx]Отчет’!$A:$F;4;0). Или же, для более простого представления, представить адрес в следующем виде: ='[Продажи 2018.xlsx]Отчет’!$A1. Разберем каждый из элементов ссылки этого типа:
- [Продажи 2018.xlsx]. Этот фрагмент содержит ссылку на файл, из которого нужно достать информацию. Его также называют источником.
- Отчет. Это мы использовали следующее имя, но это не название, которое должно обязательно быть. В этом блоке содержится название листа, в каком надо находить информацию.
- $A:$F и $A1 – адрес ячейки или диапазона с данными, которые содержатся в этом документе.
Собственно, процесс создания ссылки на внешний документ и называется связыванием. После того, как мы прописали адрес ячейки, содержащейся в другом файле, изменяется содержимое вкладки «Данные». А именно – становится активной кнопка «Изменить связи», с помощью которой пользователь может отредактировать имеющиеся связи.
Суть проблемы
Тут перед пользователем появляется два возможных варианта, как действовать этой ситуации. Он может нажать «Продолжить» и тогда изменения не обновятся, или же нажать кнопку «Изменить связи», с помощью которой он может обновить их вручную. После того, как мы нажмем эту кнопку, появится дополнительное окно, в котором можно будет изменить связи, указав, где находится правильный файл в данный момент и как он называется.
Как разорвать связь в Эксель
Один из самых простых методов решения описанной выше ситуации в случае, если не получается обновить местонахождение связанного файла самостоятельно – удалить саму связь. Особенно просто это сделать, если в документе содержится только одна связь. Для этого надо выполнить следующую последовательность шагов:
- Открываем меню «Данные».
- Находим раздел «Подключения», и там – опцию «Изменить связи».
- После этого нажимаем на «Разорвать связь».
Если собираетесь отправлять эту книгу по почте другому человеку, настоятельно рекомендуется это сделать до этого. Ведь после удаления связей все значения, которые содержатся в другом документе, будут автоматически загружены в файл, использованы в формулах, и вместо адреса ячейки информация в соответствующих ячейках будет просто трансформирована в значения.
Как разорвать связь со всеми книгами
Но если количество связей становится слишком большим, вручную их удалять может занять немало времени. Чтобы решить эту проблему за один раз, можно воспользоваться специальным макросом. Он находится в аддоне VBA-Excel. Нужно его активировать и перейти на одноименную вкладку. Там будет находиться раздел «Связи», в котором нам надо нажать на кнопку «Разорвать все связи».
Код на VBA
Если же нет возможности активировать это дополнение, можно создать макрос самостоятельно. Для этого необходимо открыть редактор Visual Basic, нажав на клавиши Alt + F11, и в поле ввода кода записать следующие строки.
Select Case MsgBox(«Все ссылки на другие книги будут удалены из этого файла, а формулы, ссылающиеся на другие книги будут заменены на значения.» & vbCrLf & «Вы уверены, что хотите продолжить?», 36, «Разорвать связь?»)
If Not IsEmpty(WbLinks) Then
For i = 1 To UBound(WbLinks)
ActiveWorkbook.BreakLink Name:=WbLinks(i), Type:=xlLinkTypeExcelLinks
MsgBox «В данном файле отсутствуют ссылки на другие книги.», 64, «Связи с другими книгами»
Как разорвать связи только в выделенном диапазоне
Время от времени количество связей очень большое, и пользователь боится, что после удаления какой-то из них не получится вернуть все назад, если какая-то была лишней. Но это проблема, которую легко не допустить. Для этого нужно выбрать диапазон, в котором осуществить удаление связей, а потом удалить их. Для этого надо выполнить следующую последовательность действий:
- Выделить тот набор данных, в котором надо вносить изменения.
- Устанавливаем дополнение VBA-Excel, после чего переходим на соответствующую вкладку.
- Далее находим меню «Связи» и нажимаем на кнопку «Разорвать связи в выделенных диапазонах».
После этого все связи в выделенном наборе ячеек будут удалены.
Что делать, если связи не разрываются
Все описанное выше звучит хорошо, но на практике всегда возникают какие-то нюансы. Например, может случиться ситуация, когда связи не разрываются. В этом случае все равно появляется диалоговое окно, что не получается автоматически обновить связи. Что же делать в этой ситуации?
- Сначала надо проверить, не содержится ли какая-то информация в именованных диапазонах. Для этого надо нажать на комбинацию клавиш Ctrl + F3 или же открыть вкладку «Формулы» – «Диспетчер имен». Если же имя к файлу указано полное, то нужно просто его отредактировать или же вовсе убрать. Перед тем, как удалять именованные диапазоны, необходимо скопировать файл в какое-то другое место, чтобы можно было вернуться к изначальному варианту, если были совершены неправильные действия.
- Если не получается решить проблему с помощью удаления имен, то можно проверить условное форматирование. Ссылка на ячейки в другой таблице может содержаться в правилах условного форматирования. Для этого надо найти соответствующий пункт на вкладке «Главная», а потом нажать на кнопку «Управление файлами».
Обычно Excel не дает возможности давать адрес других книг в условном форматировании, но это делается, если ссылаться на именованный диапазон с отсылкой на другой файл. Обычно даже после удаления связи ссылка остается. Нет никакой проблемы в том, чтобы убрать такую связь, потому что связь по факту нерабочая. Следовательно, ничего плохого не произойдет, если убрать ее.
Также можно воспользоваться функцией «Проверка данных», чтобы узнать, нет ли ненужных ссылок. Обычно связи остаются, если используется тип проверки данных «Список». Но что же делать, если ячеек много? Неужели необходимо последовательно проверять каждую из них? Конечно, нет. Ведь это займет очень много времени. Поэтому нужно воспользоваться специальным кодом, чтобы значительно сэкономить его.
Option Explicit
‘ Author : The_Prist(Щербаков Дмитрий)
‘ Профессиональная разработка приложений для MS Office любой сложности
‘ Проведение тренингов по MS Excel
‘ WebMoney — R298726502453; Яндекс.Деньги — 41001332272872
‘надо посмотреть в Данные -Изменить связи ссылку на файл-иточник
‘и записать сюда ключевые слова в нижнем регистре(часть имени файла)
‘звездочка просто заменяет любое кол-во символов, чтобы не париться с точным названием
Const sToFndLink$ = «*продажи 2018*»
Dim rr As Range, rc As Range, rres As Range, s$
‘определяем все ячейки с проверкой данных
On Error Resume Next
If rr Is Nothing Then
On Error GoTo 0
‘проверяем каждую ячейку на предмет наличия связей
For Each rc In rr
‘на всякий случай пропускаем ошибки — такое тоже может быть
‘но наши связи должны быть без них и они точно отыщутся
On Error Resume Next
On Error GoTo 0
‘нашли — собираем все в отдельный диапазон
If LCase(s) Like sToFndLink Then
If rres Is Nothing Then
Set rres = Union(rc, rres)
‘если связь есть — выделяем все ячейки с такими проверками данных
If Not rres Is Nothing Then
‘ rres.Interior.Color = vbRed ‘если надо выделить еще и цветом
Необходимо в редакторе макросов сделать стандартный модуль, а потом туда вставить этот текст. После этого вызвать окно макросов с помощью комбинации клавиш Alt + F8, а потом выбрать наш макрос и кликнуть по кнопке «Выполнить». При использовании этого кода есть несколько моментов, которые надо учитывать:
- Перед тем, как осуществлять поиск связи, которая уже не актуальна, нужно перед этим определить, как выглядит ссылка, через которую она создается. Для этого надо перейти в меню «Данные» и там найти пункт «Изменить связи». После этого надо посмотреть имя файла, и указать его в кавычках. Например, так: Const sToFndLink$ = «*продажи 2018*»
- Возможна запись имени не в полном виде, а просто заменить ненужные знаки звездочкой. А в кавычках записывать имя файла обязательно маленькими буквами. В этом случае Эксель найдет все файлы, которые содержат такую строку в конце.
- Этот код способен проверять наличие ссылок только в том листе, который сейчас активный.
- С помощью этого макроса можно лишь выделить ячейки, которые он обнаружил. Удалять придется все вручную. Это и плюс, потому что можно еще раз все перепроверить.
- Также можно сделать так, чтобы ячейки подсвечивались специальным цветом. Для этого нужно убрать знак апострофа перед этой строчкой. rres.Interior.Color = vbRed
Обычно после того, как выполнить действия, описанные в вышеприведенной инструкции, ненужных связей уже не должно оставаться. Но в случае наличия некоторых из них в документе и вашей неспособности по тем или иным причинам их убрать (типичный пример – защищенность данных в листе), то можно воспользоваться другой последовательностью действий. Эта инструкция справедлива лишь для версий 2007 и выше.
Добрый день!
Имеется 2 файла. В одном у нас формула. Во втором ссылка на формулу из 1-го файла через макрофункцию "Вычислить". При запуске второго файла поставил макрос:
[vba]
[/vba]
Теперь при запуске этого файла выскакивает оповещение:
"Эта книга содержит связи с внешними источниками данных (возможно, небезопасными). Если вы считаете эти связи надёжными, обновите их, чтобы получить последние данные. В противном случае продолжайте работу с имеющимися данными".
Как его отключить? программно возможно? [vba]
Добрый день!
Имеется 2 файла. В одном у нас формула. Во втором ссылка на формулу из 1-го файла через макрофункцию "Вычислить". При запуске второго файла поставил макрос:
[vba]
[/vba]
Теперь при запуске этого файла выскакивает оповещение:
"Эта книга содержит связи с внешними источниками данных (возможно, небезопасными). Если вы считаете эти связи надёжными, обновите их, чтобы получить последние данные. В противном случае продолжайте работу с имеющимися данными".
Как его отключить? программно возможно? [vba]
Много чего не знаю.
[/vba]
Теперь при запуске этого файла выскакивает оповещение:
"Эта книга содержит связи с внешними источниками данных (возможно, небезопасными). Если вы считаете эти связи надёжными, обновите их, чтобы получить последние данные. В противном случае продолжайте работу с имеющимися данными".
Как его отключить? программно возможно? [vba]
[/vba] почему-то не помогает.
Забыл прицепить файлики Автор - Roman777
Дата добавления - 17.11.2015 в 10:49
Попробуйте отключить в настройках:
3
Для конкретной книги можно отключить так:
[vba]
Попробуйте отключить в настройках:
3
Для конкретной книги можно отключить так:
[vba]
Иногда все проще чем кажется с первого взгляда.
Для конкретной книги можно отключить так:
[vba]
SLAVICK, как ни странно, но при запуске файла всё так же выскакивает надпись, ставил на отключить и включить автоматическое - всёравно вылетает.
SLAVICK, как ни странно, но при запуске файла всё так же выскакивает надпись, ставил на отключить и включить автоматическое - всёравно вылетает. Roman777
А Вы как делали?
Нужно так:
Открываем файл
Нажимаем OFF
Сохраняем файл
Закрываем файл
Открываем файл
Смотрим
Вторую кнопку жать не нужно - она для восстановления параметра.
А Вы как делали?
Нужно так:
Открываем файл
Нажимаем OFF
Сохраняем файл
Закрываем файл
Открываем файл
Смотрим
Вторую кнопку жать не нужно - она для восстановления параметра. SLAVICK
Иногда все проще чем кажется с первого взгляда.
А Вы как делали?
Нужно так:
Открываем файл
Нажимаем OFF
Сохраняем файл
Закрываем файл
Открываем файл
Смотрим
Вторую кнопку жать не нужно - она для восстановления параметра. Автор - SLAVICK
Дата добавления - 17.11.2015 в 12:09
именно так и делал. Всё-равно вылазило. А вот макрос Ваш убрал её))) что странно. Ещё странно, что когда я установил уже в настройках вручную вторую позицию. То действие макроса всёравно сохранилось. Единственное, всё же, я хотел чтобы окно не вылазило, но обновление ссылки происходило. А тут, я так понял, отключается это обновление. Только после нажатия на ячейку с ссылкой, она обновляется.
Большое спасибо за помощь)
именно так и делал. Всё-равно вылазило. А вот макрос Ваш убрал её))) что странно. Ещё странно, что когда я установил уже в настройках вручную вторую позицию. То действие макроса всёравно сохранилось. Единственное, всё же, я хотел чтобы окно не вылазило, но обновление ссылки происходило. А тут, я так понял, отключается это обновление. Только после нажатия на ячейку с ссылкой, она обновляется.
Большое спасибо за помощь) Roman777
Много чего не знаю.
именно так и делал. Всё-равно вылазило. А вот макрос Ваш убрал её))) что странно. Ещё странно, что когда я установил уже в настройках вручную вторую позицию. То действие макроса всёравно сохранилось. Единственное, всё же, я хотел чтобы окно не вылазило, но обновление ссылки происходило. А тут, я так понял, отключается это обновление. Только после нажатия на ячейку с ссылкой, она обновляется.
Большое спасибо за помощь) Автор - Roman777
Дата добавления - 17.11.2015 в 12:25
SLAVICK, Вы меня натолкнули на поиск и я нашёл другое решение, оно даже мб больше для меня конкретно подходит:
[vba]
SLAVICK, Вы меня натолкнули на поиск и я нашёл другое решение, оно даже мб больше для меня конкретно подходит:
[vba]
При выполнении сложных расчетов приходится использовать данные из других книг, тем самым создавая связь. При отправке таких файлов по почте, у получателя "слетают" ссылки. Как этого избежать описано ниже.
Описание проблемы
Когда в формуле вы указываете ссылку на другую книгу, то Excel образует с ней связь. Эта связь будет прекрасно работать и обновляться автоматически в том случае, когда открыты обе книги.
К сожалению, если книга-источник была удалена/перемещена или переименована, то связь нарушится. Также связь будет потеряна если вы переместите конечный файл (содержащий ссылку). Если вы передадите только конечный файл по почте, то получатель тоже не сможет обновить связи.
Как разорвать связь
Один из способов решения данной проблемы - разрыв связи. Если в файле только одна связь, то сделать это довольно просто:
- Перейдите на вкладку Данные.
- Выберите команду Изменить связи в разделе Подключения.
- Нажмите Разорвать связь.
ВАЖНО! При разрыве связи все формулы ссылающиеся на книгу-источник будут преобразованы в значения! Отмена данной операции невозможна!
Как разорвать связь со всеми книгами
Для удобства, можно воспользоваться макросом, который разорвет связи со всеми книгами. Макрос входит в состав надстройки VBA-Excel. Чтобы им воспользоваться необходимо:
Код на VBA
Код макроса удаляющего все связи с книгой представлен ниже. Можете скопировать его в свой проект.
Как разорваться связи только в выделенном диапазоне
Иногда в книге имеется много связей и есть опасения, что при удалении связи можно удалить лишнюю. Чтобы этого избежать с помощью надстройки можно удалить связи только в выделенном диапазоне. Для этого:
Надстройка для Excel содержит большой набор полезных функций, с помощью которых вы значительно сократите время и увеличите скорость работы с программой.
После подключения книги Excel к внешнему источнику данных, такому как база данных SQL Server, база данных Access или другая книга Excel, вы можете поддерживать данные в своей книге в актуальном состоянии, обновляя ссылку на ее источник. Каждый раз, когда вы обновляете соединение, вы видите самые последние данные, включая все, что было новым или было удалено.
Появится окно « Обновление данных» , в котором отображаются все таблицы данных в модели данных и отслеживается ход обновления. После завершения обновления отображается состояние.
Шаг 4 — Нажмите на Закрыть. Данные в вашей модели данных обновляются.
Обновите соединения данных
Шаг 1 — Нажмите любую ячейку в таблице, которая содержит ссылку на импортированный файл данных.
Шаг 3 — Нажмите Обновить все в группе Соединения.
Шаг 4 — В раскрывающемся списке нажмите Обновить все . Все подключения к данным в Рабочей книге будут обновлены.
Автоматически обновлять данные
Здесь мы узнаем, как автоматически обновлять данные при открытии рабочей книги.
Шаг 1 — Щелкните любую ячейку в таблице, которая содержит ссылку на импортированный файл данных.
Шаг 3 — Нажмите на Соединения в группе Соединения . Откроется окно Подключения к рабочей книге.
Шаг 4 — Нажмите на Свойства . Откроется окно свойств подключения .
Шаг 5 — Вы найдете вкладку « Использование » и вкладку « Определение ». Нажмите на вкладку Использование . Появятся параметры для управления обновлением .
Шаг 6 — Выберите Обновить данные при открытии файла.
У вас также есть опция под этим: «Удалить данные из диапазона внешних данных перед сохранением рабочей книги» . Вы можете использовать эту опцию, чтобы сохранить книгу с определением запроса, но без внешних данных.
Каждый раз, когда вы открываете свою рабочую книгу, в вашу рабочую книгу будут загружаться новейшие данные.
Автоматически обновлять данные через равные промежутки времени
Шаг 1 — Щелкните любую ячейку в таблице, которая содержит ссылку на импортированный файл данных.
Шаг 3 — Выберите опцию « Подключения» в группе « Подключения ». Откроется окно Подключения к книге.
Шаг 4 — Нажмите на Свойства . Откроется окно свойств подключения .
Шаг 5 — Нажмите на вкладку Использование . Появятся параметры для управления обновлением .
Шаг 6 — Теперь выберите «Обновить каждые» и введите 60 минут между каждой операцией обновления.
Шаг 7 — Нажмите ОК . Ваши данные будут обновляться каждые 60 минут, то есть каждый час.
Включить фоновое обновление
Шаг 1 — Щелкните любую ячейку в таблице, которая содержит ссылку на импортированный файл данных.
Шаг 3 — Нажмите на Соединения в группе Соединения . Откроется окно Подключения к рабочей книге.
Шаг 4 — Нажмите на Свойства . Откроется окно свойств подключения .
Шаг 5 — Нажмите на вкладку Использование . Появятся параметры управления обновлением .
Шаг 6 — Нажмите Включить фоновое обновление, а затем нажмите ОК .
В большинстве случаев при выборе способа вставки таблицы следует выбирать текстовый вариант, чтобы иметь возможность настроить форматирование под себя. Картинки можно вставлять, если форматирование в исходном документе Excel Вас полностью устраивает.
Рутинное копирование одних и тех же табличек или графиков с немного изменившимися данными в одни и те же документы крайне утомляет. Если же вы хотите что-то уточнить, обращайтесь ко мне!
параметры. на форматирование и файлаMacЗащита книги последняя версия. Кроме совместно редактировать файлы, ошибок при совместном этой версии требует пока другой пользователь (на английском языке). залить обратно и 7500 форматов. и т.п. не (если он открылся)
Файл excel заблокирован другим пользователем
В диспетчере показаны все связи файла. В группе «Способ обновления связи» можно выбрать тот вариант, который более предпочтителен или вообще отключить обновление связей. Также для каждой связи можно задать настройку «Сохранять формат при обновлении». Она отвечает за то, чтобы выбранное Вами форматирование не слетало, когда вы обновите связь.
9 Тоже не сработал такой способ, но вот обработочка с минимальными доработками из-за Значение не является значением объектного типа УдалитьДочерний запела на ура. Если же вы хотите что-то уточнить, обращайтесь ко мне!
(12) Отлично сработало при ошибке с Print_Area
Было
Эксель = Новый COMОбъект(«Excel.Application»);
Книга = Эксель.WorkBooks.Open(Файл.ПутьКФайлу)
.
Эксель.Application.Quit();
Стало даже короче
Книга = ПолучитьCOMОбъект(Файл.ПутьКФайлу);
.
Книга.Application.Quit();
Что делать
- Кликните на кнопку «Дополнительно» с левой стороны окна.
- Жмите на «Параметры Excel».
- Перейдите вниз до раздела общих параметров.
- Посмотрите, чтобы отметка «Игнорировать другие приложения» была установлена.
- Кликните на кнопку «ОК».
даже перезагружен комп. У всех пользователей : определение диапазонов, которыенажмите кнопку,Чтобы пользователи, просматривающие документОбновить > Выберите XLSB. попробуйте использовать Excel порекомендуйте всем использоватьExcel Online какое-то время, чтобы
Что такое связи в Excel и как их создать
Иногда при работе с различными отчетами приходится создавать связи с другими книгами(отчетами). Чаще всего это используется в функциях вроде ВПР (VLOOKUP) для получения данных по критерию из таблицы, расположенной в другой книге. Так же это может быть и простая ссылка на ячейки другой книги. В итоге ссылки в таких ячейках выглядят следующим образом:
=ВПР( A2 ;'[Продажи 2018.xlsx]Отчет'!$A:$F;4;0)
или
='[Продажи 2018.xlsx]Отчет'!$A1
- [Продажи 2018.xlsx] - обозначает книгу, в которой итоговое значение. Такие книги так же называют источниками
- Отчет - имя листа в этой книге
- $A:$F и $A1 - непосредственно ячейка или диапазон со значениями
Как разорвать связи
Как правило связи редко нужны на продолжительное время, т.к. они неизбежно увеличивают размер файла, особенно, если связей много. Исключениями являются случаи, когда связи делаются на некие общие файлы, расположенные на сетевом диске и заполняемые разными подразделениями, а в итоговый файл связями просто забираются конкретные значения из этого файла. Чтобы разорвать связи необходимо перейти на вкладку Данные -группа Данные и подключения -Изменить связи:
Выделить нужные связи и нажать Разорвать связь. При этом все ячейки с формулами, содержащими связи, будут преобразованы в значения вычисленные этой формулой при последнем обновлении. Данное действие нельзя будет отменить - только закрытием книги без сохранения.
Так же связи внутри формул разрываются, если формулы просто заменить значениями -Копируем нужные ячейки -Правая кнопка мыши -Специальная вставка -Значения. Формулы в ячейках будут заменены результатами их вычислений, а все связи будут удалены.
Более подробно про замену формул значениями можно узнать из статьи: Как удалить в ячейке формулу, оставив значения?
Что делать, если связи не разрываются
Но иногда возникают ситуации, когда вроде все формулы во всех ячейках уже заменены на значения, но запрос на обновление каких-то связей все равно появляется. В этом случае есть парочка рекомендаций для поиска и удаления этих мифических связей:
Option Explicit '--------------------------------------------------------------------------------------- ' Author : The_Prist(Щербаков Дмитрий) ' Профессиональная разработка приложений для MS Office любой сложности ' Проведение тренингов по MS Excel ' https://www.excel-vba.ru ' info@excel-vba.ru ' WebMoney - R298726502453; Яндекс.Деньги - 41001332272872 ' Purpose: '--------------------------------------------------------------------------------------- Sub FindErrLink() 'надо посмотреть в Данные -Изменить связи ссылку на файл-иточник 'и записать сюда ключевые слова в нижнем регистре(часть имени файла) 'звездочка просто заменяет любое кол-во символов, чтобы не париться с точным названием Const sToFndLink$ = "*продажи 2018*" Dim rr As Range, rc As Range, rres As Range, s$ 'определяем все ячейки с проверкой данных On Error Resume Next Set rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation) If rr Is Nothing Then MsgBox "На активном листе нет ячеек с проверкой данных", vbInformation, "www.excel-vba.ru" Exit Sub End If On Error GoTo 0 'проверяем каждую ячейку на предмет наличия связей For Each rc In rr 'на всякий случай пропускаем ошибки - такое тоже может быть 'но наши связи должны быть без них и они точно отыщутся s = "" On Error Resume Next s = rc.Validation.Formula1 On Error GoTo 0 'нашли - собираем все в отдельный диапазон If LCase(s) Like sToFndLink Then If rres Is Nothing Then Set rres = rc Else Set rres = Union(rc, rres) End If End If Next 'если связь есть - выделяем все ячейки с такими проверками данных If Not rres Is Nothing Then rres.Select ' rres.Interior.Color = vbRed 'если надо выделить еще и цветом End If End Sub
Читайте также: