Малый бизнес

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

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

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

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

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

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

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

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

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

Эта форма для каждого адреса хранилась в файле который назывался “+КС-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

‘ pikabu. ru/story/kak_ya_pri_pomoshchi_dvukh_skriptov_smog_avtomaticheski_sgenerirovat_opis_dokumentov_dlya_700_stranits_11812093

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 я не стал разбираться, потому что был хорошо знаком с подобным решением для гугл скриптов.

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

function generateLetters() {
// https://pikabu. ru/story/kak_ya_pri_pomoshchi_dvukh_skriptov_smog_avtomaticheski_sgenerirovat_opis_dokumentov_dlya_700_stranits_11812093

// Получить активную таблицу и её 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[0]; // Колонка A (описание)
var price = data[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 страниц

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

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

Итоги

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

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

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

Источник

Нажмите, чтобы оценить!
[Общий: 0 Средний: 0]

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Кнопка «Наверх»