С выходом Power Query в массы все чаще стал появляться вопрос: как обновить запросы один за другим в заданном порядке?
Если еще не работали с этой надстройкой и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query - что такое и почему её необходимо использовать в работе?
Описание проблемы
В книге может быть десять и более запросов. И только 4 из них надо обновлять постоянно, да еще и в заданном порядке. Например, у нас есть с десяток запросов по продажам, отгрузкам и прочей аналитике, но только 4 из них нам надо обновить. При этом обновить надо в строгой последовательности: "Запрос — Отделы", "Запрос — Сотрудники", "Заказы и Продажи", "Запрос — Бюджет". При этом обязательно необходимо, чтобы "Запрос — Бюджет" обновился последним, т.к. он зависит от всех остальных.
 
Какие есть варианты решения?

 
Что еще может пригодиться в решении задачи:


 
Обновление вручную
Можно обновить запросы стандартными методами, поочередно вручную обновляя каждый запрос. Но это не всегда удобно, особенно если запросов более 5. Значит этот вариант не разбираем в деталях в принципе - все его плюсы и минусы очевидны.


 
Данные -Обновить все
Кнопка Данные(Data) -Обновить все(Refresh All) не просто обновляет все без исключения запросы в книге, но и порядок запросов при этом никак не определить, следовательно мы не можем быть уверены в том, что обновление произошло именно в нужном нам порядке. Но все же не станем отбрасывать этот вариант как не рабочий - если проблема лишь в порядке обновления, то решение все же есть.
На самом деле, если мы имеем дело с Power Query, то правильнее всего выстраивать все взаимосвязи уже на стадии создания этих запросов в любом случае. Т.е. создавать их в правильной последовательности и "затягивать" данные в основной запрос в самую последнюю очередь, ориентируясь на результаты вспомогательных запросов. Это оптимальный вариант, т.к. в этом случае достаточно будет просто обновить нужный запрос, а Power Query сама определит какие вспомогательные запросы необходимо обновить для корректного результата. При этом встроенный механизм Power Query достаточно хорошо справляется с кешированием данных и даже если обновление происходит через кнопку Обновить все(Refresh All), то даже если на один и тот же вспомогательный запрос будет завязано несколько других - он будет обновлен лишь один раз, а не при обновлении каждого запроса.
Но здесь у нас опять же возникает проблема - если нам не надо обновлять все запросы в книге - как Power Query узнает, какие из них надо обновлять, а какие нет? Ответ - никак. Поэтому хоть и следует всегда стараться выстраивать цепочки взаимодействий запросов в "правильном" порядке - нашу проблему с обновлением только части запросов это никак не решит. В этом случае можно создать мастер запрос, в который просто через объединение запросов собирать все нужные. И обновление этого запроса должно инициировать обновление всех указанных в нем запросов со всеми их цепочками. Как создать подобный мастер запрос:

  • идем на вкладку Главная -Объединить -Добавить запросы:
    Power Query - Меню Добавить запросы
  • в появившемся окне выбираем "Три таблицы и больше"
    Power Query - Добавить таблицы в запрос
  • добавляем все нужные запросы
  • нажимаем Ок

Все, запрос готов. Теперь достаточно обновить только его, чтобы все связанные запросы обновились. Правда, есть одна проблема: если данных много, то такой запрос их увеличит вдвое - ведь мы в один запрос собираем все остальные...


 
Собственный порядок обновления через Visual Basic for Applications
В решении через мастер-запрос есть одна проблема - запросы могут быть не только Power Query, но и более старых версий. Здесь вариантов вообще не остается - только Visual Basic for Applications. Да и в случае, если запросы уже созданы ранее кем-то еще или навыков создать мастер-запрос нет.

