Как я при помощи двух скриптов смог автоматически сгенерировать опись документов для 700 страниц

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

Ситуацию несколько облегчает то, что на эти распечатанные документы есть исходные Excel файлы. В итоге поиск и написание решения заняло около часа и в случае повторной работы займёт около 5 минут собственного времени.

Visual Basic for Applications (VBA) – это язык программирования, который позволяет автоматизировать задачи и создавать макросы для приложений Microsoft Office. Проще говоря, VBA помогает пользователям автоматизировать повторяющиеся задачи, такие как создание отчетов, форматирование документов и многое другое.

В данном случае преимуществом было то, что все документы однотипные и созданы по шаблону - по форме КС-3. Форма КС-3 относится к документации в сфере строительства и представляет собой "Справку о стоимости выполненных работ и затрат".

Эта форма для каждого адреса хранилась в файле который назывался "+КС-3.xlsx": внутри основного каталога было множество подкаталогов и файл имел две очень важные ячейки:

Ячейка A10 - содержала название.
Ячейка I36 - содержала стоимость.

Во всех документах эти ячейки не меняются и данные можно автоматически собрать в Экселе при помощи скрипта, который обходит основной каталог и все вложенные:

Sub CopyDataFromFiles() Dim FileSystem As Object Dim objFile As Object Dim objFolder As Object Dim wbSource As Workbook Dim wsSource As Worksheet Dim wsDest As Worksheet Dim DestRow As Long Dim FileExt As String Dim FilePath As String Dim DestColumn As Long ' https://vc.ru/office/1478158-kak-ya-pri-pomoshi-dvuh-skriptov-smog-avtomaticheski-sgenerirovat-opis-dokumentov-dlya-700-stranic Set FileSystem = CreateObject("Scripting.FileSystemObject") Set wsDest = ThisWorkbook.Sheets(1) ' Данные будут скопированы на первый лист DestColumn = 2 ' Столбец B Application.ScreenUpdating = False ' Вызов рекурсивной функции для обработки каждого файла ProcessFiles FileSystem.GetFolder(ThisWorkbook.Path), wsDest, DestColumn Application.ScreenUpdating = True End Sub Sub ProcessFiles(ByVal objFolder As Object, ByVal wsDest As Worksheet, ByVal DestColumn As Long) Dim objFile As Object Dim wbSource As Workbook Dim wsSource As Worksheet Dim DestRow As Long ' Пройтись по каждому файлу в каталоге For Each objFile In objFolder.Files If InStr(objFile.Name, "+КС-3.xlsx") > 0 Then ' Открытие исходную рабочую книгу Set wbSource = Workbooks.Open(objFile.Path) ' Установка исходного рабочего листа Set wsSource = wbSource.Sheets(1) ' Данные будут скопированы на первый лист ' Найти следующую доступную строку на листе DestRow = wsDest.Cells(wsDest.Rows.Count, DestColumn).End(xlUp).Row + 1 ' Копировать значение из ячейки A10 исходного листа в следующую доступную строку на целевом листе wsDest.Cells(DestRow, 1).Value = wsSource.Range("A10").Value ' Копировать значение из I36 исходного листа в следующую доступную строку на целевом листе. wsDest.Cells(DestRow, DestColumn).Value = wsSource.Range("I36").Value ' Закрыть исходную книгу без сохранения изменений wbSource.Close SaveChanges:=False End If Next objFile ' Рекурсивная обработка подкаталогов For Each objFolder In objFolder.SubFolders ProcessFiles objFolder, wsDest, DestColumn Next objFolder End Sub

Результат работы скрипта - созданная таблица:

Как я при помощи двух скриптов смог автоматически сгенерировать опись документов для 700 страниц

Половина работы сделана - адреса и суммы уже автоматически собраны в одну таблицу.

Google Apps Script – это язык программирования, созданный компанией Google для работы с различными сервисами Google, такими как Gmail, Calendar, Drive и другими. Он позволяет разработчикам создавать скрипты, которые могут автоматически выполнять определенные задачи например, управление файлами, создание отчётов - на самом деле практически любые действия.

Как сделать генерацию сопроводительного письма по готовой таблице средствами VBA я не стал разбираться, потому что был хорошо знаком с подобным решением для гугл скриптов.

Так что я перенёс таблицу из Экселя в Гугл таблицу и написал скрипт, который генерирует текст письма по простому шаблону:

Процесс генерации занял 4 секунды:

После этого проверил цель всей работы - созданное сопроводительное письмо и сразу с форматированием:

В целом при помощи двух программных «костылей» смог автоматически генерировать опись документов для любой толщины папки и любого количества файлов.

Автор: Михаил Шардин

16 сентября 2024 г.

Как я при помощи двух скриптов смог автоматически сгенерировать опись документов для 700 страниц

Часть 1: Visual Basic for Applications (VBA)

Часть 2: Google Apps Script

Итоги