Excel как обновить запросы - Word и Excel - помощь в работе с программами

Добавление данных и обновление запроса

Наконец, добавьте данные в источник данных и обновите их в Excel. Power Query автоматически применяет каждое созданное преобразование. Это означает, что вам нужно создать запрос только один раз, а затем выполнить его в любое время.

Добавьте три строки данных в текущую таблицу «Данные о продажах». Чтобы подтвердить формулу вычисления премии, добавьте одну запись стоимостью более 25 000 рублей.

Яблоки	Андрей Седини	2/14/19	25,000
Груши	Ян Котасс	2/14/19	35,000
Кружки	Ольга 365	2/14/19	15,000

Завершив ввод данных, выберите конструктор таблиц и >обновить все.

После того как Excel завершит обновление данных, подтвердить результаты на листах данных продаж PQ.

Примечание Если вы вводите новые данные вручную или копируете и в добавляете их, убедитесь, что они были добавлены на исходный, а не на таблицу Power Query.

Нужна дополнительная помощь?

Источник

Хитрости »

12 Январь 2023 880 просмотров

С выходом Power Query в массы все чаще стал появляться вопрос: как обновить запросы один за другим в заданном порядке?

Если еще не работали с этой надстройкой и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query — что такое и почему её необходимо использовать в работе?
Описание проблемы

В книге может быть десять и более запросов. И только 4 из них надо обновлять постоянно, да еще и в заданном порядке. Например, у нас есть с десяток запросов по продажам, отгрузкам и прочей аналитике, но только 4 из них нам надо обновить. При этом обновить надо в строгой последовательности:

«Запрос — Отделы», «Запрос — Сотрудники», «Заказы и Продажи», «Запрос — Бюджет»

. При этом обязательно необходимо, чтобы «Запрос — Бюджет» обновился последним, т.к. он зависит от всех остальных.

Какие есть варианты решения?

Вручную
Кнопка Обновить все(Refresh All)
Написать собственный порядок обновления через Visual Basic for Applications

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

Вывести список имен имен всех подключений на лист

Обновление вручную

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

Данные -Обновить все

Кнопка Данные(Data) —Обновить все(Refresh All) не просто обновляет все без исключения запросы в книге, но и порядок запросов при этом никак не определить, следовательно мы не можем быть уверены в том, что обновление произошло именно в нужном нам порядке. Но все же не станем отбрасывать этот вариант как не рабочий — если проблема лишь в порядке обновления, то решение все же есть.
На самом деле, если мы имеем дело с Power Query, то правильнее всего выстраивать все взаимосвязи уже на стадии создания этих запросов в любом случае. Т.е. создавать их в правильной последовательности и «затягивать» данные в основной запрос в самую последнюю очередь, ориентируясь на результаты вспомогательных запросов. Это оптимальный вариант, т.к. в этом случае достаточно будет просто обновить нужный запрос, а Power Query сама определит какие вспомогательные запросы необходимо обновить для корректного результата. При этом встроенный механизм Power Query достаточно хорошо справляется с кешированием данных и даже если обновление происходит через кнопку Обновить все(Refresh All), то даже если на один и тот же вспомогательный запрос будет завязано несколько других — он будет обновлен лишь один раз, а не при обновлении каждого запроса.
Но здесь у нас опять же возникает проблема — если нам не надо обновлять все запросы в книге — как 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

Статья помогла? Поделись ссылкой с друзьями!

Видеоуроки

Поиск по меткам

Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

Источник

kolyaya

Пользователь

Сообщений: 160
Регистрация: 17.02.2018

Добрый день.
Есть ряд зависимых запросов (Запрос1, Запрос2, Запрос3)
Нужно что бы запросы обновлялись по очереди.
Запустился Запрос1, дождаться окончания обновления, обновить Запрос2, дождаться окончания обновления, обновить Запрос3
Помогите пожалуйста.

Hypohelix Пользователь Сообщений: 108 Регистрация: 29.10.2017	Программный код, как и яды, лучше тестировать по капельке Люблю изобретать велосипеды с колесами произвольной формы

kolyaya

Пользователь

Сообщений: 160
Регистрация: 17.02.2018

Попробовл использовать vba код из указанной темы, выскакивает ошибка Subscript out of range. Можете подправить?
И как в последующем обновить следующий запрос после обновления первого еще раз прописать код

set oc = ThisWorkbook.Connections(«test1»)
IsBG_Refresh = oc.OLEDBConnection.BackgroundQuery
oc.OLEDBConnection.BackgroundQuery = False
oc.Refresh
oc.OLEDBConnection.BackgroundQuery = IsBG_Refresh

только для test1.

Андрей VG

Пользователь

Сообщений: 11878
Регистрация: 22.12.2012

Excel 2016, 365

05.07.2020 12:11:26

Доброе время суток

Цитата
kolyaya написал: Subscript out of range

Нет там такого подключения. Достаточно провести простой эксперимент

Код
Public Sub PrintConnectionNames() Dim p As WorkbookConnection For Each p In ThisWorkbook.Connections Debug.Print p.Name Next End Sub

Чтобы в этом убедиться

Цитата
Запрос — test Запрос — test1

kolyaya Пользователь Сообщений: 160 Регистрация: 17.02.2018	Спасибо, заработало. Вроде по очереди обновляются. Буду пробовать в оригинальных файлах.

Андрей VG

Пользователь

Сообщений: 11878
Регистрация: 22.12.2012

Excel 2016, 365

У вас очевидно, как и у меня, Excel 365. В нём убрали стандартную кнопку подключения, где можно было сразу посмотреть что как называется — заменили панелью. Поэтому сейчас, чтобы увидеть правильные названия, то либо скрипт выше, либо по кнопке «Существующие подключения».

Михаил Л

Пользователь

Сообщений: 2639
Регистрация: 19.02.2020

05.07.2020 13:39:34

А можно вариант с поочередным обновлением запросов из списка?
Типа как то так (не работает):

Код

Sub RefreshPQ2()
    
    Dim ws As Worksheet, qt As QueryTable, oc As Object, IsBG_Refresh As Boolean
    Dim i As Long
    spisok = ("Запрос — test";"Запрос — test1";"Запрос — test3";"Запрос — test2")
    
  
    For i = 1 To 4
       
       Set oc = ThisWorkbook.Connections(spisok(i))
       IsBG_Refresh = oc.OLEDBConnection.BackgroundQuery
       oc.OLEDBConnection.BackgroundQuery = False
       oc.Refresh
       oc.OLEDBConnection.BackgroundQuery = IsBG_Refresh
    Next
End Sub

Прикрепленные файлы

updQuery.xlsm (32.92 КБ)

НА МЯСО В ПРОФИЛЕ

Андрей VG

Пользователь

Сообщений: 11878
Регистрация: 22.12.2012

Excel 2016, 365

05.07.2020 14:03:11

Цитата
Михаил Л написал: spisok = («Запрос — test»;»Запрос — test1″;»Запрос — test3″;»Запрос — test2″)

А почему вы решили, что это должно работать?

Код

Sub RefreshPQ2()
    
    Dim ws As Worksheet, qt As QueryTable, oc As Object, IsBG_Refresh As Boolean
    Dim i As Long, spisok
    spisok = Array("Запрос — test", "Запрос — test1", "Запрос — test3", "Запрос — test2")
    
  
    For i = LBound(spisok) To UBound(spisok)
       
       Set oc = ThisWorkbook.Connections(spisok(i))
       IsBG_Refresh = oc.OLEDBConnection.BackgroundQuery
       oc.OLEDBConnection.BackgroundQuery = False
       oc.Refresh
       oc.OLEDBConnection.BackgroundQuery = IsBG_Refresh
    Next
End Sub

Михаил Л

Пользователь

Сообщений: 2639
Регистрация: 19.02.2020

05.07.2020 14:19:24

Цитата
Андрей VG написал: это должно работать?

Не должно работать. Я только набросал для понимания того, что мне надо. Удивило что я с этим угадал — (spisok(i))
Спасибо большое

НА МЯСО В ПРОФИЛЕ

	kolyaya, Здравствуйте, столкнулась с таким же вопросом, но совершенно не разбираюсь в языке VBA Можете пожалуйста помочь готовым решением или помочь разобраться?

Alien Sphinx

Пользователь

Сообщений: 542
Регистрация: 10.01.2023

Excel 365

#11

17.02.2023 10:58:16

«Кончита ждала Резанова тридцать пять лет…»

Цитата
Профиль kolyaya Пользователь Сообщений: 160 Баллов: 0 Регистрация: 17.02.2018 Последний визит: 06.05.2022 20:50:54

Пришелец-прораб.

Дмитрий(The_Prist) Щербаков

Пользователь

Сообщений: 14181
Регистрация: 15.09.2012

Профессиональная разработка приложений для MS Office

#12

17.02.2023 11:02:49

Цитата
Irina Bogdanova написал: помочь готовым решением

Как обновить запросы Power Query(и не только) в указанном порядке?

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

babirich Пользователь Сообщений: 2 Регистрация: 16.09.2022	#13 16.03.2023 08:48:06 Дмитрий(The_Prist) Щербаков, Спасибо!

Источник

Содержание

Как в excel обновить связи
Управление обновлением внешних ссылок (связей)
Дополнительные сведения о внешних ссылках
Управление связями с другими книгами
Ручное обновление всех (или никаких) связей в книге
Частичное обновление связей с другими книгами вручную
Управление выдачей запроса на обновление всех связей
Отключение выдачи запроса на обновление связей данной книги и обновление связей вручную
Настройка обновления связей по запросу при открытии файла
Если никакие связи не создавались
Разрыв связей с источником
Замена одной формулы ее вычисляемым значением
Если известно, что подключение к исходному файлу отсутствует
Если требуется работать с имеющимися данными, а не заменять их новыми
Если обновление в последний раз заняло слишком много времени
Если эта книга создана другим пользователем и вы не знаете, почему появился этот запрос
Если вы каждый раз одинаково отвечаете на этот запрос и хотите, чтобы он больше не отображался
Если известно, что исходный файл доступен для подключения к нему
Если используется параметрический запрос
Устранение неполадок в связях между таблицами
Сообщение. Связи не были обнаружены
В сводную таблицу добавлены несвязанные поля, однако сообщение не выдается
Отсутствует допустимая связь между таблицами
При автоматическом обнаружении созданы неверные связи
�� Excel 2010
Автоматическое обновление ссылок в Excel 2007 [ТЕМА ЗАКРЫТА — двойное нарушение — благодарим stuf]
Регулировка обновлений связей с внешними данными (Формулы)

