В организации часто печатают сметы и это множество файлов, но часто бывают ситуации когда последняя страница распечатанного документа содержит только подписи, но не содержит ни одной цифры стоимости, потому что все они остаются на предыдущем листе. Директора в такой ситуации обычно отказываются подписывать документы и у таких смет приходится перепечатывать последний лист. Типичный пример такой сметы показан на скриншоте ниже:

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

Самым простым виделось сделать это, пользуясь средствами самого 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/

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 г.