Как защитить запрос Power Query от изменений

Ситуация довольно распространенная с появлением Power Query: создали запрос, в котором выполняются "хитрые" операции и книга с запросом распространяется между пользователями(или высылается заказчику, клиенту и т.п.). Но есть проблема: нам необходимо, чтобы пользователи не могли зайти в запрос и посмотреть действия и тем более изменить там что-либо. Те, кто знаком с VBA знают, что макросы можно "закрыть" паролем(Как защитить проект VBA паролем), но для запросов такой возможности нет. Как же быть? Неужели разработчики Microsoft не предусмотрели возможности защитить запросы? Предусмотрели и все гораздо проще, чем может показаться.
Идем на вкладку Рецензирование(Review) -Защита книги(Protect Workbook). При необходимости устанавливаем пароль и жмем Ок:

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

• защита накладывается на все запросы в книге. Защитить один конкретный запрос не получится
• такая защита защищает не только запросы, но и структуру книги(без этого защита не устанавливается). Это означает, что нельзя будет добавлять, удалять, перемещать, скрывать/отображать и переименовывать листы. Но с другой стороны вполне логично: ведь результаты запросов могут быть выгружены на листы книги и удаление такого листа является вмешательством в параметры запроса(параметры загрузки результата)
• как ни прискорбно, но даже в этом случае можно будет добраться до текста запроса: достаточно скопировать нужный запрос в другую книгу: отображаем панель запросов(Данные(Data) -группа Скачать и преобразовать(Get & Transform) -Показать запросы (или Запросы и подключения)) -правая кнопка мыши на нужном запросе -Копировать(Copy). Далее так же отображаем в другой книге(не защищенной) панель запросов и в этой панели правая кнопка мыши -Вставить(Paste). Теперь запрос целиком в новой книге и его можно посмотреть как любой другой.

Будем надеяться, что в будущем появится возможность защищать если не каждый запрос в отдельности, то хотя бы запросы отдельно от всей книги. Хотя, с озвученными выше нюансами это будет сложно сделать. Ну и конечно, хотелось бы, чтобы копирование запросов из защищенной книги все же было бы невозможно.
 
А если пользователю надо изменять источник данных?
Например, идет подключение к папке с файлами и дальнейшая обработка. Папка у каждого пользователя своя и обычно мы просто говорили пользователю зайти в параметры запроса и изменить там параметры источника данных. В данном случае я рекомендую воспользоваться советом из этой статьи: Относительный путь к данным PowerQuery. Если путь никак не зависит от расположения книги с запросом, то можно просто создать таблицу параметров без всяких формул, а пользователь должен будет просто записывать туда руками путь к нужной папке - принцип работы запроса от этого не поменяется. Он будет брать путь из ячейки и изменять сам запрос не будет необходимости.

Sub ProtectWorkbook()
    ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="123"
    'а строка ниже - снятие защиты
    'ActiveWorkbook.Unprotect Password:="123"
End Sub

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

    Видеоуроки

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