Как в excel обновить связи

Управление обновлением внешних ссылок (связей)

Смотрите также другом расширении, а 12:04, в сообщении №2. Например: «=ЕСЛИОШИБКА(ВПР($B14;’\mainplanningplanning данными, но ужеСделайте обратный ход бы обновление происходило �� . � том случае, еслиСоздание связи

Дополнительные сведения о внешних ссылках

.нажмите кнопку кнопкойФормулыНа вкладкеПараметрыПри создании внешних ссылок именно как «Двоичная № 1200?’200px’:»+(this.scrollHeight+5)+’px’);»>сейчас файл 2014Controlфайл№2.xlsx.xlsb]матрица’!$A:$RG;143;ЛОЖЬ);0)» несколько в другомдля дополнительной информации по запросу. ��. 2007 ��

связь является действительной.. Дополнительные сведения см.

При импорте нескольких таблицПараметрыПараметры вставкив группе

Данныев области сведений (также называемых связями) книга Excel», потому

Управление связями с другими книгами

№1 открывается ОЧЕНЬEkaterina ракурсеДмитрийДаже не знаю,:( �� .Если алгоритм автоматического обнаружения в разделе Создание Excel пытается обнаружитьи выберите категорию, а затемОпределенные именав группе о документе, а можно задать для что насколько я долго: Poltava, тогда может

Ручное обновление всех (или никаких) связей в книге

Дано: 2 файла: Добрый вечер. У что произошло, я�� ,

предлагает связь, которая

связи между двумя и определить связиДополнительно выберите командунажмите кнопкуПодключения затем выберите команду них поддержку и

Частичное обновление связей с другими книгами вручную

знаю, это немного

Странно, что вообще

подскажете, какие именно Эксель. В одном меня такой вопрос. только установил офис ��  ��  не решает бизнес-задачи,

таблицами. между этими таблицами,.Значения

Диспетчер именнажмите кнопкуВключить это содержимое расписание обновления. В

облегчает файл и открывается.

макросы? Я честно из них (назовём При использовании функции

Управление выдачей запроса на обновление всех связей

Отключение выдачи запроса на обновление связей данной книги и обновление связей вручную

 то необходимо удалитьАвтоматическое обнаружение связей запускается поэтому нет необходимостиВ Excel 2007 нажмите..Изменить связи. случае изменения значения

сокращает время зависания.Вам в Вашей говоря в макросах его №1) есть ВПР в книге настройки все стоятdlimon

� ��  ее и создать только для мер

создавать связи вручную

 кнопку Microsoft OfficeНажмите кнопкуВ столбце.Закройте все книги. в ячейке приложениеЦитата пред. теме написали,

Настройка обновления связей по запросу при открытии файла

не сильна. Умею ссылки с формулами «1»(условно) данные беру по умолчанию, может

Если никакие связи не создавались

: Сегодня перешел с �� .  вручную с использованием и не запускается или создавать сложные, щелкнитеНе обновлять

ИмяНажмите кнопкуОткройте книгу, содержащую связи.

Microsoft Excel не_Boroda_, 29.10.2013 в что нужно делать. делать только самые на второй файл

из книги «2», в реестре что-то. экселя 2003 наSerge верного ключевого столбца. для вычисляемых полей, обходные решения, чтобыПараметры Excel. Обновление из источникавыберите имя, котороеЗапрос на обновление связей

Разрыв связей с источником

На вкладке выполняет поиск нового 12:57, в сообщении Неужели Вы думаете, простые, те которые (№2). Т.е. данные при этом, при Не знаете, где 2007. И сразу: � �� См. которые используются в

работать с даннымии выберите категорию при отсутствии подключения следует удалить, и.Данные значения, если это

№ 7200?’200px’:»+(this.scrollHeight+5)+’px’);»>Вам в что за несколько с помощью автоматического в №1 формируются

открытии книги «1» в реестре посмотреть столкнулся с проблемой. ��

К началу страницы метках строк и

целостным способом.Дополнительно к нему невозможно.

нажмите кнопкуЩелкнитев группе

не задано пользователем.

Вашей пред. теме дней Майкрософт придумал записывания делаются, и путём забирания их данные обновляются автоматически, опции экселя? У меня много

�� ?�?горь столбцов сводной таблицы.Иногда Excel не удается. Например, если книга-источникУдалить

Не отображать оповещение иПодключенияКнига, содержащая связи, называется написали, что нужно что-то новое? Вернее, которые соответственно получаются из №2. Возникла

Замена одной формулы ее вычисляемым значением

без запроса. ВсёФайл удален документов содержат ссылки�?горь: �� . Поэтому перед началом определить связь междуВ разделе расположена в сети, .

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

перепробовал, но никак- велик размер

на внешние файлы.: . � �� �� 2010 построения сводной таблицы таблицами. В этомОбщие а подключение кЕсли используется диапазон внешних связи

Изменить связи книга, с которой думаете, что за было бы лучше работают, когда нужно

