Pyyhkäise näyttääksesi valikon

Kuukausiraportin malli Jokainen automatisoitu raportti tässä kurssissa noudattaa samaa rakennetta: vanhan tilan tyhjennys, datan suodatus tai yhteenvedon teko, visuaalisen ulostulon päivitys tai uudelleenrakennus, esitysmuotoilun lisääminen ja käyttäjälle valmistumisen vahvistaminen.

Työstetty esimerkki: Yhdellä klikkauksella kuukausiraportti

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

Viisi numeroitua kommenttia eivät ole vain otsikoita — ne ovat aiemmin tässä osiossa esitelty raporttimalli konkreettisina vaiheina. Muutamia yksityiskohtia, jotka kannattaa huomioida:

targetMonth As String, joka asetetaan kiinteäksi arvoksi ylhäällä, on se yksi rivi, jonka tehtävässä pyydetään muuttamaan "January" — koska kaikki myöhemmät vaiheet lukevat tästä yhdestä muuttujasta eikä sanaa "March" toisteta muualla Subissa, raportin kohdekuukauden vaihtaminen vaatii vain yhden rivin muokkausta;
Vaiheessa 3 RefreshTable on kääritty On Error Resume Next / On Error GoTo 0 -rakenteeseen samasta syystä kuin Pivotin rakentava makro osiossa 4.3: jos Pivot-välilehteä ei vielä ole, tämä rivi aiheuttaisi muuten virheen ja pysäyttäisi koko raporttimakron, sen sijaan että vain ohittaisi vaiheen, joka ei ole vielä valmis;
Vaiheen 4 PrintArea = tbl.Range.Address sitoo tulostusalueen suoraan taulukon omaan osoitteeseen, joten jos rivejä lisätään myöhemmin ListRows.Add-komennolla, tulostusalue vastaa silti täsmälleen dataa — erillistä tulostusalueen ylläpitoa ei tarvita;
Vaiheen 5 MsgBox yhdistää targetMonth-muuttujan vahvistusviestiin, joten viesti kertoo aina, mikä kuukausi juuri käsiteltiin.

Dashboardin päivitys

Jos työkirjassa on useita Pivot-taulukoita ja kaavioita, jotka syöttävät dataa dashboard-välilehdelle, RefreshAll päivittää kaikki tietoyhteydet ja PivotCachet yhdellä komennolla — tämä on yhden rivin versio siitä, mitä GenerateMonthlyReport tekee käsin yhdelle Pivot-taulukolle: ThisWorkbook.RefreshAll

Tämä yksi rivi tekee saman kuin GenerateMonthlyReportin vaihe 3, mutta koko työkirjan laajuudella yhden nimetyn Pivot-taulukon sijaan — hyödyllistä, kun dashboard sisältää useita Pivot-taulukoita, ulkoisia tietoyhteyksiä tai linkitettyjä kyselyitä, joiden kaikkien täytyy pysyä synkronoituna.

Vientivalmis muotoilu

Sivuasetusten lisäksi valmis raportti täytyy usein viedä kokonaan pois Excelistä. ExportAsFixedFormat tuottaa PDF-tiedoston suoraan koodista:

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

ThisWorkbook.Path palauttaa kansion, johon nykyinen työkirja on tallennettu, ilman lopussa olevaa kenoviivaa — siksi tiedostonimi rakennetaan yhdistämällä "\March_Report.pdf" siihen erikseen. Jos ThisWorkbookia ei ole vielä tallennettu, .Path palauttaa tyhjän merkkijonon ja tämä rivi yrittää tallentaa vain "\March_Report.pdf" juurihakemistoon, joten kannattaa varmistaa, että työkirja on tallennettu ainakin kerran ennen kuin luottaa tähän malliin.

Tehtävä

Kirjoita GenerateMonthlyReport täsmälleen kuten yllä (tarvitset aiemmista luvuista Pivot-välilehden valmiiksi) ja suorita se. Varmista, että taulukko suodattuu maaliskuulle ja Pivot päivittyy.
Vaihda targetMonth-arvoksi "January" ja suorita uudelleen — varmista, että raportti päivittyy uuden kuukauden mukaiseksi.
Lisää yksi rivi Subin loppuun, ennen MsgBoxia, joka vie Reports-välilehden PDF-muotoon ExportAsFixedFormat-komennolla kuten yllä.