'---------------------------------------------------------------------------------------
' Author : Щербаков Дмитрий(The_Prist)
'          Профессиональная разработка приложений для MS Office любой сложности
'          Проведение тренингов по MS Excel
'          https://www.excel-vba.ru
'          info@excel-vba.ru
' Purpose: Код обновляет поочередно указанные запросы в заданном порядке
'---------------------------------------------------------------------------------------
Sub RefreshConnections()
    Dim oc, xQuery, aConnections, xConName
    Dim IsBG_Refresh As Boolean
    'создаем перечисление всех запросов, которые надо обновить
    'располагаем их в том порядке, в котором необходимо обновлять
    aConnections = Array("Запрос — Отделы", "Запрос — Сотрудники", "Заказы и Продажи", "Запрос — Бюджет")
    'перебираем и обновляем все запросы поочередно
 
    For Each xConName In aConnections
        Set oc = ActiveWorkbook.Connections(xConName)
        Set xQuery = Nothing
        Select Case oc.Type
        Case Excel.XlConnectionType.xlConnectionTypeODBC
            Set xQuery = oc.ODBCConnection
        Case Excel.XlConnectionType.xlConnectionTypeOLEDB
            Set xQuery = oc.OLEDBConnection
        Case Else
            'запрос выгружен в таблицу на листе
            If oc.Ranges.Count > 0 Then
                Set xQuery = oc.Ranges(1).QueryTable
            End If
        End Select
        'непосредственно обновление запроса с ожиданием окончания обновления
        If Not xQuery Is Nothing Then
            'запоминаем значение обновления в фоне для запроса
            IsBG_Refresh = xQuery.BackgroundQuery
            'выставляем принудительно ждать завершения запроса
            xQuery.BackgroundQuery = False
            'обновляем запрос
            xQuery.Refresh
            'возвращаем обновление в фоне в первоначальное состояние
            xQuery.BackgroundQuery = IsBG_Refresh
        End If
    Next
End Sub

Как использовать: Для начала надо убедиться, что разрешены макросы и при необходимости включить их: почему не работает макрос. Затем копируем код выше, из Excel переходим в редактор VBA(Alt+F11) -Insert -Module. Вставляем туда скопированный код. Теперь код можно вызывать нажатием клавиш Alt+F8 -выделяем RefreshConnections -Выполнить(Run).
Чтобы использовать приведенный код для своих запросов достаточно в строке:

aConnections = Array("Запрос — Отделы", "Запрос — Сотрудники", "Заказы и Продажи", "Запрос — Бюджет")

внутри Array() перечислить в кавычках через запятую все имена запросов, которые необходимо обновить. Перечислять надо сразу в том порядке, в котором их требуется обновлять.
Код обновляет не только запросы Power Query, но и запросы более старых версий, выгруженных просто на лист.


 
Вывести список всех запросов в книге на лист
Что делать, если я не знаю, как правильно называются запросы
Да, вполне логичная ситуация, когда запросов много и переписывать имя каждого запроса не такая уж простая задача. Очень легко ошибиться в имени и тогда обновления не будет. Поэтому ниже я прилагаю код, который выводит на новый лист имена всех запросов:

'---------------------------------------------------------------------------------------
' Author : Щербаков Дмитрий(The_Prist)
'          Профессиональная разработка приложений для MS Office любой сложности
'          Проведение тренингов по MS Excel
'          https://www.excel-vba.ru
'          info@excel-vba.ru
' Purpose: Код выводит на лист все запросы в текущей книге
'---------------------------------------------------------------------------------------
Sub GetAllConnections()
    Dim ws As Worksheet
    Dim lr As Long
    Dim oc, aList()
    'создаем массив для запоминания имен всех запросов
    ReDim aList(1 To ActiveWorkbook.Connections.Count, 1 To 1)
    For Each oc In ActiveWorkbook.Connections
        lr = lr + 1 'увеличиваем счетчик
        aList(lr, 1) = oc.Name
    Next
    'создаем новый лист, в который записываем имена всех запросов
    Set ws = ActiveWorkbook.Worksheets.Add(before:=ActiveWorkbook.Worksheets(1))
    'оформляем заголовок(для эстетики)
    ws.Cells(1, 1).Value = "Имя запроса"
    ws.Cells(1, 1).Font.Bold = True
    'записываем имена всех запросов
    ws.Cells(2, 1).Resize(lr, 1).Value = aList
End Sub

Так же см.:
Как дождаться обновления выполнения запроса
Порядок обновления запросов
Управление обновлениями запросов
Время обновления запросов

Loading

