Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Автоматическое обновление ссылок
 
#1
20.11.2019 17:56:44
Здравствуйте!

Такая проблема. Есть 2 фаила (Фаил А и фаил Б). Фаил А что-то вроде Итоговой работы, в который сводятся разные данные, с разных листов Фаила Б. Моя задача была в том, чтобы как-то настроить связь, таким образом, чтобы по-нажатию на кнопку определенную макросом выскакивало бы окно выбора фаила и автоматический менял все данные (т.е. подгружал их из фаила Б в фаил А). И она у меня (благодаря вашему форуму) получилась. Но остался 1 косяк. Косяк, в ситуации, когда в фаиле Б отсутствуют нужные данные. Выскакивает такая плашка в ссылке ячейки #ССЫЛКА, которую нужно менять в ручную.

Объясню еще раз более детально. К сожалению не могу подгрузить пример(, но постараюсь объяснить:
В фаиле А 3 строчки
1.=ЕСЛИОШИБКА('[Фаил Б.xlsm]1'!$L$7;"НЕТ")
2.=ЕСЛИОШИБКА('[Фаил Б.xlsm]2'!$L$7;"НЕТ")
3.=ЕСЛИОШИБКА('[Фаил Б.xlsm]3'!$L$7;"НЕТ")

В Фаиле Б - 2 (ИМЕННО 2!) листа. И что получается, когда я открываю фаил А (при этом фаил Б закрыт) имею на выходе вот такие строки:
1. 12
2. 15
3. НЕТ

Никаких ошибок не появляется, при этом, если я добавлю в фаил Б (закрыв при этом фаил А) 3 лист, то потом он прекрасно подгрузится, дав мне нужные данные:
1. 12
2. 15
3. 16

НО!

Если я открою фаил А и открою фаил Б и при этом в фаиле Б не будет листа 2, выскочит это:
1.12 - здесь появятся нужные данные
2.15 - здесь появятся нужные данные
3.НЕТ - А ЗДЕСЬ ВЫСКАКИВАЕТ "НЕТ" при этом, в формуле появляется это =ЕСЛИОШИБКА('[Фаил Б.xlsm]#ССЫЛКА'!$L$7;"НЕТ") - ВОТ ИМЕННО эту ссылку потом нужно убирать вручную.

Как с этим справится. Заранее извиняюсь, что так детально и угарно все расписал) Надеюсь меня поняли.
Изменено: dodge - 25.11.2019 11:12:08
 
 
 
#2
21.11.2019 12:04:25
Надеюсь на помощь.
 
 
 
#3
21.11.2019 12:58:02
"#ССЫЛКА'!" появляется, потому что листа "3" в файле "Б" нет.
Как с этим справиться? - либо не ссылаться на несуществующий лист, либо добавить лист, чтобы он начал существовать.
 
 
 
#4
21.11.2019 13:38:21
Цитата
Haemoglobin написал:
"#ССЫЛКА'!" появляется, потому что листа "3" в файле "Б" нет.Как с этим справиться? - либо не ссылаться на несуществующий лист, либо добавить лист, чтобы он начал существовать.
Варианта, чтобы листа не было и ссылка была, а #ССЫЛКА не появлялась нету?

И сразу вопрос еще:
когда в ячейке идет ссылка на внешнюю книгу, она типа вот такого вида С/Папка/Новая папка/[Фаил]НАЗВАНИЕ ЛИСТА'!A1

Вот можно ли как-то выделенное красным менять не только вручную? Как-то подвязать на формулу например.
Изменено: dodge - 21.11.2019 13:53:57
 
 
 
#5
21.11.2019 13:41:45
dodge, цитата здесь абсолютно неуместна. Да и не цитата это, а полная копия сообщения. Зачем?
 
 
 
#6
22.11.2019 06:05:56
Вариант, чтобы листа не было и ссылка была - отключить автоматический пересчет формул.
Вот можно ли как-то выделенное красным менять не только вручную - можно.
 
 
 
#7
22.11.2019 15:38:14
По автоматическому расчету формул - понял, спасибо. И я так понял это отключение можно будет через VBA сделать? Для нужного диапазона?
А как выделенное красным не только вручную менять?
Изменено: dodge - 22.11.2019 15:43:58
 
 
 