Vinkit

1. GenerateMonthlyReportin suorittaminen sellaisenaan

Varmista ensin, että Pivot-välilehti ja ptProfitByRegion Pivot-taulukko aiemman luvun tehtävästä ovat olemassa — tämä Sub päivittää olemassa olevan Pivot-taulukon, ei rakenna uutta alusta.
Kirjoita menettely täsmälleen kuten yllä, suorita se ja tarkista kaksi asiaa: Reports-taulukon pitäisi nyt olla suodatettu näyttämään vain maaliskuun rivit ja Pivot-välilehden lukujen pitäisi heijastaa tätä (vaikka Pivot itsessään tiivistää kaikki kuukaudet riippumatta Reports-taulukon suodatuksesta, koska PivotCache lukee koko alueen, ei suodatettua näkymää).

2. targetMonthin vaihtaminen tammikuuksi

Vain yksi rivi tarvitsee muuttaa — targetMonth = "March" -asetus ylhäällä.
Suorita koko Sub uudelleen ja varmista, että Reports-taulukko suodattuu nyt tammikuulle.

3. PDF-vientirivin lisääminen

Tämä on täsmälleen sama ExportAsFixedFormat-rivi kuin aiemmin luvussa — viet ws-työvälilehden, et koko työkirjaa.
Rakenna tiedostonimi samalla tavalla kuin luvun laskuesimerkissä: yhdistä ThisWorkbook.Path ja nimi, joka sisältää targetMonth, jotta jokainen ajo tuottaa erillisen tiedoston eikä ylikirjoita samaa joka kerta.
Sijoituksella on väliä: rivin pitää tulla suodatus/päivitys/muotoilu-vaiheiden jälkeen, mutta ennen lopullista MsgBox-vahvistusta — muuten vahvistusviesti ilmestyisi ennen kuin tiedosto on oikeasti olemassa.

Ratkaisu

Option Explicit

Sub GenerateMonthlyReport()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim chtObj As ChartObject
    Dim printRange As Range
    Dim targetMonth As String

    Set ws = ThisWorkbook.Worksheets("Reports")
    Set tbl = ws.ListObjects("tblReports")
    targetMonth = "January"

    ' 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. Build a print area that covers the table AND the chart
    On Error Resume Next
    Set chtObj = ws.ChartObjects(1)
    If Not chtObj Is Nothing Then
        Set printRange = Union(tbl.Range, ws.Range(chtObj.TopLeftCell.Address, _
            chtObj.BottomRightCell.Address))
    Else
        Set printRange = tbl.Range
    End If
    On Error GoTo 0

    On Error Resume Next
    With ws.PageSetup
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintArea = printRange.Address
    End With
    On Error GoTo 0

    ' 5. Export the filtered report to PDF
    ws.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=ThisWorkbook.Path & "\" & targetMonth & "_Report.pdf", _
        Quality:=xlQualityStandard

    ' 6. Confirm completion
    MsgBox targetMonth & " report is ready — filtered, refreshed, and exported."
End Sub

Suorita kerran targetMonth = "March" ja kerran "January" — sinun pitäisi saada kaksi erillistä PDF-tiedostoa (March_Report.pdf ja January_Report.pdf) työkirjan viereen, kumpikin sisältäen oikean suodatetun datan ajon hetkellä.

Huomio

Jos näyttöön ilmestyy valintaikkuna, jossa pyydetään valitsemaan tulostin (sen sijaan että koodi epäonnistuisi suoraan), valitse mikä tahansa käytettävissä oleva vaihtoehto — mukaan lukien "Microsoft Print to PDF", "Microsoft XPS Document Writer" tai mikä tahansa muu luettelossa oleva tulostin, myös faksiajuri. Valitulla tulostimella ei ole tässä merkitystä; VBA tarvitsee vain jonkin tulostimen valittuna, jotta PageSetup-/vientiprosessi onnistuu, sillä Excel ohjaa nämä toiminnot tulostinjärjestelmän kautta taustalla riippumatta siitä, mikä tulostin on aktiivinen.

Oliko kaikki selvää?

Kiitos palautteestasi!

Osio 4. Luku 5

Kysy tekoälyä

Kysy mitä tahansa tai kokeile jotakin ehdotetuista kysymyksistä aloittaaksesi keskustelumme

Automaattisten Raporttien Rakentaminen