8 комментариев

  1. Спасибо большое за статью, очень помогла
    Подскажите, а есть возможность часть запросов обновлять вместе, а часть по отдельности в одном макросе?

    1. Ирина, если правильно понял вопрос - то возможно поможет следующее: открываете панель запросов(Данные -Запросы и подключения) -правая кнопка мыши на запросе -Свойства. Там есть галочка "Обновить это подключение по команде Обновить все". Для тех запросов, которые надо обновлять вместе - галочку оставляете. Для остальных - снимаете.
      В итоге те запросы, которые надо обновить вместе можно обновлять кнопкой "Обновить все", а другие - в заданном порядке.
      Только помните - если есть связанные запросы, то даже если галочка не установлена, а от этого запроса зависит результат другого запроса - он будет обновлен.

  2. Здравствуйте! Использовала Ваш макрос для обновления Запросов, но у меня есть один нюанс. Указала все запросы в той последовательности, в которой необходимо их обновлять. Источники данных - разные (внешние). Но есть один запрос, который создан на основании другого запроса из этого файла, который я обновляю макросом. Как мне обновить этот запрос последним. Если я его включаю в массив всех имен запросов - выдает ошибку. Надеюсь, я Вас не запутала. )

    1. Сложно ответить без файлов и без понимания, какая вообще ошибка возникает(текст, номер) и на какой строке кода.
      Возможно, имя запроса неверно задано. Возможно, идет ошибка обновления вложенного запроса. В любом случае без конкретики все сводится к гаданию.

      1. Добрый день! Ошибка "Элементов в перечислении было недостаточно для выполнения операции" на строке xQuery.Refresh. И еще возможно немаловажный момент, я работаю в Excel 2013. Запросы вставляла копированием после выполнения макроса Sub GetAllConnections().

          1. Проверила Запрос на котором спотыкается, да там имя Листа изменилось. Но ошибка изначально была не в этом, но я ее тоже устранила, она была в том, что у меня был запрос с именем "X", я его задублировала, затем дубль оставила, а первоначальный запрос удалила, имя изменила на "X". А в подключениях как потом выяснилось имя осталось "X(1)". И проблема все-таки не в этом, мне сейчас не хватает времени описать ее, чуть позже...

          2. У меня на офис 365 отрабатывает корректно код. Как я уже ранее сказала, возможно мне проблем добавляет факт, что у меня Excel 2013 на работе. В Excel 2013 при запуске макроса начинают обновляться сразу несколько запросов и затем почти сразу выдается сообщение, что обновление завершено. Но и понятно, что запросы не обновились… Все запросы используют внешние источники. А есть еще один запрос, который я обновляю отдельно после всех, его я делаю так… После выгрузки таблиц в книгу, я создаю запрос и загружаю этот же файл в редактор и беру все таблицы с Лист1. Поэтому я в макросе добавила сохранение книги, а потом уже добавила обновление последнего запроса. Посмотрите, пожалуйста, макрос, а то я могу ведь накрутить...)
            Sub RefreshConnections()
            ' Выводим сообщение с именами обновленных запросов
            MsgBox "Запросы обновлены", vbInformation
            On Error Resume Next
            With Worksheets(1).UsedRange: End With
            ThisWorkbook.Save
            ThisWorkbook.Connections("Запрос — ХХХ_ХХ(1)").Refresh
            MsgBox "Укажите папку для сохранения файла", vbInformation
            Call SavePrefix
            ActiveWorkbook.Worksheets(1).Activate
            End Sub
            Sub SavePrefix()
            Dim fileName As String
            Dim Path As String
            Dim Prefix As String
            fileName = ThisWorkbook.Name
            Prefix = Format(Now, "dd.mm.yyyy_hh-mm_")
            1 With Application.FileDialog(msoFileDialogFolderPicker)
            If .Show = -1 Then
            Path = .SelectedItems(1)
            Else
            MsgBox "Вы не выбрали путь для сохранения.": GoTo 1
            End If
            End With
            Excel.Application.DisplayAlerts = False
            ThisWorkbook.SaveAs fileName:=Path & "\" & Prefix & fileName & ".xlsx", FileFormat:=xlOpenXMLWorkbook
            Excel.Application.DisplayAlerts = True
            End Sub
            Но вот и вся проблема...Что думаете по этому поводу, понимаю, что скорее всего не в коде дело, но как же мне тогда быть? Мне это архиважно именно на работе, потому что коллеги не будут отслеживать что обновилось, а что нет и это мне необходимо автоматизировать. Я так надеялась, что я смогу это сделать и вот такое...((

Добавить комментарий

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.