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

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

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

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://habr.com/ru/articles/843298/
    
    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

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

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

Часть 2: Google Apps Script

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

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

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

function generateLetters() {
  // https://habr.com/ru/articles/843298/
  
  // Получить активную таблицу и её 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 секунды:

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

Итоги

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

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

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