Remkomplekty.ru

IT Новости из мира ПК
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Vba excel работа с файлами

3.9.10 Функции для работы с файловой системой

Файловые функции VBA, Input(), FileLen(), EOF(), LOF(), Loc()

В VBA предусмотрен набор встроенных функций для выполнения различных операций с файлами, каталогами, дисками и прочими объектами файловой системы. Информация об этих функциях приведена ниже. Но не забывайте, что помимо этих возможностей (общих для всех приложений, в которых используется VBA) у нас есть также, во-первых, возможности, специфические для данного приложения (например, открытие и сохранение документа Word средствами объектной модели Word). Во-вторых, на любом компьютере под управлением Windows есть объектная библиотека под названием Microsoft Scripting Runtime — очень простая и очень удобная для выполнения различных операций с файлами, каталогами и дисками. Можно добавить в проект VBA на нее ссылку и использовать все имеющиеся в ней возможности. Если, к примеру, мне нужно пройти по всем файлам в данном каталоге и что-нибудь с ними сделать (например, загрузить в Excel все файлы отчетов, которые пришли из филиалов), я использую именно эту библиотеку. Справку по ней можно найти на сайте Microsoft (www.microsoft.com/scripting).

А это — встроенные функции для работой с файловой системой, предусмотренные в VBA:

  • CurDir() — функция, которая возвращает путь к текущему каталогу, в котором будут сохраняться файлы нашего приложения по умолчанию.
  • Dir() — позволяет искать файл или каталог по указанному пути на диске.
  • EOF() — при операции записи в файл на диске эту функция вернет True, если вы находитесь в конце файла. Используется при записи в файл своего собственного формата. При сохранении документов Word, книг Excel и т.п. лучше использовать стандартные методы объектов этих документов: Save и SaveAs().
  • Error() — позволяет вернуть описание ошибки по ее номеру. Генерировать ошибку нужно при помощи метода RaiseError() специального объекта Err (см. главу 6, в которой рассказывается про перехват ошибок и отладку).
  • FileAttr() — позволяет определить, как именно был открыт вами файл в файловой системе: на чтение, запись, добавление, в двоичном или текстовом режиме и т.п.
  • FileDateTime() — позволяет получить информацию о последнем времени обращения к указанному вами файлу. Если к файлу после создания ни разу не обращались, то это будет время создания файла.
  • FileLen() — позволяет определить длину указанного вами файла в байтах.
  • FreeFile() — позволяет определить следующую свободную цифру, которую можно использовать как номер файла при его открытии.
  • GetAttr() — возможность обратиться к файлу к файловой системе и получить информацию об его атрибутах (скрытый, доступен только для чтения, архивный и т.п.)
  • Input() — позволяет считать информацию из открытого файла. Например, считать информацию из файла C:text1.txt и вывести ее в окно Immediate можно так:

Open » c:text1.txt» For Input As #1 ‘Открываем файл функцией Open() на чтение

Do While Not EOF(1) ‘пока файл не кончился

‘ Получаем по одному символу и добавляем его к предыдущим