данных. Когда открываешь не получается снять — [ Раньше при открытии �� , �� ��  несвязанные таблицы можно

случае используйте информациюснимите флажок сети отсутствует.

данных, параметр запроса.. установлена связь, называется несколько дней Майкрософт старого. записать макрос чуть

Если известно, что подключение к исходному файлу отсутствует

№1 (в момент автоматическое обновление. Есть,МОДЕРАТОРЫ таких документов эксель �� , � ��. �� добавить, однако связи из этой статьиЗапрашивать об обновлении автоматических

Если требуется работать с имеющимися данными, а не заменять их новыми

Нажмите кнопку может также использоватьВозможно, связь была создана

Если обновление в последний раз заняло слишком много времени

Из списка исходной книгой. Если придумал что-то новое?Ekaterina сложнее, чем просто когда №2 закрыт), конечно, вариань поставить] спрашивал обновить ссылки ��  2007 ��. � не будут видны для устранения ошибок связейНе обновлять данные из другой случайно путем перемещения

Если эта книга создана другим пользователем и вы не знаете, почему появился этот запрос

Источник открытая книга (конечный Вернее, так: «что-то: Да, очень много форматирование ячеек например то все ячейки, общий ручной пересчёт,Артем или нет. Сейчас �� . �� 2007 ��  до тех пор, автоматического обнаружения связей. Чтобы

Если вы каждый раз одинаково отвечаете на этот запрос и хотите, чтобы он больше не отображался

. Если этот флажок. книги. Может потребоваться или копирования диапазона

выберите связанный объект, файл) содержит связи новое, что было

данных. Как яEkaterina в которых есть но это неудобно,: так «предупреждение системы появляется предупреждение системы ��

��  пока поле не лучше понять требования снят, связи будутНажмите кнопку проверить и удалить ячеек, листа или

который необходимо изменить. с другими книгами бы лучше старого». и говорила.: Johnny, задача в связь с №2 сами понимаете. Подскажите

