Удалённое обновление макросов Excel или cказ о том, как мы были готовы к тотальной удалёнке

Intro

Привет! Меня зовут Ярослав Тихонов, и я Business Process Optimization Lead в Havas Media.

Достаточно часто я слышу мнение, что Excel – это прошлый век, ведь даже начинающие менеджеры работают с PowerBI, Python и R. Однако, как показывает практика, для великого множества задач «попроще» нет смысла заново изобретать велосипед. Когда почти у каждого сотрудника есть доступ к мультитулу вроде Excel – главное уметь его правильно применить.

Если с базовым инструментарием не получается найти простое/относительно простое решение, тогда – да, в ход идёт тяжелая артиллерия. У нас в Havas Media в таких случаях активно используется и PowerBI, и эконометрика на R, и модели прогноза рейтингов на Python, и собственная платформа Aizek, но об этом коллеги расскажут в других статьях.

В этой колонке я расскажу именно про Excel/PowerBI и о том, как с его помощью можно решить некоторые задачи. Ну и про грабли – куда без них :)

Disclaimer: не ставлю целью обучить читателя всем премудростям работы с Excel – для этого есть специальные курсы, которые можно проходить в течение месяца + в режиме рабочей недели. Эта статья — скорее размышление про грабли и полезные советы/рецепты от фанатика фанатикам.

Постановка задачи

Дело было в доковидную эру. Многие специалисты в нашем агентстве используют макросы в специальной надстройке. Устанавливать их ручками или каждый раз объяснять новым сотрудникам, как устанавливать надстройку – занятие утомительное. Что уж говорить про большую проблему с обновлениями (особенно когда появлялся hotfix, который нужно раскатать на всех ещё вчера). Хотелось сделать взмах палочкой и чтобы оно… само как-то сделалось :)

Муки выбора

Размышления по способу хранения и запуску макросов по поставленной задаче были такими:

· Хранить макросы в «мега» файле Excel. В теории можно все макросы общего пользования запихнуть в один файл и просить менеджеров открывать его, когда нужно запустить макросы. На практике – и не взлетит, и неудобно. Это и проблемы с открытием файла на чтение, и требование держать книгу всегда открытой, и сложности с определением того, актуальный ли файл открыт и т.д.

· Хранить макросы в персональной книге макросов. Хороший, рабочий вариант, но из минусов – сложно с обновлением. Просить менеджеров скопировать и вставить текст определенного макроса к себе в персональную книгу – так себе идея…

· Хранить в надстройке и обновлять саму надстройку. Самый оптимальный вариант, который решает базовые вопросы и, как оказалось, сможет попутно решить много скрытых проблем в будущем (помним, что решение создавал до COVID-19)

Решение 0.5

Решили делать надстройку, а потом её обновлять.

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

У большинства путь хранения надстройки находится по такому адресу:

C:\Users\{UserName}\AppData\Roaming\Microsoft\AddIns

Его можно проверить через любой открытый файл Excel, где выбираете тип файла надстройка (*.xlam или *.xla) и вас автоматом пробросит в директорию хранения надстройки

Получить имя пользователя, кто будет запускать макрос (и чьё имя нужно будет подменить в пути), можно с помощью функции Environ(«Username»). И не забудьте для обновления сначала отключить надстройку, а потом только копируйте с заменой.

Самое «сложное» в этом решении – аккуратно учесть, куда и что записывать. Алгоритм скрипта следующий:

· Отключаем надстройку, если она подключена

· Копируем файл надстройки из сетевой папки

· Вставляем файл надстройки в локальную папку хранения надстроек

· Включаем надстройку

Скрипт помещаем в персональную книгу макросов. Остаётся только вынести кнопочку для запуска обновления на панель быстрого доступа и будет счастье… Или нет?

Решение 1.0

Кстати о кнопочках. Макросы удобнее всего запускать через кнопки на панели.

Тут возникает множество вопросов. А если макросов много, то их нужно вручную у каждого настраивать? А если появился новый макрос – это каждому нужно объяснить как вынести новую кнопку? А представьте, что пользователи будут добавлять свои иконки?

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

Так не пойдет, макросов много, а я один копаем глубже.

Кнопки на панели быстрого доступа хранятся в специальном файле Excel.officeUI, который хранится по адресу (как и ранее – в большинстве случаев)

C:\Users\{UserName}\AppData\Local\Microsoft\Office\

Он является простым xml файлом, который подтягивается в момент открытия окна Excel (на самом деле хитрее, но для упрощения будем считать, что так).

Прописываем все кнопки и ссылки на макросы из надстройки, копируем файл «Excel.officeUI» из локальной папки в сетевую. Подменяем в файле свой UserName на заглушку вроде {UserName}.

Алгоритм макроса для обновления панели с кнопками

· Копируем файл из сетевой папки на Рабочий стол

· Подменяем в файле заглушку на UserName человека, который запускает макрос

· Помещаем модифицированный файл в локальную папку

Из граблей — если класть файл с настройками панели новому сотруднику, которому только настроили компьютер, то он автоматически (чертова магия) удаляется. Обойти просто – через добавление любой кнопки на панель быстрого доступа и перезапуска. По всей видимости, таким образом срабатывает разрешение на хранение файла с настройками по аналогии с персональной книгой макросов.

Итого: приходит новый сотрудник

· Просим добавить любую кнопку на панель быстрого доступа

· Просим перезапустить Excel

· Даём ссылку на файл с одной большой кнопкой, по нажатию на которую ему устанавливается и персональная книга макросов, и надстройки, и даже кнопочки для запуска.

Решение 1.2

Приходит COVID-19 и массовая удалёнка. Для всех и каждого – включая новых сотрудников. Не знаю по другим компаниям, но у нас продолжался набор сотрудников, даже в период карантина. В ход шли разные ухищрения, одно из которых – терминальный доступ.

И оказалось, что не всегда и не у всех логический диск с программами это C:\, и не у всех папка с профилями это Users.

Подстраиваемся – редактируем скрипт, который обновляет надстройку и обновляет кнопочки на панели быстрого доступа. Вместо Environ («Username») используем Environ («Appdata») и нехитрые текстовые функции, чтобы вычленить и UserName, и путь к настроечным файлам.

Ах, да. Проверяем надстройку на предмет обращения к рабочему столу – скорее всего путь тоже поменялся. Заодно проверяем пути к сетевым дискам.

Ну теперь точно – счастье есть. Установка макросов новому сотруднику происходит меньше, чем за минуту и, что немаловажно – он может это сделать самостоятельно.

Вместо итога

Решение не идеально, но оно рабочее и относительно простое. Можно было бы использовать более тяжелые инструменты для этого, но зачем – когда есть такой инструмент в коробке)

Что можно сделать лучше:

· Записать видеоинструкцию о том, куда нажимать для установки

· Использовать Telegram канал / Teams для оповещения об исправлениях в макросах или новых версиях

· Копировать личную книгу макросов с помощью скрипта vb в момент настройки профиля

· Написать простенький скрипт на Autoait (или любого другого RPA решения), который будет проделывать действия за пользователя в момент подготовки профиля

При вычитке статьи коллегой по цеху – спросил, почему нельзя обновлять надстройку / кнопки по logon скрипту. Ответ прост – потому что я работал в поддержке и понимаю, что убедить людей среди рабочего дня перезагрузиться / перелогиниться – сложно, а попросить перед запуском очередного макроса нажать на еще одну кнопку – можно.

Спасибо что прочитали, надеюсь было полезно.

Stay tuned!