В организации часто печатают сметы и это множество файлов, но часто бывают ситуации когда последняя страница распечатанного документа содержит только подписи, но не содержит ни одной цифры стоимости, потому что все они остаются на предыдущем листе. Директора в такой ситуации обычно отказываются подписывать документы и у таких смет приходится перепечатывать последний лист. Типичный пример такой сметы показан на скриншоте ниже:
К исполнителю, который печатает сметы они поступают в виде обычного Эксель файла, который надо просто распечатать. Чтобы не просматривать перед печатью каждый файл было решено автоматизировать процесс и как-то автоматически определять - остаются ли на последнем листе только подписанты или есть хотя бы одна цифра?
Самым простым виделось сделать это, пользуясь средствами самого Excel. Для этого подходило Visual Basic для приложений (VBA) в Office.
Проблема
Сметы создаются в приложении ГРАНД-Смета, версия 2024.2 - это указывается в колонтитуле Эксель файла и, возможно, у этого приложения есть такая кнопка, чтобы сделать всё красиво и не делать так, чтобы на последнем листе оставались только подписи.
Но организация, которая составляет эти сметы, и передаёт их на печать к нам в другую организацию, похоже с этой кнопкой не знакома. Поэтому в Excel часто сталкивались с ситуацией, когда последняя страница содержит только строки подписи, а цифры цен отображается на предыдущей странице.
Такой макет может вызвать недовольство или отказ от подписи со стороны директоров, которые предпочитают, чтобы цена была на той же странице, что и подписи.
Решение
Сценарий VBA под названием «AdjustRowHeightsForSignaturePage» предназначен для обработки множества книг Excel, в том числе в текущем и во вложенных каталогах, и изменения высоты последних нескольких строк, чтобы гарантировать, что окончательная цифра цены и строки подписи появятся на одной странице. Эта корректировка предотвращает ситуацию, когда последняя страница сметы лишена цифр.
Подготовка Microsoft Excel
Убедитесь, что Microsoft Excel установлен. Включение вкладки «Разработчик» в Excel позволяет получить доступ к инструментам и функциям, связанным с разработкой, включая макросы, Visual Basic для приложений (VBA). Вот как включить вкладку «Разработчик» в Excel:
1. Запустите Excel на своем компьютере.
2. Перейдите к параметрам. В зависимости от вашей версии Excel действия могут незначительно отличаться:
➡️ Для Excel 2010 и более поздних версий: нажмите вкладку «Файл» в верхнем левом углу, затем выберите «Параметры» в нижней части меню.
➡️ Для Excel 2007: нажмите круглую кнопку Office в верхнем левом углу, затем нажмите «Параметры Excel» в нижней части меню.
3. В диалоговом окне «Параметры Excel» слева вы увидите список категорий. Найдите и нажмите «Настроить ленту» (для Excel 2010 и более поздних версий) или «Популярные» (для Excel 2007).
4. Найдите параметр «Разработчик» в списке справа в разделе «Основные вкладки». Убедитесь, что флажок рядом с «Разработчик» установлен.
4. Установив флажок «Разработчик», нажмите кнопку «ОК» в нижней части диалогового окна «Параметры Excel», чтобы сохранить изменения и закрыть диалоговое окно.
5. После того как вы включили вкладку «Разработчик», вы должны увидеть ее на ленте Excel в верхней части окна Excel вместе с другими вкладками, такими как «Главная», «Вставка» и т.д.
Сценарий VBA для изменения высоты последних строк, если это требуется
Перед любыми массовыми манипуляциями с файлами рекомендую создать резервные копии файлов Excel простым копированием каталогов.
Чтобы вставить сценарий VBA в Excel и выполнить его, выполните следующие действия:
1. Включите вкладку «Разработчик» (если она еще не включена).
2. Получите доступ к редактору Visual Basic:
Нажмите вкладку «Разработчик» на ленте Excel.
В группе «Код» нажмите «Visual Basic» (или нажмите ALT+F11).
3. Вставьте скрипт:
В окне редактора Visual Basic убедитесь, что ваша книга выбрана в окне Project Explorer обычно с именем «VBAProject (имя файла)».
Щелкните правой кнопкой мыши на имя проекта или любую существующую папку модуля.
Выберите «Вставка» > «Модуль» в контекстном меню.
Вставьте код VBA:
' Подробнее: https://habr.com/ru/articles/820249/
' Михаил Шардин, https://shardin.name/
Sub AdjustRowHeightsForSignaturePage()
Dim FileSystem As Object
Dim HostFolder As String
Dim FileName As String
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
' Определить путь к папке
HostFolder = ThisWorkbook.Path
' Создать экземпляр FileSystemObject
Set FileSystem = CreateObject("Scripting.FileSystemObject")
' Рекурсивная функция для поиска во вложенных каталогах
ProcessFiles HostFolder, FileSystem
MsgBox "Обработка всех файлов завершена", vbInformation
End Sub
Sub ProcessFiles(ByVal folderPath As String, ByVal fs As Object)
Dim subFolder As Object
Dim file As Object
Dim ws As Worksheet
Dim lastRow As Long
Dim startRow As Long
Dim pageBreaks As HPageBreaks
Dim breakCount As Long
Dim lastPageBreakRow As Long
Dim i As Long
On Error GoTo ErrorHandler
' Обработка файлов в текущем каталоге
For Each file In fs.GetFolder(folderPath).Files
If LCase(file.Name Like "*.xlsx*") Then
' Открыть книгу
Dim wb As Workbook
Set wb = Workbooks.Open(file.Path)
' Цикл по каждому листу в книге
For Each ws In wb.Worksheets
ActiveWindow.View = xlPageBreakPreview
' Найти последнюю использованную строку на рабочем листе, где 2 номер столбца
lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
' MsgBox "Последняя использованная строка в столбце 2 на рабочем листе: " & lastRow
' Определить начальную строку последних 20 строк
startRow = lastRow - 20
If startRow < 1 Then startRow = 1 ' Убедитесь, что начальная строка не меньше 1
' MsgBox "Начальная строка последних 20 строк: " & startRow
' Получить горизонтальные разрывы страниц
Set pageBreaks = ws.HPageBreaks
breakCount = 0
' Подсчитать количество разрывов документа в пределах последних 15 строк
' MsgBox "Количество разрывов документа: " & pageBreaks.Count
For i = 1 To pageBreaks.Count
' MsgBox "Разрыв страницы " & i & " находится в строке " & pageBreaks(i).Location.Row & vbNewLine & "Значение lastRow " & lastRow & " - 15 = " & lastRow - 15
If pageBreaks(i).Location.Row > lastRow - 14 Then
breakCount = breakCount + 1
End If
Next i
' Если последние 15 строк начинаются на новой странице с менее чем 15 строками, изменить высоту последних 20 строк
If breakCount > 0 And breakCount < 15 Then
For i = startRow To lastRow
ws.Rows(i).RowHeight = 25
Next i
MsgBox "Высота последних строк изменена для файла " & file.Name & vbNewLine & file.Path
End If
Next ws
' Сохранить и закрыть книгу
wb.Save
wb.Close
End If
Next file
' Обработка вложенных папок
For Each subFolder In fs.GetFolder(folderPath).SubFolders
ProcessFiles subFolder.Path, fs
Next subFolder
Exit Sub
ErrorHandler:
MsgBox "Произошла ошибка: " & Err.Description, vbExclamation
End Sub
4. Запустите сценарий VBA:
➡️ Запуск макроса.Можете запустить его, перейдя на вкладку «Разработчик», нажав «Макросы», выбрав макрос и нажав «Выполнить».
➡️ Запуск из VB: Можете запустить ее непосредственно из редактора Visual Basic, поместив курсор внутри процедуры и нажав F5 или выбрав «Выполнить» > «Выполнить Sub / UserForm» из меню.
Готово
Теперь надо проверить результаты и убедится, что скрипт выполнил нужные действия и изменил высоту последних строк, где это необходимо в других книгах Excel.
Итоги
Использование сценариев VBA позволяет решить проблему, когда последняя страница содержит только строки подписи без цен. Зачастую это приводит к недовольству или прямому отказу подписывать документ. Чтобы решить эту проблему сценарий Visual Basic для приложений (VBA) в Office может гарантировать то, что этого не случится, для этого сценарий регулирует высоту последних строк.
Пошаговое руководство, изложенное в этой статье, дает инструмент, позволяющий за минуты сделать преобразование в сотнях документов и выполнить то, что в противном случае потребовало бы часов выполнения вручную.
Автор: Михаил Шардин,
10 июня 2024 г.