безопасности «Автоматическое обновление безопасности «автоматическое обновление ��  �� — �� будет перемещено в и механизмы обнаружения обновляться автоматически (безНе обновлять эти типы связей.

 диаграммы из однойДля выделения нескольких связей или файлами (которые

Из предыдущей темыЦитата том, чтобы при обозначаются как «знач». пожалуйста, как включить ссылок отключено»» - ссылок отключено». Я, �� — ��

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

называются исходными файлами), я не поняла,_Boroda_, 29.10.2013 в

открытии файла №1,

 А если при запрос на обновление. это и есть конечно, могу войти

Если известно, что исходный файл доступен для подключения к нему

��. ��  �� Значения Связи между таблицамиОдинаковый запрос для всех

Если используется параметрический запрос

получение текущих данных, при замене на Откройте книгу, а

объект, удерживая нажатой а исходные файлы что нужно делать

12:57, в сообщении

выскакивало окошко: «обновить

этом открыть файл

Спасибо. запрос, не пойму, в раздел «изменить

Устранение неполадок в связях между таблицами

��, �� !�� -. в модели данных. пользователей этой книги можно сэкономить время, возвращаемое значение. Чтобы затем найдите ненужные клавишу CTRL. изменятся, то связи

:(, поэтому просто № 7200?’200px’:»+(this.scrollHeight+5)+’px’);»>плюс к связи с внешними №2, то толькоАйболит чем принципиально это связи» и обновиться �� , �� Иногда таблицы, добавляемые вНа панели уведомлений всегда

Сообщение. Связи не были обнаружены

не обновляя все отменить случайную замену связи и разорвитеЧтобы выделить все связи, в открываемой книге убрала половину ссылок этому данными?» я бы тогда во всех: Все довольно просто: отличается от 2003 вручную, но это � �� сводную таблицу, просто автоматически отображается сообщениеПредупреждение:

связи. После открытия формулы ее значением, их или замените нажмите клавиши CTRL+A. могут указывать на на файл№2 иЦитата Ekaterina, 29.10.2013 отвечала «да» этих ячейках появляются СЕРВИС-ПАРАМЕТРЫ-ВЫЧИСЛЕНИЯ-ВРУЧНУЮ. При этом экселя? У вас

не рационально. В �� (��).Guest невозможно соединить с о необходимости установления Этот режим влияет на книги нажмите на немедленно после ввода их значениями.Нажмите кнопку устаревшие данные. заменила их значениями, в 12:45, вИ вот только правильные обновлённые значения. пересчет по ф9 же есть выбор параметрах экселя стоит

В сводную таблицу добавлены несвязанные поля, однако сообщение не выдается

=(((: ��. ��. �� другими таблицами. Например, связи при перетаскивании всех пользователей книги. вкладке или вставки значенияВажно:Обновить значения1. Конечная книга содержит что очень для сообщении № 4 в этом случае Но мне нужно,stuf обновлять или нет. галочка на «запрашивать�?горь

Отсутствует допустимая связь между таблицами

�� post_187218.JPG (58.02 две таблицы могут поля в область Если отключить обновлениеДанные нажмите кнопку При разрыве связей с. внешние ссылки. меня плохо

;’\main все связи обновлялись чтоб можно было: Та же проблем.Guest на обновлении автоматических: � �� ) иметь частично совпадающиеЗначения связей и выдачув группе

При автоматическом обнаружении созданы неверные связи

Отменить источником все формулы,Можно настроить автоматическое обновление2. Внешняя ссылка (илиТеперь время раздумываниявсе на сети бы, а в нажать кнопку условно Только когда я: Так это и связей», в меню �� ?горь данные, но несуществующей сводной таблицы запроса, пользователи этойПодключения. использующие источник, заменяются связей при запуске связь) представляет собой

над каждой операцией лежит остальных случаях не «обновить», и чтоб копирую всю папку есть запрос? Я «изменить связи» включено �� ?

�� Excel 2010

 иметь логических связей в случае, если
книги не будуткнопкуВыделите ячейку с формулой. на их текущее или после запуска. ссылку на диапазон немного сократилось. НоДа, на сети. обновлялись, чтоб компьютер при закрытом файле с комплектом книг думал, при открытии

 «пользователь указывает. «, параметры��. �� ». �� с другими используемыми

 это поле не знать, что данныеИзменить связиЕсли формула является значение. Например, связьПредупреждение: ячеек в книге-источнике. в этой теме Потому что впоследствии не подвисал, когда №2 все данные он берет старые файла появиться вопрос

 безопасности для связи�� 2007�� . �� таблицами. связано ни с устарели., а затем обновите формула массива, выделите =СУММ([Бюджет.xls]Годовой!C10:C25) будет заменена Этот режим влияет на

3. Книга-источник содержит ячейку, вопрос в другом. нужно будет, чтобы

 не нужно. в №1 обновились. с салке а «Обновить связи - в книге включен �� Если добавить в сводную одним из существующихНа вкладке связи только с диапазон ячеек, содержащих на =45. Эту всех пользователей книги. значение из которой Как сделать так, много-много людей на

_Boroda_ И при этом не те что Да или Нет».

 «запрос на автоматическое �� . �� . �� таблицу таблицу, которую в сводной таблицеДанные нужными исходными файлами. формулу массива. операцию нельзя отменить, Если отключить обновление загружается и помещается чтоб данные в разных компьютерах могли: Я охреневаю, дорогая есть параллельная задача:

 в текучей папке.Я обновление связей в� 2010 ��
�� ..
нельзя соединить с полей. Однако иногдав группе
Нажмите кнопкуВыбор диапазона, содержащего массив поэтому стоит сохранить связей и выдачу

 в конечную книгу. файле №1 обновлялись вносить изменения в редакция! сейчас файл №1

 система просит автоматических: Отключение запроса на книге». При этом

 ��  другой таблицей, то связь после уведомленияПодключенияНе обновлять версию файла запроса, пользователи этойКогда обе связанные книги при закрытом файле незащищённые области файлаRG — это открывается ОЧЕНЬ долго обновлений с правильным обновление связей при при открытии документа ��

обычно автоматическое обнаружение
обнаружить не удается.нажмите кнопку. Обратитесь к владельцу

Автоматическое обновление ссылок в Excel 2007 [ТЕМА ЗАКРЫТА — двойное нарушение — благодарим stuf]

Щелкните любую ячейку в. книги не будут открыты на одном №2. Ну т.е. №1. 475-й столбец. Да как раз из-за путем (1рис.) но открытии книг и никаких запросов на �� !��. �� не даст никаких Это может произойтиИзменить связи книги. Вы также диапазоне массив.На вкладке знать, что данные компьютере, связи обновляются как сделать так,Цитата еще по 1 этих связей с когда я нажимаю автоматическое обновление связей обновление не выскакивает.Z � 2007 �� результатов. В других по разным причинам.. можете выяснить, какие
На вкладкеДанные устарели. автоматически. Когда конечная чтоб при открытии_Boroda_, 29.10.2013 в 048 576 строкам. №2. Можно сделать окей. и захожуЭтот режим задаетсяКак решить эту: � ��

 ��. ��, �� случаях по результатамАлгоритм обнаружения связей зависитНажмите кнопку связи находятся вГлавнаяв группеНа вкладке книга открыта, а выскакивало окошко: «обновить 12:57, в сообщении Маленький такой, аккуратненький так, чтоб при на вкладку данные
только для текущего проблему? И вообще �� (��)? �� . в сводной таблице от внешнего ключевогоЗапрос на обновление связей книге. На вкладкев группеПодключения
Данные книга-источник не открыта, связи с внешними № 7200?’200px’:»+(this.scrollHeight+5)+’px’);»>еще очень диапазончик на почти

 каждом открытии файла изменить связи то пользователя и влияет как настроитьсистему безопасности �� post_187284.jpg (75.69�?горь видно, что поля столбца, имя которого.ДанныеРедактирование

нажмите кнопкув группе в области сведений данными?» любопытное название 500 млн. ячеек! данные не обновлялись?

не позволяют формировать схоже с именемВыберите нужный режим.в группенажмите кнопкуИзменить связиПодключения о документе возможноJohnnyЦитата Ekaterina, 29.10.2013

И это только И чтоб они ссыка которую он книги. На работу она поменьше думала

Z � �� xlsx осмысленные вычисления. первичного ключевого столбца.Примечание:ПодключенияНайти и выделить.нажмите кнопку
появление запроса на
: понятно, что сводной

 в 12:45, в в одной формуле! не обновлялись при предлагал в начале других пользователей он сама, а побольше: �� xlsm. ��При создании связей алгоритм Если имена столбцов При наличии разорванных связейнажмите кнопкуи выберите командуВ спискеИзменить связи обновление связей. Можно у вас нет. сообщении № 4плюс к этому любом измении в (2рис.)((

 автоматического обнаружения создает недостаточно похожи, рекомендуется будет появляться оповещениеИзменить связиВыделить группу ячеекИсточник. задать режим вывода иначе бы ифайл№2.xlsx.xlsbЦитата файле №1, аstuf режим также применяется уже сам решуZ. 56554 xls �� список всех возможных открыть окно Power об этом.

..
выберите связь, которуюНажмите кнопку этого запроса, а вопроса такого неЦитата Ekaterina, 29.10.2013

Ekaterina, 29.10.2013 в ТОЛЬКО тогда, когда
: 1рис к связям с обновлять мне связиmrMad-Cat ��

Регулировка обновлений связей с внешними данными (Формулы)

 связей исходя из Pivot и вручнуюНажмите кнопкуМожно ответить на запросВ диалоговом окне требуется разорвать.
Запрос на обновление связей также режим обновления возникло) в 12:04, в 12:45, в сообщении мне нужно?Файл удален другими программами. и можно ли: �� . �� 2010, значений, содержащихся в создать необходимые связиОбновить и запретить егоВыделить группу ячеекДля выделения нескольких связанных. всех связей безесли вы принципиально сообщении № 1 № 4200?’200px’:»+(this.scrollHeight+5)+’px’);»>;’Большое спасибо!- велик размер1. Нажмите кнопку открывать файлы с �� таблицах, и ранжирует между таблицами.. Это позволит гарантировать вывод для этойустановите флажок объектов щелкните каждыйВыберите нужный режим. вывода запроса. Кроме не желаете пользоватьсясейчас файл №1\Johnny — [ Microsoft Office Значок макросами и т.п. � �� . ��. возможные связи вТипы данных могут не
наличие в книге

 книги в будущем.Текущий массив

 из них, удерживаяПримечание:

 того, если книга сводными таблицами, открывается ОЧЕНЬ долгоmain
: забирайте данные своднойМОДЕРАТОРЫ кнопки, щелкните Параметрыdlimon �� Serge соответствии с их

 поддерживаться. Если любая самых последних данных.Не отображать запрос и. нажатой клавишу CTRL. Даже если запретить Excel содержит несколько связей,можно отключить вСтранно, что вообщевсе на сетиPoltava] Excel, а затем: Запрос на обновление �� — ��: ��

 вероятностью. Затем Excel создает из таблиц, используемыхСвязь с параметрическим запросом обновлять связи автоматическиНа вкладкеЧтобы выделить все связи, выдавать запрос на можно обновлять только настройках автоматическое обновление открывается. лежит: макросы в помощьstuf выберите категорию Дополнительно. не появляется. Вместо �� . ��

 ��? только наиболее вероятную в сводной таблице,
нельзя обновить без Главная нажмите клавиши CTRL+A. обновление связей, пользователь некоторые из них. связей и обновлять»файл№2″ — этоеще очень любопытное
Ekaterina
: 2рис2. В группе этого выскакивает на ��. �� �?горь связь. Поэтому, если
содержит столбцы только открытия книги-источника.
Этот параметр влияет нав группе
Щелкните элемент будет получать сообщенияЗакройте все книги. Если по мере необходимости: я для форума
 название: Johnny, какой сводной?Файл удален При расчете этой панели предупреждение системы � ��
: �� таблицы содержат несколько
неподдерживаемых типов данных,Нажмите кнопку все книги, которыеБуфер обменаРазорвать о наличии разорванных одна книга-источник будетв excel2010 Данные-Изменить изменила название. НаЦитата

 У меня нет- велик размер книги снимите флажок безопасности «Автоматическое обновление
 ��  �� .. �� столбцов, которые могут то связи обнаружитьНе обновлять
открываются на компьютере.нажмите кнопку.
связей
открыта, а остальные связи-Запрос на обновление
самом деле названиеEkaterina, 29.10.2013 в никакой сводной. — [ Запрашивать об обновлении ссылок отключено», а �� . � �� . �
 использоваться в качестве невозможно. В этом. Пользователи, которые откроютКопировать
имя связи автоматически не. закрыты, обновления не
связей-Не задавать вопрос
другое. Или Вас 12:45, в сообщенииУ меня в
МОДЕРАТОРЫ автоматических связей. Если
когда я нажимаю �� — ��
�� . �� ключей, некоторые связи случае необходимо создатьЗакройте конечную книгу. книгу на другом. удаляется, поэтому егоСуществует несколько вариантов выбора будут одинаковыми. и не обновлять смущает «.xlsx.xlsb» ? № 4200?’200px’:»+(this.scrollHeight+5)+’px’);»>файл№2 файле №1 в] этот флажок снят, «Включить содержимое», то
 �� , �� могут получить более связи между активнымиОткройте книгу-источник. компьютере, не будутНажмите кнопку может потребоваться удалить. в диалоговом окнеОткройте книгу, содержащую связи. связи Если это, то.xlsx.xlsb
ячейках находятся формулы,Ekaterina связи будут обновляться происходит автоматическое обновление �� . �� низкий ранг и таблицами в своднойОткройте конечную книгу. затронуты.ВставитьУдаление имениЗапрос на обновление связейЧтобы обновить связи, нажмитеEkaterina да, я сохранилаЦитата в которых есть: Вопрос опять про автоматически (без выдачи связей. А очень �� , �� не будут автоматически

 таблице вручную вНажмите кнопкуНа вкладке.На вкладке.
кнопку: Всем большое спасибо! этот файл в
Ekaterina, 29.10.2013 в связь с файлом связи с внешними запроса).
хотелось бы, что � �� , �� созданы даже в диалоговом окне

Источник

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава Содержание Следующая глава

По мере того, как вы создаете все больше и больше решений на основе Power Query, и начинаете понимать, сколько времени это экономит, вы захотите еще большей автоматизации. Да, вы можете просто щелкнуть правой кнопкой мыши таблицу, которая была импортирована с помощью Power Query, но даже это ощущается как… ручная операции. Нельзя ли запланировать обновление или, возможно, контролировать порядок обновления?

На самом деле вы можете использовать несколько различных методов автоматизации обновления решений Power Query:

Обновление при открытии книги
Обновление каждые Х минут
Обновить соединение по требованию с помощью VBA
Обновить все соединения по требованию с помощью VBA
Использовать расписание обновлений в сторонней надстройке

Рис. 16.1. Настройка параметров подключения

Скачать заметку в формате Word или pdf, примеры в формате архива

Планирование обновлений без использования кода

Первые два метода работают через пользовательский интерфейс и не требуют кода VBA. Они могут быть настроены на основе подключения, и вы даже можете автоматизировать обновление вплоть до Power Pivot. Откройте файл Automating Refresh.xlsx. Перейдите на вкладку Данные –> Запросы и подключения. В правой части окна Excel откроется область Запросы и подключения. Кликните правой кнопкой мыши имя запроса –> Свойства. Откроется окно Свойства запроса (рис. 1).

Обратите внимание, что флажок Фоновое обновление установлен по умолчанию. Этот параметр позволяет указать, следует ли продолжать работать в Excel во время обновления данных. Если вы снимите этот флажок, вы можете потенциально уменьшить время, необходимое для обновления вашего решения, но вы также заблокируете пользовательский интерфейс, что означает, что вы не сможете делать другие вещи, пока обновление запроса не будет завершено. Желательно оставить этот параметр включенным.

Следующий параметр – Обновлять каждые ХХ минут. Этот параметр очень пригодится, если вы извлекаете данные из веб-источника, который постоянно меняется, или если вы ориентируетесь на базу данных, которая регулярно обновляется. Для того, чтобы это обновление произошло книга Excel должна быть открыта. Допустимые значения для этого параметра – от 1 до 32 767.

Обновлять при открытии книги. Второй параметр в этом разделе – Удалять данные из внешнего диапазона перед сохранением книги. Установите этот флажок, если вы хотите сохранить книгу с определением запроса, но без использования внешних данных. Снимите этот флажок, чтобы сохранить, и определение запроса, и данные. Этот флажок, будет доступен только после установления флажка Обновлять при открытии книги.

Автоматизация обновления запросов с помощью VBA

Приемы, описанные выше, и не использующие макросы, обходятся без каких-либо окон, предупреждающих о безопасности. Кроме того, такие книги легче переносить в Power BI, так как они не вызывают никаких проблем с блокировкой. Однако если вы работаете исключительно в настольном экземпляре Excel, иногда может потребоваться предоставить пользователю удобный и очевидный способ обновления решений Power Query. Это может быть сделано с помощью записи кода VBA.

Можно создать макрос для обновления одного подключения Power Query. В файле Automating Refresh.xlsx и перейдите на лист Transactions. На листе расположена Таблица Transactions, а также сводная таблица. Допустим, вы хотите создать макрос для обновления их обеих. Перейдите на вкладку Разработчик. Если вы не видите вкладку Разработчик, щелкните правой кнопкой мыши любую вкладку на ленте и выберите Настроить ленту. В правом окне установите флажок рядом с вкладкой Разработчик и нажмите Ok. На вкладке Разработчик нажмите кнопку Запись макроса. Также можно нажать иконку в левом нижнем углу окна Excel:

Рис. 16.2. Кнопка Начать запись макроса

Как только вы нажмете эту кнопку, Excel начнет записывать каждый щелчок листа, каждое нажатие клавиши и… каждую ошибку, которую вы делаете. Выполните в точности следующие действия. Дайте макрос Refresh и сохраните его в текущей книге. Пройдите по меню Данные –> Запросы и подключения –> В правой части окна Excel в области Запросы и подключения кликните правой кнопкой мыши на запросе Transactions –> Обновить. Щелкните правой кнопкой мыши любую ячейку сводную таблицу –> Обновить. Повторно кликните кнопку, как на рис. 16.2. Запись макроса будет остановлена. Проверьте код. Пройдите по меню Вид –> Макросы:

Рис. 16.3. Макросы

Нажмите Изменить. Откроется окно редактора VBA

Рис. 16.4. Код VBA; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Добавьте кнопку для обновления макроса. Перейдите на вкладку Разработчик –> Вставить –> Кнопку:

Рис. 16.5. Вставить элемент управления Кнопку

Выберите место на листе, и удерживая левую кнопку мыши, нарисуйте прямоугольник для Кнопки. Отпустите левую кнопку мыши. Появится окно с предложением поставить в соответствие Кнопке макрос. Выберите Refresh. Нажмите Ok. Щелкните правой кнопкой мыши по Кнопке –> Изменить текст. Введите Обновить. Щелкните любую ячейку на листе, чтобы выйти из режима редактирования названия кнопки.

Рис. 16.6. Кнопка, подключенная к макросу

Обновление нескольких запросов

Теперь можно добавить иные запросы в макрос. Пройдите по меню Разработчик –> Макросы –> Refresh –> Изменить. На данный момент код имеет вид (см. также рис. 16.4):

Sub Refresh()

‘

‘ Refresh Макрос

‘

ActiveWorkbook.Connections(«Power Query — Transactions»).Refresh

Range(«J6»).Select

ActiveSheet.PivotTables(«Сводная таблица1»).PivotCache.Refresh

End Sub

Первые четыре строки после Sub Refresh() – это просто комментарии. Сейчас в них нет смысла, так что можно их удалить. Строка, начинающаяся с ActiveWorkbook, обновляет запрос. В следующей строке выбирается ячейка на активном листе. Предпоследняя строка обновляет сводную таблицу на активном листе.

Вы можете внести некоторые изменения в этот макрос, чтобы не только контролировать порядок обновления всех соединений, но и сделать код немного более надежным. (Сейчас код потерпел бы неудачу, если бы кто-то попытался запустить его из другого рабочего листа, так как на нем не было бы сводной таблицы.) Вот как должен выглядеть усовершенствованный код:

Sub Refresh()

ActiveWorkbook.Connections(«Power Query — Jan2008»).Refresh

ActiveWorkbook.Connections(«Power Query — Feb2008»).Refresh

ActiveWorkbook.Connections(«Power Query — Mar2008»).Refresh

ActiveWorkbook.Connections(«Power Query — Transactions»).Refresh

Worksheets(«Transactions»).PivotTables(«PivotTable1»). _

PivotCache.Refresh

End Sub

Символы пробела и подчеркивания используются для обозначения разрыва строки в коде VBA. Код будет работать, если PivotCache.Refresh останется в той же строке (без переноса). Вот только длинные строки плохо размещать на картинках)) Использование Worksheets("Transactions") вместо ActiveSheet более универсально.

Имена подключений должны совпадать с именами, которые можно увидеть в диалоговом окне Существующие подключения:

Рис. 16.7. Существующие подключения

Поскольку теперь книга включает макрос, вы не можете сохранить ее в формате XLSX. Вместо этого сохранить книгу в формате XLSM. При открытии книги пользователи получат предупреждение системы безопасности.

Обновление всех запросов

Чтобы обновить все запросы Power Query, необходимо использовать несколько иной код. Следующий макрос будет просматривать все подключения в книге и обновлять те из них, что созданы Power Query (и игнорировать остальные):

Public Sub UpdatePowerQueriesOnly()

Dim lTest As Long, cn As WorkbookConnection

On Error Resume Next

For Each cn In ThisWorkbook.Connections

lTest = InStr(1, cn.OLEDBConnection.Connection, _

«Provider=Microsoft.Mashup.OleDb.1»)

If Err.Number <> 0 Then

Err.Clear

Exit For

End If

If lTest > 0 Then cn.Refresh

Next cn

End Sub

Этот макрос может быть сохранен в той же книге, что и созданный в предыдущем разделе, или он может заменить предыдущий код (в этом случае вам нужно будет повторно связать кнопку с новым макросом).

Имейте в виду, что этот код не обязательно обновит запросы в том порядке, в котором они должны быть обновлены, так как Excel обновляет запросы в алфавитном порядке. Если порядок обновления важен, пройдите по меню в Данные –> Существующие подключения. Откроется окно Существующие подключения (см. рис. 16.7). Кликните на подключении правой кнопкой мыши –> Изменить свойства подключения. Откроется окно Свойства запроса. Последовательно измените имена запросов, например, на такие 01-Jan2008, 02-Feb2008, 03-Mar2008, 99-Transactions. Теперь запросы будут обновляться в нужном порядке.

Источник

Нужна дополнительная помощь?

Поиск по меткам

Как в excel обновить связи

Управление обновлением внешних ссылок (связей)

Дополнительные сведения о внешних ссылках

Управление связями с другими книгами

Ручное обновление всех (или никаких) связей в книге

Частичное обновление связей с другими книгами вручную

Управление выдачей запроса на обновление всех связей

Отключение выдачи запроса на обновление связей данной книги и обновление связей вручную

Настройка обновления связей по запросу при открытии файла

Если никакие связи не создавались

Разрыв связей с источником

Замена одной формулы ее вычисляемым значением

Если известно, что подключение к исходному файлу отсутствует

Если требуется работать с имеющимися данными, а не заменять их новыми

Если обновление в последний раз заняло слишком много времени

Если эта книга создана другим пользователем и вы не знаете, почему появился этот запрос

Если вы каждый раз одинаково отвечаете на этот запрос и хотите, чтобы он больше не отображался

Если известно, что исходный файл доступен для подключения к нему

Если используется параметрический запрос

Устранение неполадок в связях между таблицами

Сообщение. Связи не были обнаружены

В сводную таблицу добавлены несвязанные поля, однако сообщение не выдается

Отсутствует допустимая связь между таблицами

При автоматическом обнаружении созданы неверные связи

�������� ��������������� ���������� ������ � Excel 2010

Автоматическое обновление ссылок в Excel 2007 [ТЕМА ЗАКРЫТА — двойное нарушение — благодарим stuf]

Регулировка обновлений связей с внешними данными (Формулы)

Планирование обновлений без использования кода

Автоматизация обновления запросов с помощью VBA

Обновление нескольких запросов

Обновление всех запросов

А вот еще интересные статьи:

�� Excel 2010