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

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

Реальная фотография с документами
Реальная фотография с документами

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

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

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

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

Форма КС-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 страниц

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

Часть 2: Google Apps Script

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

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

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

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

function generateLetters() { // https://vc.ru/office/1478158-kak-ya-pri-pomoshi-dvuh-skriptov-smog-avtomaticheski-sgenerirovat-opis-dokumentov-dlya-700-stranic // Получить активную таблицу и её ID var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sheet = spreadsheet.getActiveSheet(); var spreadsheetId = spreadsheet.getId(); // Получить родительскую папку нахождения таблицы var file = DriveApp.getFileById(spreadsheetId); var parentFolder = file.getParents().next(); // Получить родительскую папку // Получить все данные из таблицы var data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 2).getValues(); // Создать новый Google Документ в той же папке, что и таблица var doc = DocumentApp.create('Автосозданое сопроводительное письмо'); var docFile = DriveApp.getFileById(doc.getId()); parentFolder.addFile(docFile); // Добавить документ в родительскую папку var body = doc.getBody(); // Обработать каждую строку в таблице for (var i = 0; i < data.length; i++) { var Description = data[i][0]; // Колонка A (описание) var price = data[i][1]; // Колонка B (цена) // Добавить описание в виде параграфа var paragraph = body.appendParagraph(''); paragraph.appendText((i + 1) + ". ").setBold(true); paragraph.appendText(Description + ":"); // Создать маркированный список для каждого документа body.appendListItem("Справка КС-3 на сумму " + price + " руб. - 2 экз."); body.appendListItem("Акт приемки законченного строительством ХХХХХХХ - 1 экз."); body.appendListItem("Акт выполненных работ – 2 экз."); body.appendListItem("ЛСР - 2 экз."); body.appendListItem("ЛСР НЦС - 2 экз."); body.appendListItem("Единичные расценки стоимости работ на 1 стр - 1 экз."); body.appendListItem("Расчёт затрат на командировочные расходы на 1 стр - 1 экз."); // Добавить пустую строку между секциями body.appendParagraph(""); } // Сохранить и закрыть документ doc.saveAndClose(); // Получить URL документа var docUrl = doc.getUrl(); console.log(`Письмо создано. Можно просмотреть документ по следующей ссылке: ${docUrl}`); }

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

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

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

Цель всей работы - созданное сопроводительное письмо и сразу с форматированием
Цель всей работы - созданное сопроводительное письмо и сразу с форматированием

Итоги

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

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

22
3 комментария

спасибо, как раз искал что-то подобное.
надо теперь по изучать по глубже всё это)

1
Ответить

700 страниц - это 350 листов.
В пачке офисной бумаги - 500 листов.
На фотографии несоответствие вышесказанному. Зачем? непонятно...

Ответить

Реальная пачка документов не в смысле пачка бумаги 500 страниц. Просто пачка документов

Ответить