Створення автоматизованих звітів

Шаблон місячного звіту Кожен автоматизований звіт у цьому курсі має однакову структуру: очищення попереднього стану, фільтрація або підсумовування даних, оновлення або перебудова візуального відображення, застосування форматування для презентації та підтвердження завершення для користувача.

Приклад з поясненням: Місячний звіт в один клік

Option Explicit
 
Sub GenerateMonthlyReport()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim targetMonth As String
 
    Set ws = ThisWorkbook.Worksheets("Reports")
    Set tbl = ws.ListObjects("tblReports")
    targetMonth = "March"
 
    ' 1. Start from a clean slate
    If tbl.AutoFilter.FilterMode Then tbl.AutoFilter.ShowAllData
 
    ' 2. Filter to the month being reported on
    tbl.Range.AutoFilter Field:=1, Criteria1:=targetMonth
 
    ' 3. Refresh the summary PivotTable so it reflects current data
    On Error Resume Next
    ThisWorkbook.Worksheets("Pivot").PivotTables("ptProfitByRegion").RefreshTable
    On Error GoTo 0
 
    ' 4. Apply export-ready formatting
    With ws.PageSetup
        .Orientation = xlLandscape
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintArea = tbl.Range.Address
    End With
 
    ' 5. Confirm completion
    MsgBox targetMonth & " report is ready — filtered, " & _
        "refreshed, and print-formatted."
End Sub

П’ять пронумерованих коментарів — це не просто підписи, а буквальне втілення шаблону звіту, розглянутого раніше в цьому розділі: один крок на кожному етапі. Декілька важливих деталей:

• targetMonth As String, встановлений у фіксоване значення на початку, — це єдиний рядок, який потрібно змінити на "January" за завданням, оскільки всі наступні кроки використовують цю змінну, а не слово "March" у різних місцях процедури. Таким чином, для зміни місяця звіту потрібно змінити лише один рядок;
• Крок 3 обгортає RefreshTable у On Error Resume Next / On Error GoTo 0 з тієї ж причини, що й макрос для побудови зведеної таблиці в розділі 4.3: якщо аркуш Pivot ще не існує, цей рядок викличе помилку і зупинить виконання макросу, замість того щоб просто пропустити крок, який ще не готовий;
• Крок 4: PrintArea = tbl.Range.Address напряму прив’язує область друку до адреси самої таблиці, тому якщо рядки додаються через ListRows.Add, область друку завжди точно відповідає даним — не потрібно окремо підтримувати область друку;
• Крок 5: MsgBox додає targetMonth у текст підтвердження, тому повідомлення завжди містить назву місяця, який був оброблений.

Оновлення дашборду

Якщо у книзі є кілька зведених таблиць і діаграм, що формують дашборд, RefreshAll оновлює всі підключення до даних і PivotCache одним викликом — це однорядковий варіант того, що GenerateMonthlyReport робить вручну для однієї зведеної таблиці: ThisWorkbook.RefreshAll

Цей рядок виконує ту ж задачу, що й крок 3 у GenerateMonthlyReport, але для всієї книги, а не лише для однієї зведеної таблиці — корисно, коли дашборд містить кілька зведених таблиць, зовнішніх підключень чи пов’язаних запитів, які мають бути синхронізовані.

Форматування для експорту

Окрім налаштувань сторінки, готовий звіт часто потрібно експортувати з Excel. ExportAsFixedFormat дозволяє створити PDF безпосередньо з коду:

ws.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=ThisWorkbook.Path & "\March_Report.pdf", _
    Quality:=xlQualityStandard

ThisWorkbook.Path повертає шлях до папки, де збережено поточну книгу, без кінцевого зворотного слеша — тому ім’я файлу формується шляхом додавання "\March_Report.pdf". Якщо ThisWorkbook ще не збережено, .Path повертає порожній рядок, і цей рядок намагатиметься зберегти файл просто як "\March_Report.pdf" у корені поточного диска, тому варто переконатися, що книгу збережено хоча б один раз перед використанням цього підходу.

Завдання

1. Введіть GenerateMonthlyReport точно як показано (потрібно, щоб аркуш Pivot з попередніх розділів вже існував) і запустіть його. Переконайтеся, що таблиця відфільтрована за березень, а зведена таблиця оновилася.
2. Змініть targetMonth на "January" і запустіть ще раз — переконайтеся, що звіт оновився для нового місяця.
3. Додайте один рядок у кінець процедури, перед MsgBox, який експортує аркуш Reports у PDF за допомогою ExportAsFixedFormat, як показано вище.