Страницы: 1
RSS
Прошу помощи по макросу "Обновление связей", Несколько книг со связями
 
Добрый день всем! Есть вроде бы простая задача, которую мне никак не удается решить.

Есть несколько книг: первая книга - самая простая, простой "справочник", наименование товара и его цена на сегодняшний день (под сотню-другую строк). Эта книга вручную корректируется довольно часто (цены же растут). Назовем ее Книга №1.

Второй тип книги - "основной расчет", в книге несколько Листов. На этих Листах формулы ссылаются на Первую книгу и содержат формулы "ВПР", ну то есть поиск цены для конкретного товара.

Книг второго типа несколько, в каждой - десятки Листов, на каждом Листе - сотни формул. Назовем эти книги "Книги №2.1, 2.2, 2.3 и т.д.)

И есть третий тип Книги. Я назвал ее "сводная". Она собирает во всех Книг второго типа данные. Формулы очень сложные. С помощью этого форума я смог сделать формулу практически универсальной вида ИНДЕКС (ПОИСКПОЗ.... ; ПОИСКПОЗ;....). Учитывая, что путь к файлам находится на сервере, типа X:\Каталог номер 1\Каталог номер 2\[Название книги номер 1]Лист такой-то!...и так далее, формула с трудом умещается на 2 строчках. Но не суть. Назовем ее Книга №3.

Проблема: Книга третьего типа (№3) при открытии НЕ учитывает изменение данных в Книге №1. Чтобы результаты расчетов стали актуальными нужно открыть каждую Книгу 2.1.....2.10, ничего не меняя в ней сохранить её. И тогда уже открывать Книгу №3.

В принципе, наверное, это логично. С точки зрения Excel 2013. Но капец как неудобно.

Основная цель: чтобы после изменения значений цены в Книге №1 (и её закрытия) значения в Книге №3 были актуальными БЕЗ необходимости открывать Книги №2.1....2.10.

Я тут основательно погуглил "обновление связей макросом". Даже попробовал внедрить в Книгу №3 эти макросы. Разные. Результат нулевой. Что сделал:

1. В Книге №3 поставил при открытии Книги "Не задавать вопрос и обновлять связи".
2. В Книгах №2.1...2.10 сделал то же самое.
3. Попытался в Книгу №3 разные макросы вставлять. Тут самое трудное, т.к. я вообще ничего не понимаю в VBA. Всё, что могу - тупо скопипастить код.

Я нашел вот такие:

Первый - обновляет каждые x минут/секунд. Я сделал каждые 2 секунды. Результат - счетчик в ячейке X1 тикает, макрос работает, но

Код
Sub go_()
    Up_
End Sub
Sub Up_()
    Dim tn_
    ActiveWorkbook.RefreshAll
    tn_ = TimeSerial(Hour(Now) + 0, Minute(Now), Second(Now) + 2)
    Range("X1") = Range("X1") + 1
    Application.OnTime tn_, "Up_"
End Sub


И второй, вроде как должен решить мою задачу, но увы. Ничего не меняется. Кстати, скопировал сюда макрос и только что увидел, что расширение стоит .xls, а у меня все файлы .xlsx, и у Книги №3 .xlsm (из-за макросов), может быть причина в этом? Щас попробую ... А, всё, увидел - там звездочка в конце.
Код
Sub update()
With Application 'операции с приложением/отключаем для повышения скорости работы макроса
.ScreenUpdating = False 'обновление экрана
.DisplayAlerts = False 'вывод системных сообщений
Папка = "C:\тест\"
'------------ Excel-файлы в этой папке ------------------
Имя = Dir(Папка & "*.xls*")
Do While Имя <> ""
With .Workbooks.Open _
(Filename:=Папка & Имя, UpdateLinks:=True)
'здесь Ваш макрос делает свое грязное дело
.Close SaveChanges:=True
End With
Имя = Dir
Loop
.ScreenUpdating = True 'обновление экрана
.DisplayAlerts = True 'вывод системных сообщений
End With
End Sub

Все эти коды я нашел на просторах инета, автор их - не я.

upd: изменил отображение кода
Изменено: boberchik - 09.06.2021 09:27:36
 
del
Изменено: msi2102 - 15.06.2021 11:55:10
 
Может, у кого какие идеи есть?
 
boberchik, Вы ещё файлик с примером приложите
 
Цитата
msi2102 написал:
boberchik, Вы ещё файлик с примером приложите
Блин, ну я бы с радостью! Но у меня Альфа-книга ссылается на Основной расчет, а Расчет ссылается на Справочник. И в каждой их этих Книг несколько Листов (а то и десятков), а на каждом листе десятки таблиц, в каждой таблице сотня-другая ячеек. Таблицы - просто однотипный набор ячеек, визуально оформленный так, чтобы выглядел отдельной таблицей. Никаких "Умных таблиц" нет. Выкладывать "как есть" я это не могу по понятным причинам.

Если появится возможность, создам аналог всего этого безобразия без конкретных данных. Прям очень-очень постараюсь.
Изменено: boberchik - 15.06.2021 14:41:36
 
Некоторые функции не работают с закрытыми книгами (и начинают работать,  если эти книги открыть): СУММЕСЛИ, СУММЕСЛИМН, СЧЕТЕСЛИ, СЧЕТЕСЛИМН,  СМЕЩ, ... См. также здесь.
Владимир
 
Цитата
boberchik написал:
каждые 2 секунды
это так
Код
tn_ = Now + TimeValue("00:00:02")

а то, что у вас ждет дату "30.12.1899 15:02:47"
 
Если Вы просто обновляете данные может Вам начать пользовать Power Query.
По Вашему первому сообщению, без файлов с примерами, трудно понять, что именно Вы хотите видеть
Изменено: msi2102 - 15.06.2021 15:05:31
 
Цитата
msi2102 написал:
Если Вы просто обновляете данные может Вам начать пользовать Power Query.
По Вашему первому сообщению, без файлов с примерами, трудно понять, что именно Вы хотите видеть
За наводку на Power Query огромное спасибо! Загуглил, что это такое. После прочтения описания стало понятно, что я многое упустил, пользуясь Excel без этой штуки. Но ничего, наверстаю.

Да, без файлов с примерами трудно. Я бы и сам не факт, что понял бы суть проблемы :)


Цитата
RAN написал:

а то, что у вас ждет дату "30.12.1899 15:02:47"
Попозже поизучаю, спасибо.

Цитата
sokol92 написал:
Некоторые функции не работают с закрытыми книгами (и начинают работать,  если эти книги открыть): СУММЕСЛИ, СУММЕСЛИМН, СЧЕТЕСЛИ, СЧЕТЕСЛИМН,  СМЕЩ, ... См. также  здесь .
Да, это я не так давно осознал уже на примере функции ДВССЫЛ. Очень был удивлен, что она работает только с открытыми книгами. Но в контексте моей задачи отношения не имеет. У меня все функции вида - ИНДЕКС(ПОИСКПОЗ... ПОИСКПОЗ...)
Страницы: 1
Читают тему (гостей: 1)
Наверх