MyChar = MyChar & Input(1, #1)

Close #1 ‘ Закрываем файл

Debug . Print MyChar ‘Выводим его содержание в окно Immediate

  • Вариант этой функции — InputB() позволяет указывать количество байт, которые надо скачать из файла.
  • Loc() — от Location, то есть местонахождение — возвращает число, которое определяет текущее место вставки или чтения в открытом файле. Похоже работает функция Seek(), но она возвращает информацию о позиции, с которой будет выполняться следующая операция чтения или вставки.
  • LOF() — от length of file — позволяет определить длину открытого файла в байтах.

Объект FileSystemObject для работы с файлами. С примерами на VBA

Объект FileSystemObject

Ниже приведен справочник по объекту FileSystemObject, который умеет работать с файлами, каталогами, файловыми потоками. Данный объект позволяет читать файлы, писать в файлы, получать информацию о файлах и др. Данный объект используется в ОС Windows, в некоторых скриптовых языках.

1. Создание объекта

2. Свойства

2.1. Drives

Возвращаемое значение: объект-коллекция «Drives».

Замечание: только чтение.

3. Методы

3.1. CreateFolder

Назначение: создаёт каталог с указанным именем.

  • — строка, путь к каталогу. Если такой каталог уже существует, произойдёт ошибка.

3.2. CopyFile

Назначение: копирует один или несколько файлов.

  • — строка, путь к источнику копирования (что копировать). В последнем компоненте параметра можно использовать групповые символы «*» и «?».
  • — строка, путь назначения (куда копировать).
  • — необязательный, булево (число). Перезаписывать существующие файлы, или нет. По умолчанию — True (перезаписывать). Если файл, который нужно перезаписать, имеет атрибут read-only, возникнет ошибка (независимо от установки этого параметра).

3.3. CopyFolder

Назначение: рекурсивно копирует каталог.

  • — строка, путь к источнику копирования (что копировать). В последнем компоненте параметра можно использовать групповые символы «*» и «?».
  • — строка, путь назначения (куда копировать).
  • — необязательный, булево (число). Перезаписывать существующие файлы, или нет. По умолчанию — True (перезаписывать). Если файл, который нужно перезаписать, имеет атрибут read-only, возникнет ошибка (независимо от установки этого параметра).

Описание: процесс копирования прерывается после первой возникшей ошибки.

3.4. MoveFile

Назначение: перемещает один или несколько файлов.

  • — строка, путь к источнику копирования (что копировать). В последнем компоненте параметра можно использовать групповые символы «*» и «?».
  • — строка, путь назначения (куда копировать).

Описание: процесс перемещения прерывается после первой возникшей ошибки. Если перемещаемый файл уже существует или является папкой в Destination, возникнет ошибка.

3.5. MoveFolder

Назначение: рекурсивно перемещает один или несколько каталогов.

  • — строка, путь к источнику копирования (что копировать). В последнем компоненте параметра можно использовать групповые символы «*» и «?».
  • — строка, путь назначения (куда копировать).

Описание: процесс перемещения прерывается после первой возникшей ошибки. Если перемещаемый каталог уже существует или является файлом в Destination, возникнет ошибка.

3.6. DeleteFile

Назначение: удаляет указанный файл.

  • — строка, путь к файлу. В последнем компоненте параметра можно использовать групповые символы «*» и «?».
  • — необязательный, булево (число). Определяет, удалять или нет файлы с атрибутом read-only. По умолчанию — False (не удалять).

3.7. DeleteFolder

Назначение: удаляет указанную папку.

  • — строка, путь к папке. В последнем компоненте параметра можно использовать групповые символы «*» и «?».
  • — необязательный, булево (число). Определяет, удалять или нет файлы с атрибутом read-only. По умолчанию — False (не удалять).

3.8. BuildPath

Назначение: добавляет к заданному пути новое имя. Если необходимо, вставляется «».

— строка, путь.

  • — строка, имя файла.
  • 3.9. GetAbsolutePathName

    Назначение: возвращает полный путь для заданного относительного пути (из текущего каталога).

    — строка, относительный путь.

    3.10. GetBaseName

    Назначение: возвращает имя (без расширения) последнего компонента в заданном пути.

    3.11. GetExtensionName

    Назначение: возвращает расширение последнего компонента в заданном пути.

    3.12. GetFileName

    Назначение: возвращает имя (с расширением) последнего компонента в заданном пути.

    3.13. GetParentFolderName

    Назначение: возвращает путь к последнему компоненту в заданном пути (его каталог).

    3.14. GetDriveName

    Назначение: возвращает имя диска в заданном пути.

    Читать еще:  Excel автоматический пересчет формул

    3.15. DriveExists

    Назначение: возвращает True, если указанный диск сущесвтвует, и False в противном случае.

    3.16. FileExists

    Назначение: возвращает True, если указанный файл сущесвтвует, и False в противном случае.

    3.17. FolderExists

    Назначение: возвращает True, если указанный каталог сущесвтвует, и False в противном случае.

    3.18. GetDrive

    Назначение: возвращает объект «Drive» по указанному имени или пути.

    • — строка, имя диска или путь к корневому каталогу диска, возможно UNC-путь.

    3.19. GetFile

    Назначение: возвращает объект «File» по указанному пути.

    Описание: если файл не существует, произойдёт ошибка.

    3.20. GetFolder

    Назначение: возвращает объект «Folder» по указанному пути.

    Описание: если каталог не существует, произойдёт ошибка.

    3.21. GetSpecialFolder

    Назначение: возвращает объект «Folder» для некоторых специальных папок Windows.

    • — число, определяет специальную папку. Возможные значения:
      • 0 — Каталог Windows.
      • 1 — Системный каталог библиотек и драйверов.
      • 2 — Каталог временных файлов, путь к которому хранится в переменной среды «TMP».

    Описание: если каталог не существует, произойдёт ошибка.

    3.22. GetTempName

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

    3.23. CreateTextFile

    Назначение: создаёт новый текстовый файл и возвращает объект «TextStream», указывающий на него.

    • — строка, путь к файлу.
    • — необязательный, булево (число). Перезаписывать файл, если он существует (True), или нет (False). По умолчанию — False. Если указано False и файл существует — произойдёт ошибка.
    • — необязательный, булево (число). Файл в формате Unicode (True), или ASCII (False). По умолчанию — False.

    3.24. OpenTextFile

    Бесплатный видеокурс по SQL

    РАБОТА С ФАЙЛАМИ

    Типы файлов в VBA

    В VBA допустима работа с тремя типами текстовых файлов. .

    Файл последовательного доступа

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

    Файл произвольного доступа

    Состоит из записей фиксированной длины и размер записи указывается при его открытии. Это позволяет локализовать любую запись в файле по ее номеру

    Является частным случаем файла произвольного доступа. Размер записи в бинарном файле считается равным 1 байту

    Открытие и закрытие файла

    Обсудим инструкции, связанные с открытием файла для операций ввода/вывода, а также с закрытием файла по завершению работы с ним.

    Разрешает выполнение операций ввода/вывода при работе с файлом.

    Open Путь For Режим [Access Доступ] [Блокировка] As [ # ] НомерФайла [Lеn=Длина]

    Завершает операции ввода/вывода с файлом, открытым с помощью инструкции open. Эта инструкция ‘очищает буфер и указывает операционной системе обновить FAT (таблицу размещения файлов). Важно, чтобы каждый файл по завершении работы с ним был закрыт, иначе это может привести к частичной потере информации.

    Close [СписокНомеровФайлов] Аргумент СписокНомеровФайлов может представлять один или несколько номеров файлов. При этом используется следующий синтаксис, где номерФайла представляет любой допустимый номер файла: [[#] номерФайла] [, [#] номерФайла] .

    Закрывает все активные файлы, открытые с помощью инструкции open, и записывает содержимое всех буферов файлов

    На диск, открытых с помощью инструкции Open

    Функция возвращает доступный номер, который может использоваться в инструкции Open

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

    Open «Первый» For Binary Access Write As #1 Close #1

    Ввод данных в файл последовательного доступа

    Обсудим инструкции, используемые при вводе информации в файл последовательного доступа.

    Записывает форматированные данные в файл последовательного доступа. Синтаксис:

    Print #НомерФайла, [СписокВывода]

    Записывает неформатированные данные в файл последовательного доступа. В отличие от инструкции Print, инструкция write вставляет запятые между элементами и заключает строки в кавычки по мере записи их в файл.

    Write #НомерФайла, [СписокВывода]

    Приведем две процедуры, использующие инструкции Print и write для создания файлов последовательного доступа. Сначала рассмотрим процедуру с инструкцией

    Open «С:Новый» For Output As II

    ‘ Печатает текст в файл ‘

    ‘ Печатает пустую строку в файл

    ‘ Печатает в двух зонах печати

    Результатом описанных выше инструкций будет файл со следующим содержимым:

    Зона 1 Зона 2 3 пробела

    Перейдем теперь к примеру процедуры, создающей файл последовательного

    Достпа при помощи инструкции Write.

    For Output As #1

    Write #l, «инструкции»;

    Результатом описанных выше инструкций будет файл со следующим содержимым:

    «Пример»,»использования» «инструкции»,»Write» «Число»,1

    Обратите внимание на автоматическое размещение в файле разделителей -запятых, и то, что строковая информация берется в кавычки. В процедуре

    ПримерислользованияWrite вторая инструкция write специально заканчивается знаком «;» Это обеспечивает вывод данных третьей инструкцией write в ту же строку файла, в которую выводила вторая инструкция.

    Вывод данных из файла последовательного доступа

    Обсудим инструкции и функции вывода информации из файла последовательного доступа, а также функцию идентификации конца файла.

    Считывает .данные из открытого файла последовательного доступа и присваивает их переменным. Данные, считываемые с помощью инструкции input I, обычно записываются в файл с помощью инструкции Write #.

    Input #НомерФайла, СписокПеременных

    Считывает строку из открытого файла последовательного доступа и присваивает ее переменной типа string. Данные, считываемые с помощью инструкции Line input I, как правило, записываются в файл с помощью инструкции Print #.

    Line Input #НомерФайла, ИмяПеременной

    Синтаксис инструкции Line Input1 содержит следующие элементы:

    Возвращает значение типа string, содержащее символы из файла, открытого в режиме input или Binary. Функция input считывает данные, записываемые в файл с помощью инструкции Print # или Put.

    Input (Число, [#] НомерФайла)

    Функция возвращает значение True при достижении конца файла.

    EOF (НомерФайла> При последовательном считывании информации из файла часто используется следующий цикл:

    Do While Not EOF(l) Loop

    или, для тех пользователей, кто предпочитает инструкцию While — Wend инструкции Do While — Loop, следующий эквивалентный цикл:

    While Not EOF (I) Wend

    Приведем пример использования инструкции input # для считывания данных из файла. В этом примере предполагается, что на диске существует файл группаЭкономистов, содержащий информацию о студентах. Файл был создан при помощи инструкции write # и состоит из двух столбцов, в первом из которых указывается фамилия, а во втором — оценка студента. Для удобства работы с информацией введен пользовательский тип Студенты. Процедура примериспользованияInput последовательно считывает фамилии и оценки из файла и выводит их в ячейки первого и второго столбца рабочего листа.

    ‘ Фамилия As String * 20

    Оценка As String * 3

    Dim Студент As Студенты

    For Input As 12 i = 1

    Do While Not EOF(2) With Студент

    Input #2, .Фамилия, .Оценка

    Cells(i, 1).Value = .Фамилия

    Cells(i, 2).Value = .Оценка

    Приведем пример использования инструкции Line input # для считывания данных из файла группаЭкономистов, имеющего ту же структуру, что и в предыдущем примере, но созданного с помощью инструкции Print #. Инструкция Line input # считывает всю строку из файла в строковую переменную. Поэтому в этом случае уже нет необходимости использовать введенный пользовательский тип, а достаточно ограничиться только обычной строковой переменной. Вся считываемая информация строка за строкой вводится в список диалогового .окна.

    Читать еще:  Cells vba excel описание

    Private Sub UserForm_Initialize()

    Dim Студент As String

    .Clear Do While Not EOF(l)

    Line Input 11, Студент

    Работа с файлом произвольного доступа

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

    Записывает содержимое переменной в файл произвольного доступа.

    Put [#] НомерФайла, [НомерЗаписи] , ИмяПеременной

    Читает данные из открытого файла произвольного доступа в переменную. Синтаксис:

    Get [#] НомерФайла, [НомерЗаписи] , ИмяПеременной

    Функция возвращает значение типа Long, определяющее текущее положение указателя чтения/записи внутри файла, открытого с помощью инструкции Open.

    Функция возвращает значение типа Long, представляющее размер файла в байтах, открытого с помощью инструкции Оpen . Для определения размера закрытого файла следует использовать функции FileLen.

    Возвращает значение типа Long, содержащее размер файла в байтах.

    Как видно из приведенного в предыдущем разделе примера для считывания информации при помощи инструкции input # из файла группаЭкономистов, файл последовательного доступа иногда удобно задавать как последовательность записей (в данном случае, записей о студентах), причем каждая из записей формируется из группы полей (в данном случае из полей Фамилия и оценка). Отметим, что такая группировка по записям не является чем-то присущим файлам последовательного доступа. Это просто подход, позволяющий упростить процесс последовательного считывания записей. В файле последовательного доступа существует только одна внутренняя структура, образованная разделителями (запятыми или специальными символами, обозначающими переход на новую строку).

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

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

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

    Отметим, что при открытии файла произвольного доступа, в отличие от файла последовательного доступа, не надо специально указывать, открывается он для ввода или вывода информации. Ввод и вывод информации определяют команды Put и Get.

    Приведем один, пример работы с файлом произвольного доступа группа Экономистов, который имеет ту же структуру, что и в предыдущем примере. Файл создается с помощью процедуры записьвоайл, которая последовательно считывает данные из первого и второго столбца рабочего листа и затем вводит их в файл. В этой процедуре число вводимых записей фиксировано и равно 5. Процедура Считываниеизфайла производит обратное действие — считывает данные из файла и вводит их в ячейки третьего и четвертого столбца рабочего листа. Интересной особенностью файла произвольного доступа является то, что при работе с ним можно определить число записей не пересчитывая их. Число записей равно отношению размера файла к длине одной записи. Длина записи устанавливается при создании файла произвольного доступа и определяется типом переменной, при помощи которой файл был создан, размер открытого файла возвращается функцией LOF, а еще не открытого — функцией FileLen. В рассматриваемом случае число записей в файле равно LOF(l) / Len(Студент).

    Фамилия As String * 20 Оценка As String * 3

    Dim Студент As Студенты

    Dim i As Integer

    For Random As #1 Len = Len(Студент)

    .Фамилия = Cells(i, 1).Value .Оценка = Cells(i, 2).Value

    Функции, используемые в процедурах VBA

    Настоящая заметка продолжает знакомство с VBA. В ней представлены некоторые «практичные» функции, которые могут использоваться в ваших приложениях либо помогут в создании аналогичных функций. Эти функции наиболее полезны, когда вызываются из другой процедуры VBA. Следовательно, они объявляются с ключевым словом Private и не отображаются в диалоговом окне Excel Мастер функций (подробнее см. Работа с процедурами VBA).[1]

    Функция FileExists

    Данная функция получает один аргумент (путь и имя файла) и возвращает ИСТИНА, если файл существует.

    Скачать заметку в формате Word или pdf, примеры в архиве (политика безопасности провайдера не позволяет загружать файлы Excel с поддержкой макросов)

    Функция FileNameOnly

    Функция получает один аргумент (путь и имя файла; не забывайте брать строку в кавычки) и возвращает только имя файла.

    Функция использует функцию VBA Split, которая принимает строку (вместе с символами-разделителями) и возвращает массив типа variant, содержащий элементы, которые находятся между символами-разделителями. В рассматриваемом случае переменной temp присваивается массив, содержащий текстовые строки между Application.PathSeparater (обычно в качестве разделителя используется обратная косая черта).

    Если в качестве аргумента указать » с:excelfiles2010backupbudget.xlsx » , функция возвратит строку budget.xlsx.

    Функция FileNameOnly обрабатывает любой путь и имя файла (даже если файла не существует). Если файл существует, лучше воспользоваться следующей более простой функцией.

    Функция PathExists

    Функция получает один аргумент (путь) и возвращает ИСТИНА, если путь существует.

    Функция RangeNameExists

    Функция получает один аргумент (название диапазона) и возвращает ИСТИНА, если в активной рабочей книге существует указанное название диапазона.

    Функция SheetExists

    Функция получает один аргумент (название рабочего листа) и возвращает ИСТИНА, если данный рабочий лист существует в активной рабочей книге.

    Функция WorkbooklsOpen

    Функция получает один аргумент (название рабочей книги) и возвращает ИСТИНА, если данная рабочая книга открыта.

    Проверка принадлежности к коллекции

    Следующая функция представляет собой образец «групповой» функции, с помощью которой можно определить, является ли объект членом коллекции.

    Эта функция имеет два аргумента: коллекцию (объект) и элемент (строка), который может быть либо не быть членом коллекции. Функция будет создавать объектную переменную, представляющую элемент коллекции. Если попытка увенчается успехом, функция возвратит True; иначе — False. Функцию IsInCollection можно использовать вместо трех других функций, приведенных выше. Чтобы определить, содержится ли в активной рабочей книге диапазон Data, вызовите функцию IsInCollection с помощью следующего оператора:

    MsgBox IsInCollection(ActiveWorkbook.Names, » Data » )

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

    MsgBox IsInCollection(Workbooks, » budget.xlsx » )

    Чтобы узнать, содержит ли активная рабочая книга рабочий лист Лист1, используйте следующий оператор:

    MsgBox IsInCollection(ActiveWorkbook.Worksheets, » Лист1 » )

    Получение значения из закрытой рабочей книги

    В VBA не существует метода получения значения из закрытого файла рабочей книги. Однако вы можете воспользоваться возможностью управления ссылками на файлы, которая предоставляется в Excel. В настоящем разделе описана функция VBA GetValue, которая получает значение из закрытой книги. Эта задача выполняется в результате вызова макроса XLM, который появился в «доисторических» версиях Excel (до версии 5), но поддерживается до сих пор (подробнее о макрофункциях xlm см. Функция Получить.Ячейку).

    Tech&Biz Insights

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

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

    Читать еще:  Как округлить сумму в 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 был более понятен, чем что-либо совсем новое

    Заключение

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

    Ссылка на основную публикацию
    ВсеИнструменты
    Adblock
    detector
    ×
    ×