Форум программистов
 

Восстановите пароль или Зарегистрируйтесь на форуме, о проблемах и с заказом рекламы пишите сюда - alarforum@yandex.ru, проверяйте папку спам!

Вернуться   Форум программистов > Microsoft Office и VBA программирование > Microsoft Office Excel
Регистрация

Восстановить пароль
Повторная активизация e-mail

Купить рекламу на форуме - 42 тыс руб за месяц

Ответ
 
Опции темы Поиск в этой теме
Старый 16.09.2016, 22:41   #1
iNataliya
Пользователь
 
Регистрация: 29.08.2013
Сообщений: 27
По умолчанию Проблема с изменением внешних связей средствами VBA

Добрый день!

Есть задача - изменить внешние связи в файлах, а точнее изменить пути к папкам где лежат связанные файлы, т.к. файлы изменили свое местоположение. Связей в файлах много до 40. Задача решается в VBA путем последовательного перебора всех связей в цикле, заменой текста старого пути на новый, открытием связанной книги по новому пути (это значительно ускоряет процесс) и изменением связи. Для ускорения также отключается обновление экрана, пересчет и все предупреждения. Все ок, все работает. Но возникла проблема: если в файле есть формула, содержащая одновременно ссылки на два файла (допустим суммирование данных из двух файлов), то при попытке макроса заменить первую связь эксель находит, что второго файла по старой ссылке нет и выдает окно "Обновить значение <имя файла>", НО т.к. обновление экрана отключено, это окно не видно и макрос повисает с возможностью выхода только через перегрузку эксель.
Подскажите как можно обойти эту проблему. Пока есть только решение не отключать обновление экрана, но это полдела, т.к. после выбора нового файла связь на него обновляется очень долго, т.к. файл не открыт.
Может быть можно как-то отключить проверку корректности связей в формулах? Т.к. пересчет отключен, то ошибка в данных не повлияет на скорость, а после того как макрос дойдет до второго файла и поменяет путь, ошибка исчезнет. Или какой нибудь еще способ решения проблемы.
iNataliya вне форума Ответить с цитированием
Старый 16.09.2016, 23:56   #2
IgorGO
Новичок
СтарожилДжуниор
 
Аватар для IgorGO
 
Регистрация: 05.02.2008
Сообщений: 9,487
По умолчанию

- взять формулу содержащуюся в ячейке в строку,
- заменить ВСЕ связи на корректные,
- вернуть строку на место
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете
IgorGO вне форума Ответить с цитированием
Старый 17.09.2016, 10:01   #3
iNataliya
Пользователь
 
Регистрация: 29.08.2013
Сообщений: 27
По умолчанию

Спасибо, за совет, была такая мысль, но формул таких может быть несколько сот или тысяч на разных листах и поиск и замена может затянутся на неопределенной время. По крайней мере это даже дольше чем через "Обновить значение <имя файла>". Хотелось что-то более быстрое.
iNataliya вне форума Ответить с цитированием
Старый 02.07.2021, 12:17   #4
YuraMits
Новичок
Джуниор
 
Регистрация: 02.07.2021
Сообщений: 1
Лампочка Решение проблемы

Может кому-то еще актуально, как мне. Есть такая же задача - поменять связи к файлам, использующиеся в одной формуле. Формул, примерно до ... Стандартная функция Excel Изменить связи отрабатывает, даже с открытыми всеми файлами, примерно 30-45 минут.
Вот как получилось поменять связи буквально за 5 минут с пересчетом формул и без открытия файлов:
1. Обязательно делаем резервную копию файла
2. Открываем файл при помощи любого архиватора(WinRAR отлично справляется, но это может быть и другой, работающий с форматом ZIP)
3. В архиве перейти в папку xl -> externalLinks ->_rels
4. Сколько связей содержится в файле, столько файлов вида externalLink1.bin.rels и будет внутри. Там будет содержаться путь и имя файла-источника.
5. Меняем путь и имя файла (по необходимости), аналогично содержимому файла externalLink1.xml.
6. Закрываем архив, с сохранением/обновлением файлов.
7. Открываем файл в Excel. Открываем Изменить связи и видим, что связи у нас уже относятся к новым файлам (если все сделали правильно в предыдущих пунктах). Достаточно выбрать каждую связь и нажать кнопку Обновить. Excel сам пересчитает все формулы с учетом новых данных, что займет всего несколько минут (в зависимости от количества формул для пересчета).
YuraMits вне форума Ответить с цитированием
Ответ


Купить рекламу на форуме - 42 тыс руб за месяц

Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск


Похожие темы
Тема Автор Раздел Ответов Последнее сообщение
Состояние внешних связей книги iNataliya Microsoft Office Excel 0 26.01.2015 14:55
Массовая замена внешних связей mrMad-Cat Microsoft Office Excel 5 24.04.2013 12:16
Проблема с изменением hosts _-Re@l-_ Безопасность, Шифрование 6 19.11.2010 21:20
Вопрос по запросу на иморт внешних связей! Klim Bassenger Microsoft Office Excel 0 25.05.2009 13:41
Обновление внешних связей Григорьев Игорь Microsoft Office Excel 12 10.11.2008 11:33