Kako odpreti in izpolniti predlogo z Excel VBA

Predloge Excel so običajno delovni zvezki z okvirom poročanja, ki ga pogosto podpirajo funkcije. Predloge (xltx) je mogoče vedno znova uporabljati, ne da bi jih onesnažili s podatki. Po populaciji s podatki se delovni zvezek predloge shrani kot xlsx in ohrani prvotno stanje samega xltx.

V tej vaji bomo s kodo VBA odprli in zapolnili predlogo. Predloga mogoče najti tukaj in uporabljeni makro Excel je mogoče najti tukaj.

Ta članek predvideva, da ima bralec prikazan trak za razvijalce in da pozna urejevalnik VBA. V nasprotnem primeru prosimo za Google »Excel Developer Tab« ali »Excel Code Window«.

Predloga

Najprej bomo zgradili preskusno predlogo, zapolnjeno s podatki, vrtilno tabelo in grafikonom, kot sledi:

Odprite novo datoteko Excel. "Sheet1" preimenujte v "Chart" in "Sheet2" v "Data"

Kopirajte naslednje besedilo, vključno z naslovi, v D1 na zavihku "Podatki":

Izberite vse podatke, vključno z naslovi stolpcev, in vstavite vrtilno tabelo na A1 lista „Podatki“, kot je prikazano spodaj.

Na zavihku »Grafikon« ustvarite grafikon, pri čemer uporabite vrtilno tabelo kot vir podatkov.

Odstranite podatke iz D2: F15. Obsega podatkov vrtilne tabele ni treba ponastaviti; pustite, da je poseljeno, tudi če ni podatkov.  

Shranite delovni zvezek kot »VacancyTemplate.xltx. " v podimeniku tistega, v katerem naj bo delovni zvezek makra. Med shranjevanjem odgovorite z »Ne« na opozorila iz Excela.

Potrebovali bomo tudi podimenik Poročila. Na primer:

Excel poročila (tu je shranjen xlsm)

| _Predloge (tu je shranjen xlxt)

       | _Poročila (vsak xlsx tukaj shranjen)

Ko je shranjen kot xltx, zaprite predlogo

Makro

Odprite novo delovno knjigo, v kateri bo naša koda. "Sheet1" preimenujte v "Main" in "Sheet2" v "Database".

Postavite gumb na “Main” za vožnjo aplikacije.

Podatki se običajno pridobijo iz zbirk podatkov. Ker nimajo vsi priročnih baz podatkov, bo list »Baza podatkov« posnemal tabelo baze podatkov.

Kopirajte podatke z začetka tega članka v zavihek »Zbirka podatkov« na A1 ...

Kodeks

Spodnja struktura kode jasno opredeljuje procese:

• Pridobite podatke iz "baze podatkov";
• Odprite predlogo;
• Predlogo napolnite s podatki in ponastavite obseg podatkov vrtilne tabele;
• Shranite predlogo kot poročilo

Option Explicit
    'Create objects to represent the template workbook and worksheets
    Public wb As Object
    Public XL As Object
    Public connDB As New ADODB.Connection
    Public rs As ADODB.Recordset
    Public eRow As Integer
    Public eRec As Integer
    Public dDate As String

Sub openWorksheet()
    Call GetData
    Call OpenTemplate
    Call PopulateTemplate
    
    'Save template as datestamped xlsx
    On Error Resume Next
    dDate = Format(Now(), "yyyy.mm.dd")
    wb.SaveAs Filename:=ActiveWorkbook.Path & "\Reports\Vacancies" & dDate & ".xlsx", _
         FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

     Sheets("Main").Activate        'Move off the database tab
     wb.Activate            'Bring the chart to the fore
     Set wb = Nothing
     Set XL = Nothing
     Set rs = Nothing
     Set connDB = Nothing
End Sub

Sub GetData()
    'Emulate database retrieval
    If connDB.State = 1 Then connDB.Close
    Sheets("Database").Activate
    Sheets("Database").Range("A1").Select
    Selection.End(xlDown).Select
    
    eRec = ActiveCell.Row - 1 'establish how many records will be in the recordset
                     'This step won't be needed in a database environment
    eRow = ActiveCell.Row   'the end row, used later in the template
    
    connDB.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
      "Data Source=" & ActiveWorkbook.FullName & ";" & _
      "Extended Properties=Excel 12.0;"
        
    Set rs = New ADODB.Recordset
    rs.Open "Select top " & eRec & " * from [Database$]", connDB, , , adCmdText
End Sub

 Sub OpenTemplate()
    Set XL = CreateObject("Excel.Application")
    XL.Visible = True       'enables us to see what's happening on debug.
    XL.Workbooks.Add ActiveWorkbook.Path & "\Templates\VacancyTemplate.xltx"
    Set wb = XL.ActiveWorkbook             'the new workbook is referenced by "wb"
 End Sub
 
 Sub PopulateTemplate()
    wb.Sheets("Data").Activate
    wb.Sheets("Data").Range("D2").CopyFromRecordset rs
    wb.Sheets("Data").Range("A1").Select
    
    'resize the range driving the pivot table, using the eRow variable.
    wb.Sheets("Data").PivotTables("PivotTable1").ChangePivotCache wb. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Data!R1C4:R" & eRow & "C6", _
        Version:=xlPivotTableVersion14)
    wb.Sheets("Chart").Select

 End Sub

Podatkovni objekti ActiveX

Za simulacijo branja baze podatkov se moramo sklicevati na knjižnico Active X. To storimo v oknu kode prek Orodja>Sklici.

Preizkusite kodo

Dodelite gumb na “Main” Pod Openworkbook. Shranite delovni zvezek kot “Populling Templates.xlsm”.

ZAPRITE delovni zvezek in ga znova odprite.

Pritisnite gumb, da si ogledate rezultat. Povečajte število podatkovnih vrstic v “Podatkovni zbirki” in zaženite znova in preverite, ali je bil grafikon posodobljen z dodatnimi informacijami.

V zgornji kodi smo predlogo prikazali zgodaj, z XL.Visible = Res. V okolju v živo je to mogoče storiti čisto na koncu, tako da posodobitve zaslona niso vidne.

Ukvarjajte se s katastrofo podatkov!

Malo stvari je bolj frustrirajočih kot sesutje močno razvite Excelove datoteke, poškodba izvorne datoteke in brez varnostne kopije. V takih primerih, ko Excel ne more obnoviti poškodovane datoteke, se vse delo, opravljeno na njej, izgubi, razen če imate pri roki orodje za to. popraviti Excel datotek.

Preudarno je tudi pogosto podpirati dragoceno delo.

Uvod avtorja:

Felix Hooker je strokovnjak za obnovitev podatkov v DataNumen, Inc., ki je vodilna na svetu na področju tehnologij za obnovitev podatkov, vključno z popravilo rar-ja in sql programske izdelke za obnovitev. Za več informacij obiščite www.datanumen.com