#8
22.11.2019 18:14:53
Цитата
Haemoglobin написал:
Вариант, чтобы листа не было и ссылка была - отключить автоматический пересчет формул.
Почему-то этот вариант все же не работает. Даже если отключить перерасчет, при открытие одновременно 2 книг, вылазиет #ССЫЛКА(
 
 
 
#9
25.11.2019 11:12:26
Еще нужна помощь(  
 
 
 
#10
25.11.2019 12:34:26
dodge Все же сделайте пример. Предполагаю, что поможет ДВССЫЛ.
Мой канал
 
 
 
#11
25.11.2019 13:31:09
ДВССЫЛ вроде как не поможет, я делал через нее, получается ситуация наоборот, информация будет подгружаться, только когда открыты оба фаила, а стоит закрыть фаил 2 будет опять выскакивать ССЫЛКА (НО правда да, формула не собьется). Я бы подгрузил фаил, но просто не знаю как это лучше показать. Попробую еще раз объяснить:

Есть 2 фаила.
В 1 фаиле есть 5 строчек, в каждую строчку подгружаются данные (ну через связь) с фаила 2, с листов 1, 2, 3, 4, 5. Все оке, все работает.
Стоит мне удалить лист 5 в фаиле 2 и ОДНОВРЕМЕННО открыть и фаил 1 и фаил 2 ,то в фаиле 1 в строчке под номером 5 появляется #ССЫЛКА - и формула превращается в такую - С:\Блаблабла\[Фаил2]#ССЫЛКА'A1 (а была соответственно такая С:\Блаблабла\[Фаил2]5'A1).

ПРИ ЭТОМ!

Если одновременно оба фаила не открывать, то все будет нормально. В фаиле 1 на строчке 5 будет написано ошибка, эксель будет ругаться, но ФОРМУЛА не собьется, останется прежней (С:\Блаблабла\[Фаил2]5'A1) и затем добавив снова в фаил 2 5 лист - информация подгрузится.

Мне нужно, чтобы #ССЫЛКА - не появлялась, и формула не сбивалась.

Не прошу конкретного решения, нужен скорее именно совет, мб кто-то подскажет какую функцию применить или посоветует как это через VBA сделать. Вот например товарищ выше мне посоветовал отключить пересчет формул и я думал как-то это на марос подвязать, что когда открыт фаил со связью, в нужном диапазоне формулы не пересчитываются, но попробовал сейчас, и отключение перерасчета формул не помогло. При одновременно открытии - все равно формула сбивается, а ССЫЛКА появляется.
Изменено: dodge - 25.11.2019 13:33:20
 
 
 
#12
25.11.2019 13:32:26
Цитата
skais675 написал:
поможет ДВССЫЛ
а вот и нет, она не умеет работать с закрытыми файлами.
Возможно, поможет что-то из этого(через VBA): Как получить данные из закрытой книги?
Вариант через ExecuteExcel4Macro должен работать довольно быстро и при этом можно отсечь те ссылки, которые ссылаются на несуществующие листы или возвращающие ошибки. Например, можно обработать так:
Код
vData = ExecuteExcel4Macro(sAddress)
If Not Iserror(vData) then
    range("A1").value = vData
end if
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#13
25.11.2019 14:53:50
Спасибо за помощь, но это все вроде бы тоже не пойдет (хотя я еще полазию). Я подумал, что отлично подойдет вот эта функция  Function Get_Value_From_Close_Book из вашей ссылки, но все же мне как-то надо это подвязать именно на связь с фаилами, а не получение данных из закрытой книги. Объясню почему (возможно сам виноват изначально забыл это указать). Указанными способами, стоит только убрать фаил из нужного расположения - данные не подгружаются. Везде сразу ошибки ЗНАЧ. А мне бы хотелось, чтобы я выбрал фаил какойто-то, из него все подгрузилось, я этот фаил удалил или переместил, и старые данные остались бы. Пока я сам не нажму обновить их, или не выберу другой фаил для подгрузки. Да и чтобы потом нажав - разорвать связь, можно было бы получить фаил без формул, а просто со значениями. Но все равно спасибо.
Изменено: dodge - 25.11.2019 14:54:53
 
 
 
#14
26.11.2019 00:35:42
Последняя попытка. Залил 2 фаила. Из фаила 2 в фаил 1 подгружаются данные. Строчка 5 в фаиле 1 - главная проблема.
 
 
 
#15
02.12.2019 12:54:26
Dodge, написал в личку.
 
 
 
Страницы: 1
Наверх