Страницы: 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, Вы ещё файлик с примером приложите
 
Блин, ну я бы с радостью! Но у меня Альфа-книга ссылается на Основной расчет, а Расчет ссылается на Справочник. И в каждой их этих Книг несколько Листов (а то и десятков), а на каждом листе десятки таблиц, в каждой таблице сотня-другая ячеек. Таблицы - просто однотипный набор ячеек, визуально оформленный так, чтобы выглядел отдельной таблицей. Никаких "Умных таблиц" нет. Выкладывать "как есть" я это не могу по понятным причинам.

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

Батник засунул в планировщик Винды, запускается каждый день. Батник запускает макрос. Засунуть в планировщик макрос нельзя, это так не работает.
Макрос открывает поочередно нужные Книги, сохраняет и закрывает. Всё просто. Таким образом решается главная задача этой темы.

Батник выглядит вот так:
Код
@echo off
%~d0
cd "%~p0"
echo Выполняем.
powershell /nologo /ExecutionPolicy bypass .\script.ps1
echo Готово.
"pause"
Для отладки удалите кавычки на pause. Тогда окно консоли не закроется автоматически.

Скрипт выглядит так:
Код
$excel = new-object -comobject excel.application
$wbook = $excel.workbooks.open("C:\test\Книга 2.xlsx")
$excel.visible = $false
start-sleep -seconds 5
$wbook.save()
$excel.quit()

$excel = new-object -comobject excel.application
$wbook = $excel.workbooks.open("C:\test\Книга 3.xlsx")
$excel.visible = $false
start-sleep -seconds 5
$wbook.save()
$excel.quit()
У меня Книг для открытия больше двух, но я оставил только 2 блока, чтобы не пугать размером скрипта. Там всё повторяется, кроме названия Книг.

Отображение окна Excel выключено ($false). Задержку после открытия поставил 5 секунд.
bat-файл и макрос находятся в одном каталоге. Это важно.

Теперь каждый вечер все Книги открываются, таким образом обновляются связи с файлом "Цены", сохраняются и закрываются автоматически. И сводная таблица имеет свежие, обновленные итоговые данные. Без необходимости вручную это делать.

Информацию взял отсюда:
Открыть, сохранить и закрыть файл Excel
Скрипт сразу не заработал (естественно :)). Поэтому пришлось эту тему тоже загуглить:
Ошибка «Невозможно загрузить файл ….ps1, так как выполнение сценариев отключено в этой системе»
 
Возможно такое решение
Код
Const interval As Date = 2 / 86400
Private Delayed As Collection

Sub UpdateLinks()
    Dim LS(), i&
    LS = ThisWorkbook.LinkSources
    Application.DisplayAlerts = False
    On Error Resume Next
    
    Set Delayed = New Collection
    For i = 1 To UBound(LS)
        ThisWorkbook.UpdateLink LS(i)
        If Err.Number Then    'если ссылка недоступна, помещаем ее
            Delayed.Add LS(i) 'в коллекцию "отложенных"
            Err.Clear
        End If
    Next
    
    Application.DisplayAlerts = True
    If Delayed.Count Then
        Application.OnTime Now + interval, "DelayedUpdate"
    End If
End Sub

Sub DelayedUpdate()
    Static n&, lnk
    Application.DisplayAlerts = False
    On Error Resume Next
    
    For Each lnk In Delayed
        ThisWorkbook.UpdateLink lnk
        If Err.Number Then
            Err.Clear
        Else
            Delayed.Remove 1
        End If
    Next
    n = n + 1
    If n < 2 Then 'кол-во поворных попыток обновления не обновленных сурсов
        If Delayed.Count Then
            Application.OnTime Now + interval, "DelayedUpdate"
        End If
    Else
        Set Delayed = Nothing
    End If
    
    Application.DisplayAlerts = True
End Sub
Изменено: testuser - 14.06.2023 08:10:13
Страницы: 1
Наверх