VBA и Python для автоматизации Excel и MS Office

    Поводом для заметки послужила статья на Хабре, в которой автор описывал, как он решал на Python задачу сбора и анализа метаданных из файлов Excel.


    Эта заметка более подробно раскрывает всем известный тезис: Под конкретную задачу надо выбирать наиболее подходящий инструмент применимо к офисной автоматизации.


    VBA и Python


    VBA (Visual Basic for Applications), де-факто, самый популярный язык для автоматизации Microsoft Office. Доступен из коробки, помимо Excel, работает в PowerPoint, Outlook, Access, Project и других приложениях.


    Если задать вопрос: «Какой язык программирования выбрать первым», то где-то в 90% всех случаев будет предложен Python. На практике здесь может быть и любой другой язык, но, исходя из популярности языка и своего опыта, буду сравнивать с ним.


    В общем виде можно описать ситуацию через подобный график:



    Детального сравнения не будет, рассмотрим основные killer-фичи, в ситуации, когда junior-программист/офисный сотрудник хочет автоматизировать что-либо, связанное с MS Office, и у него есть возможность выбора между языками.


    Если в силу разных причин возможности выбора нет, то и сравнивать нечего.


    В пользу VBA



    • Отличная работа с объектной моделью Excel и других приложений MS Office. Написание кода на VBA для большинства внутренних операций тривиально. У Python, в сравнении с VBA, поддержка объектной модели Office очень слабая.
    • Поддержка разных форматов MS Office. Самая большая проблема для внешних языков — это работа с разными форматами файлов MS Office. Например, xls, xlsx, xlsm файлы могут требовать разных библиотек, так как каждая хорошо работает только со своим форматом файла. Для VBA — это все "файл Excel", работа с которыми в целом одинаково хороша.
    • Работа с MS Exchange. Если необходимо обеспечить работу с корпоративной почтой/календарем на Exchange, то далеко не каждом языке есть нормальная библиотека для работы протоколом Exchange. В VBA это решается относительно просто через использование в макросе объектной модели MS Outlook.
    • Легкая установка и дистрибуция. К уже установленному офису не надо ничего устанавливать. Чтобы коллега мог воспользоваться программой, достаточно передать ему файл с макросом. Легко сделать надстройку, которая позволит "установить" модель макроса в фон офиса.
    • Интерактивность внутри приложений MS Office. Внутри офисных программ можно как просто поставить кнопки запуска макросов, так и (чуть сложнее) сделать целый отдельный UI. Сюда же относится написание своих формул в Excel и то, что макросы могут воздействовать на объекты внутри документов Office в реальном времени.
    • Запись макросов. Удобный инструмент, который позволяет записать действия человека в готовый код, для последующего редактирования использования.

    В пользу Python (и других внешних языков программирования)



    • Приятный синтаксис и синтаксический сахар. Если коротко, то VBA не отличается выразительностью и удобством. Это вопрос личного вкуса, но для меня Python намного удобнее.
    • Богатая экосистема библиотек. Огромный выбор готовых библиотек для работы с внешним миром. Пытаться сделать на VBA программу, взаимодействующую с каким-нибудь внешним API, та еще боль. Занимательно, что как раз для работы с файлами Office библиотеки того же Python — откровенно "на троечку".
    • Хорошие средства разработки. Можно выбрать из огромного выбор программ, которые облегчают процесс разработки. Стандартный редактор VBA из Office предлагает очень бедный функционал и, в сравнении с альтернативами из мира Python, откровенно неудобен. Писать код VBA в внешнем редакторе, а потом копировать внутрь офиса для отладки — тоже неудобно.
    • Скорость работы. Не проверял скорость однопоточной работы, но, предположу, что в случае однопоточной работы преимущество будет за Python. В любом случае, достаточно тривиально организуется многопоточная обработка данных/файлов, что позволяет говорить в большей достижимой скорости.

    Кейсы



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


    • Задача: Программа для проверки всех файлов Excel в директории на предмет наличия скрытых листов
      • Мой выбор: VBA. Причины: простота работы с разными форматами файлов Excel и отсутствие внешних взаимодействий.
    • Задача: Сервис, который должен был позволить пользователю с мобильного устройства конвертировать файлы PowerPoint в PDF для просмотра
      • Сервис был реализован как почтовый бот, на адрес которого пользователь может переслать файлы Office, а в ответ пользователю по почте приходит ответ с файлами PDF.
      • Мой выбор: Логика VBA + Python для мониторинга
        • Во-первых, внутренние функции гарантированно сохраняли PDF, аутентичный файлу PowerPoint (внешние библиотеки плохо справляются с рендером PowerPoint).
        • Во-вторых, реализация почтового бота, как макроса в MS Outlook решала проблемы работы с корпоративной Exchange почтой. Так, в Python нет нормальной библиотеки для работы с MS Exchange.
        • Python использовался для организации мониторинга работы сервиса и нотификации о возможных проблемах
    • Задача: Программа для объединения файлов Powerpoint с "подстрочником" (текстом для докладчика) в файл для печати
      • Мой выбор: VBA. Задача решалась через конвертацию двух файлов в PDF и их объединением с Riffle Shuffle. Так как важно качество конвертации в PDF, то использовались встроенные функции офиса для экспорта в PDF.
    • Задача: Дана база данных по предприятиям отрасли в виде карточек-отдельных файлов html, которые надо отфильтровать и объединить в 1 файл Excel для расчета ряда показателей.
      • Мой выбор: Python.
        • Чтобы вытаскивать из html карточек данные пригодилась библиотека для парсинга html BeautifulSoup.
        • Excel-файл создает программа, поэтому мы сами можем решать, какую аналитику рассчитывать уже в нем, а какую еще на стадии подготовки данных в Python.
    • Задача: Перевести весь текст в презентации PowerPoint на другой язык машинным переводчиком
      • Мой выбор: VBA. Важно было аккуратно работать с текстом во внутренних объектах файла PowerPoint. Для перевода использовался API от Яндекса, так как он бесплатен для небольших объемов и прост в подклюении. Например, API переводчика Bing я так и не смог заставить работать в VBA, так как там для работы нужен OAuth со своими заморочками. Если бы пришлось работать с Bing, то, наверное, я бы делал сервис-посредник на Python.
    • Задача: По заданным биржевым тикерам брать данные из API с финансовыми показателями (API выдает сформированный по запросу CSV файл) и считать на их основе ряд бенчмарков для анализа
      • Мой выбор: Python. Хотя API — простой (не требует какой-либо подписи запросов или авторизации) и выдает данные в CSV, выбран Python, так как нет причин выбирать VBA, а на Python писать удобнее.
    • Задача: Ведение базы поручений, рассылка уведомлений исполнителям, генерация отчета для печати
      • Здесь я выбирал очень долго, так как есть много альтернатив:
        • Сторонняя готовая система поручений
        • База данных с каким-то обработчиком
        • Access
        • Excel
      • Мой выбор: VBA
        • Во-первых, Excel сам по себе является готовым UI для работы
        • Во-вторых, VBA решает задачу работы с корпоративным Exchange через подключение MS Outlook
        • В-третьих, это решение делалось с прицелом на коллег, которым Excel был более понятен, чем что-либо совсем новое

    Заключение


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

    Поделиться публикацией

    Комментарии 16

      0
      Не убедительно.
      Пишу на vbscript или HTA. На VBA не программирую, потому что не установлен Microsoft Office.
      Так вот для подключения к внешним API не вызывает сильных проблем: COM-сервера, WMI, библиотека ADO и DOM — все это можно использовать и в VBA.
        0
        В Vbscript нет отладчика. Это решает все. Редактор VBA в Office это по-сути обрезанная версия Visual Studio 6. И есть хороший отладчик. Без него любой более-менее сложный проект не написать. Поэтому я пользуюсь только VBA.
          0
          Может красивого и нет, но сообщения об ошибке в такой-то строке при исполнении выскакивает.
            0
            да есть же cscript //X scriptfile.vbs
              0
              Да есть, не отрицаю, но некоторые вещи в консоли не пашут.
            0
            vbsedit посмотрите там и редактор и отладчик есть.
              0
              Специфика работы не позволяет пользоваться сторонним ПО, потому и пишу том что есть, блокнот с подсветкой. Для мелких работ вполне хватает.
            0
            А почему не jscript? — все API те же, плюс на 99% тот же javascript, опыт работы на котором можно переиспользовать в массе областей. Ну и без призрачных перспектив один только try-catch дорогого стоит.
              0
              Пишу конечно jscript, не без этого иначе не поймут сотоварищи. Просто как-то изначально случилось, что первые скрипты писал на vbscript на работе, так и продолжил. Часто приходиться тупа собирать новый скрипт из кусков старого, просто копипаст и все.
              Очень в своё время поразила работа скриптового червя I love you, на его основе написал скрипт, который из большого текстового файла по заданным параметрам искал кусок необходимого текста и кидал на печать. Ну как кидал, формировал файл и батником отправлял в lpt порт матричного принтера.
              0
              Я вам больше скажу, при использовании соответствующей библиотеки в Python вы получаете доступ ко всем возможностям VBA)
              +1
              Больше десятка лет назад у меня была задача экспортировать текстовые данные из Autocad в Excel. Выбор стоял между Lisp и VBA, оба языка я знал на уровне пары обзорных лекций в университете. Потыкавшись в Лиспе день или два, я понял, что выбора на самом деле у меня нет. На VBA получилось почти без проблем, работало нормально. (к чему это? просто музыкой навеяло)
                0
                cscript //X scriptfile.vbs — в качестве отладчика VBScript прекрасно работает Visual Studio
                  0
                  Не согласен с графиком сложности разработки, есть какие-то аргументы?
                    +1
                    На самом деле нужно выбирать инструмент исходя из основной логики программы. Если нужно работать с объектами Office — лучше использовать VBA. Если Office используется только для вывода результата, то лучше использовать то, что ближе по технологии к объекту обработки.
                    С графиком тоже не согласен. Если возникают задачи, которые на VBA сложно решить, я использую VSTO. Вся мощь NET в поддержку.
                      +1
                      > Задача: Дана база данных по предприятиям отрасли в виде карточек-отдельных файлов html, которые надо отфильтровать и объединить в 1 файл Excel для расчета ряда показателей.
                      Не знаю, как там всё было устроено, но, возможно, задачу можно решить вообще без программирования, используя надстройку PowerQuery (не путать с PowerPivot).
                        0
                        Была задача проверить 20к имен из excel файла с именами общей базы(110k).Ну еще вычислить кое какую дату и отсортировать все.Питон справился отлично.За 2-3 минуты программа все сделала и создала нужные файлы для дальнейшей работы.

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

                        Самое читаемое