How I was able to automatically generate a document inventory for 700 pages using two “cheats”

Sometimes there are tasks that you don't want to take on – for example, in the photo there is a real stack of documents of about 700 pages for which you need to write a cover letter – that is, make an inventory of the documents. According to a rough estimate – manual work for a whole day at least.

The situation is somewhat alleviated by the fact that there are original Excel files for these printed documents. As a result, searching and writing the solution took about an hour and in case of repeated work will take about 5 minutes of my own time.

Part 1: Visual Basic for Applications (VBA)

Visual Basic for Applications (VBA) is a programming language that allows you to automate tasks and create macros for Microsoft Office applications. Simply put, VBA helps users automate repetitive tasks such as creating reports, formatting documents, and more.

In this case, the advantage was that all the documents were of the same type and created in Excel using a template – form KS-3. Form KS-3 refers to documentation in the construction industry and is a “Certificate of the cost of work performed and expenses”.

This form for each address was stored in a file called “+KS-3.xlsx”: inside the main directory there were many subdirectories and the file had two very important cells:

In all documents, these cells do not change and the data can be automatically collected in Excel using a script that goes through the main directory and all nested ones:

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

The result of the script is the created table:

Half the work is done – addresses and amounts are already automatically collected in one table.

Part 2: Google Apps Script

Google Apps Script is a programming language created by Google to work with various Google services, such as Gmail, Calendar, Drive, and others. It allows developers to create scripts that can automatically perform certain tasks, such as file management, report generation – in fact, almost anything.

I didn't bother to figure out how to generate a cover letter based on a ready-made table using VBA, because I was well acquainted with a similar solution for Google scripts. It's not for nothing that the title of the article mentions two “crutches”.

So I transferred the table from Excel to Google Sheet and wrote a script that generates the text of the letter using a simple template:

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}`);
}

The generation process took 4 seconds:

After that I checked the purpose of the whole work – the created cover letter and immediately with formatting:

Results

In general, with the help of two software “cheats” I was able to automatically generate an inventory of documents for any folder thickness and any number of files.

Author: Mikhail Shardin

September 16, 2024