Как сделать так, чтобы на последнем листе Экселя оказались не только подписи директоров, но и суммы

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

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

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