Автоматическое обновление запросов в excel
На этом шаге мы рассмотрим как обрабатываются данные, возвращаемые из MS Query и записанные в рабочий лист.
Данные, возвращаемые после выполнения запроса, записываются либо в рабочий лист, либо в сводную таблицу. Данные, сохраняемые в рабочем листе, записываются в специально поименованный диапазон, так называемый диапазон внешних данных ( Excel присваивает имя этому диапазону).
В диалоговом окне Свойства внешнего диапазона (рис. 1) можно корректировать различные свойства диапазона внешних данных.
Чтобы вызвать это диалоговое окно, указатель ячейки должен находиться в пределах диапазона внешних данных. Доступ к диалоговому окну можно получить, воспользовавшись одним из трех методов:
- Щелкните правой кнопкой мыши и из контекстного меню выберите команду Свойства диапазона данных .
- Выберите команду Данные | Внешние данные | Свойства диапазона данных .
- Щелкните на кнопке Свойства диапазона данных на панели инструментов Внешние данные (это окно появляется автоматически, когда Вы выполняете запрос).
В диалоговом окне Свойства внешнего диапазона имеются следующие опции:
- Имя. Имя диапазона внешних данных, создаваемое Excel по умолчанию. Это имя можно изменить или оставить.
- Определение запроса. Если флажок Сохранить определение запроса установлен, то определение запроса сохраняется вместе с диапазоном внешних данных. Если для базы данных требуется пароль, можно также задать пароль, и тогда не нужно будет его вводить при обновлении запроса.
- Обновление экрана. Определяет, как и когда обновляются данные.
- Формат и разметка данных. Определяет, как будет размещен диапазон внешних данных.
После выполнения запроса можно сохранить файл рабочей книги, а позже открыть его. В рабочей книге будут содержаться данные, которые были первоначально получены из внешней базы данных. Однако внешняя база данных за это время может измениться.
Если установлен флажок опции Сохранить определение запроса в диалоговом окне Свойства внешнего диапазона , то определение запроса сохраняется вместе с рабочей книгой. Для обновления запроса надо поместить курсор где-нибудь в таблице внешних данных, а затем выполнить одно из следующих действий:
- Щелкнуть правой кнопкой мыши и выберать из контекстного меню команду Обновить данные .
- Выберать команду Данные | Обновить данные .
- Щелкнуть на кнопке Обновить данные панели инструментов Внешние данные .
Excel запустит Query и использует первоначальный запрос для получения текущих данных из внешней базы данных.
Отдельная рабочая книга, если это необходимо, может содержать много диапазонов внешних данных. Каждому диапазону присваивается уникальное имя, поэтому с каждым из них можно работать независимо от других. Excel автоматически отслеживает запрос, который используется для получения каждого из диапазонов внешних данных.
После выполнения запроса иногда требуется скопировать или переместить диапазон внешних данных. При этом можно использовать обычные приемы копирования, вырезания и вставки. Однако необходимо убедиться, что копируется или вырезается целый диапазон внешних данных, иначе соответствующий запрос не будет скопирован, а скопированные данные не будут обновляться.
Если данные, возвращаемые по запросу, больше не нужны, их можно удалить. Для этого следует выделить весь диапазон внешних данных и выберать команду Правка | Удалить .
Если Вы нажмете клавишу Delete , то содержимое ячеек будет удалено, но соответствующий запрос останется. А это означает, что можно обновить запрос, и значения удаленных ячеек появятся снова.
Если полученные результаты запроса неудовлетворительные, то запрос можно модифицировать. Для этого следует поместить курсор где-нибудь в пределах таблицы внешних данных, а затем использовать один из следующих способов обновления запроса:
Excel запустит Query , после чего можно изменить первоначальный запрос. По окончании корректировки выберите команду Файл | Вернуть данные в Microsoft Excel . Excel вновь будет активизирован, выполнится модифицированный запрос, а диапазон внешних данных обновится.
На следующем шаге мы рассмотрим создание запроса в MS Query без использования Мастера запросов .
Для того, чтобы получить доступ к диалоговому окну форм, просто выберите таблицу данных на рабочем листе и нажмите на добавленную кнопку из панели быстрого доступа. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Совет: При отключении флажка, клавиши со стрелками можно использовать для управления перемещением указателя. Например, если вы вводите данные в строке, нажмите клавишу со стрелкой вправо, а не Enter, чтобы перейти к следующей ячейке в той же строке.
Как перенести данные из Google Таблиц в Microsoft Excel
Будучи универсальным универсальным набором инструментов для восстановления, Stellar File Repair предназначен для решения всех проблем с повреждением файлов, связанных с офисом MS. Будь то файл Word, Excel, PowerPoint или даже zip-файлы, этот инструментарий устраняет все соответствующие проблемы и хорошо работает с Office 2000, 2002, 2003, 2007, 2010, 2013 и 2016.
Часть 2. Найдите неработающие ссылки в Excel с помощью функции «Найти и заменить»
Шаг 1: Нажмите Ctrl + F, чтобы открыть диалоговое окно «Найти и заменить».
Шаг 2: В диалоговом окне выберите Параметры.
Шаг 3: В поле Найти теперь введите соответствующее расширение файла, связанное с
Шаг 5: В поле Искать в выберите вариант Формулы.
Следуя этим простым шагам, можно найти неработающую ссылку на Excel. Следующим шагом будет замена одной функциональной ссылкой. Это делается при изменении местоположения исходного файла. Как только вы найдете неработающую ссылку, выполнив указанные выше действия, затем вы можете выбрать вариант замены и изменить расширение файла на новое (функциональное).
И да! Все сделано с поиском и исправлением битой ссылки Excel.
Обновление полученной таблицы происходит кликом правой кнопки мыши по названию нужного запроса в правой части листа список Запросы книги. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Обновление полученной таблицы происходит кликом правой кнопки мыши по названию нужного запроса в правой части листа (список «Запросы книги»). После клика правой кнопкой мыши в выпадающем контекстном меню следует нажать на пункт «Обновить»
Недостатки умных таблиц Excel
После того, как Таблицы данных созданы на рабочем листе, формы обеспечивают возможность ввода данных в этих таблицах, используя специальное диалоговое окно. Для добавления кнопки формы на панели быстрого доступа, выполните следующие действия:
Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.
По мере того, как вы создаете все больше и больше решений на основе Power Query, и начинаете понимать, сколько времени это экономит, вы захотите еще большей автоматизации. Да, вы можете просто щелкнуть правой кнопкой мыши таблицу, которая была импортирована с помощью Power Query, но даже это ощущается как… ручная операции. Нельзя ли запланировать обновление или, возможно, контролировать порядок обновления?
На самом деле вы можете использовать несколько различных методов автоматизации обновления решений Power Query:
- Обновление при открытии книги
- Обновление каждые Х минут
- Обновить соединение по требованию с помощью VBA
- Обновить все соединения по требованию с помощью VBA
- Использовать расписание обновлений в сторонней надстройке
Рис. 16.1. Настройка параметров подключения
Планирование обновлений без использования кода
Первые два метода работают через пользовательский интерфейс и не требуют кода 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):
Когда дело доходит до работы с электронными таблицами, нельзя отрицать, что самым популярным инструментом является Microsoft Excel, которым пользуются миллионы пользователей по всему миру. В рамках его широкого спектра функций у нас будет возможность импортировать данные с веб-сайта в нашу электронную таблицу и обновлять ее позже.
Excel предлагает возможность агрегировать данные для углубленного просмотра, расчета и анализа. Возможно, ручной способ — добавить его вручную, но это очень непрактично. Поэтому мы можем импортировать данные непосредственно с веб-сайта с помощью инструмента Power Query.
Что такое Power Query в Excel
Мы говорим об инструменте, включенном в Microsoft Excel и помогающем нам упростить процесс импорт данных из разных источников в электронную таблицу в наиболее удобном формате, а затем очищать, преобразовывать и переделывать эти данные по мере необходимости. . Поскольку он принадлежит офисному пакету Office, это бесплатное дополнение, которое периодически обновляется.
Его главное преимущество и то, что делает его чрезвычайно полезным инструментом, заключается в том, что он выполняет функцию ETL, то есть извлечение, преобразование и загрузку данных. Его пользовательский интерфейс интуитивно понятен и хорошо разработан, что упрощает его изучение. Для этого нам не нужно знать или использовать какой-либо код, поэтому его использование намного проще по сравнению с другими инструментами, такими как формулы или VBA.
- Возможность подключения к различным источникам данных, таким как текстовые файлы, файлы Excel, веб-сайты, базы данных и другие.
- Преобразует полученные данные на основе предпосылок отчета.
- Это позволяет вам сохранять данные в таблице Excel или просто подключаться к данным, чтобы загрузить их позже.
Кроме того, если исходные данные со временем меняются, можно обновить полученные результаты и найти их в нашей электронной таблице простым щелчком мыши, поэтому это очень полезно, если мы работаем с данными, которые могут часто меняться.
Аспекты, которые следует учитывать
- Версия, которую мы используем Microsoft Excel: чтобы выполнить важность данных для нашей электронной таблицы, мы должны использовать функцию Power Query. Таким образом, мы можем установить соединение, преобразовать, объединить и загрузить данные. Для этого необходимо использовать совместимую версию Excel, такую как Microsoft 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013 и Excel 2010.
- Формат данных: поскольку мы собираемся импортировать данные с веб-сайта в Excel, для того, чтобы это было сделано удовлетворительно, они должны быть встроены в таблицу HTML. В противном случае Power Query не сможет их идентифицировать, поэтому мы не сможем их импортировать.
Как только мы убедимся, что сможем выполнить оба требования, мы можем начать процесс импорта.
Шаги для импорта данных в Excel
Первое, что мы должны сделать, это открыть Microsoft Excel и выбрать существующий или новый лист, куда мы собираемся импортировать данные. Позже на ленте щелкните вкладку «Данные». Далее переходим в раздел данных «Получить и преобразовать», который находится слева. Отсюда мы нажимаем на "Получить данные" раздел, в котором будет отображаться меню, где мы выбираем «Из других источников» и «Из Интернета».
Это откроет новое всплывающее окно под названием «Из Интернета», где мы должны вставьте URL-адрес веб-страницы который мы хотим импортировать в текстовое поле «URL-адрес». Один раз нажимаем «ОК». В некоторых случаях Excel попросит нас подтвердить данные, которые мы хотим загрузить на веб-сайт, поэтому мы должны нажать «Подключиться» для подтверждения.
Как только Excel закончит загрузку данных, появится новое окно с именем «Навигатор» появится, что покажет нам компоненты веб-страницы в левой панели. Теперь мы должны выбрать любую из пронумерованных таблиц в «Навигаторе», чтобы проверить правильность импорта данных.
Если мы хотим убедиться, что находимся на правильной странице, мы можем щелкнуть значок «Веб-просмотр» tab, чтобы получить традиционный предварительный просмотр страницы в формате HTML.
Как правило, перечисленные компоненты не представлены с очевидными именами, которые позволяют нам определить, к какому элементу или компоненту веб-страницы он относится, поэтому нам придется щелкнуть по каждому из них, чтобы получить предварительный просмотр каждого элемента в правую панель, таким образом имея возможность узнать, какая из них принадлежит нужной таблице. Если мы согласны с тем, как данные будут импортированы из Интернета, мы можем нажать «Загрузить в» для отправки данных непосредственно в Excel.
После этого появится окно с именем «Импорт данных» появится, который предложит нам выбрать, как мы хотим видеть данные в книге, либо в формате таблицы, отчета сводной таблицы, сводной диаграммы или Создать только соединение. Мы также можем указать, где мы хотим разместить данные, будь то в существующей электронной таблице или в новой. После выбора нажмите «ОК».
В результате мы увидим таблицу в нашей электронной таблице. Это связано с «Запросы и связи» панель справа. В нем перечислены все существующие запросы в этом файле. При наведении на запрос открывается информационное окно. В нем мы увидим предварительный просмотр данных, количество импортированных столбцов, дату и время обновления, способ загрузки данных и место их происхождения.
Обновить веб-данные
Как мы уже знаем, когда мы импортируем данные с веб-страницы в Excel, они автоматически обновляются. Таким же образом можно отредактировать наши предпочтения, указав, как мы хотим, чтобы они выполнялись.
Для этого на рабочем листе, подключенном к Интернету, мы нажимаем на таблицу, а на ленте мы нажимаем вкладку «Дизайн таблицы». Здесь, в разделе «Внешние данные таблицы», мы нажимаем раскрывающееся меню «Обновить». Здесь мы найдем три варианта: Обновить, Обновить все и «Свойства соединения» . Мы нажмем на последний, чтобы установить наши предпочтения.
Это откроет новое окно под названием «Свойства запроса». В котором мы нажмем на вкладку «Использование», чтобы установить наши настройки обновления.
Здесь мы обнаружим установленный флажок «Включить фоновое обновление», что гарантирует постоянное обновление содержимого. Мы также можем установить флажок «Обновлять каждые», чтобы Excel обновлял данные только в течение определенного периода. Наконец, опция «Обновлять при открытии файла» позволяет обновлять данные только при запуске приложения.
После завершения нажмите «Принять», чтобы сохранить наши настройки обновления.
Когда дело доходит до работы с электронными таблицами, Microsoft Excel - это мировой справочник. Когда мы работаем с несколькими книгами в одной организации, нас может заинтересовать импорт данных из нескольких внешних источников данных, таких как другая книга Excel, база данных, файлы CSV и т. Д. Хотя связывание с другими книгами является очень распространенной задачей, мы можем иногда возникают проблемы, из-за которых Excel не может обновить некоторые ссылки.
Какие ссылки и причины вызывают проблему
В Excel есть инструмент под названием Ссылки или внешние ссылки это позволит нам скопировать все данные с одного листа и вставить их в другой, как в одной книге, так и в разных книгах. Эта ссылка означает, что при изменении данных исходного листа это изменение также влияет и выполняется на целевом листе. Важно уточнить, что эта функция отличается от функции копирования и вставки или гиперссылок, поскольку таким образом не выполняется обновленный процесс связывания. Таким образом, каждый раз, когда исходное содержимое ссылки обновляется, оно также будет выполняться в книге, в которую мы добавили ссылку.
Далее мы увидим различные действия, которые мы можем предпринять, чтобы попытаться решить проблемы при обновлении ссылок в Microsoft Excel.
Найдите внешние ссылки, чтобы решить проблему
В нашей книге все ячейки, содержащие ссылки или внешние ссылки, имеют имя книги, на которую они ссылаются, поэтому ячейка будет содержать расширение книги. Это то, что мы можем использовать для поиска ячеек и их восстановления, чтобы решить проблему с обновлением.
Контроль обновления ссылок
Чтобы обновить ссылки, на вкладке «Данные» мы должны нажать кнопку «Обновить все».
Разблокировать защищенную таблицу
Иногда мы можем обнаружить, что проблема с обновлением ссылок в Excel может возникнуть, когда мы защищаем электронную таблицу. Если мы установили пароль, который защищает наш лист, мы можем попытаться удалить его, чтобы увидеть, решит ли он проблему.
Для этого достаточно будет щелкнуть правой кнопкой на вкладке «Лист» в левой нижней части. Это приведет к появлению контекстного меню, в котором мы должны выбрать опцию «Снять защиту листа «. Если наша электронная таблица защищена паролем, мы должны ввести правильный пароль и нажать «Применить». Если он не заблокирован каким-либо паролем, нажатие на «Снять защиту с листа» автоматически разблокирует защищенный лист.
В качестве альтернативы мы также можем разблокировать защищенную электронную таблицу на вкладке «Обзор», которую мы находим в верхней правой части Excel. Отсюда в разделе «Защитить» нажмите кнопку «Снять защиту с листа».
Восстановить битые ссылки
Перед тем, как сделать этот вариант, удобно, чтобы сохраняем резервную копию нашей книги Excel. Позже мы открываем книгу Excel, которая показывает нам ошибку со ссылками, и щелкаем вкладку «Данные». Здесь мы увидим, что команда «Редактировать ссылки» недоступна, если наша книга не содержит ссылок.
Отключить DDE (динамический обмен данными)
Протокол динамического обмена данными DDE был разработан Office, чтобы иметь возможность обмениваться данными между различными приложениями, которые его составляют, например, между PowerPoint и Excel, Word и Excel и т. д. Когда мы пытаемся открыть документ с помощью этого протокола, появится окно с предупреждением, указывающее, мы хотим обновить данные связанных файлов.
Если мы хотим отключить его, мы можем щелкнуть по вкладке «Файл» и «Параметры». В новом окне в разделе «Дополнительно» мы смотрим в раздел «Общие» и устанавливаем флажок «Пропустить другие приложения, использующие динамический обмен данными (DDE)» в Excel.
У меня есть запрос ( Query1 ), загруженный в Excel с использованием соединения из Teradata . Обычно, когда я хочу обновить запрос, я должен щелкнуть его правой кнопкой мыши и выбрать «Изменить».
Оказавшись внутри Power Query Editor , я могу открыть Source и отредактировать SQL-скрипт внутри, чтобы обновить запрос.
Вопрос в том, как мне отредактировать этот сценарий SQL , используя VBA ?
При попытке использовать Macro Recorder , я могу только обновить запрос, используя
Попытка изменить CommandText также не удалась. Вот свойства запроса.
2 ответа
Сначала мы получаем формулу запроса, используя:
Причина, по которой мы должны это сделать, заключается в том, что строка формулы содержит некоторую дополнительную информацию о соединении с нашей базой данных, а не только сам SQL-запрос. Это может выглядеть примерно так:
Чтобы обновить запрос, мы устанавливаем ThisWorkbook.Queries.Item("Query1").Formula в новую строку формулы. Также будьте осторожны при обработке двойных кавычек внутри VBA. Вот скрипт, который я использовал:
Во-первых, замените ActiveWorkbook на ThisWorkbook , поскольку вы хотите работать с этой конкретной книгой (той, в которой хранится ваш код VBA), а не с какой-либо книгой, которая оказывается в настоящее время активен .
.Refresh - это метод соединения OLEDB, которое вы хотите изменить: у вас уже есть ссылка на соединение, для которого вы хотите изменить текст команды - все, что вам нужно, это вызвать метод, другой чем .Refresh против этого, то есть попробуй набрать эту точку и посмотреть, что этот объект может предложить.
Вы можете получить текст команды для этого OLEDBConnection объекта через его свойство CommandText - попробуйте ввести его в панель быстрого доступа (Ctrl + G) и посмотрим, если вы получите тот же результат:
Читайте также: