Ako otvoriť a naplniť šablónu pomocou programu Excel VBA

Šablóny programu Excel sú zvyčajne zošity s rámcom prehľadov, ktoré často podporujú funkcie. Šablóny (xltx) je možné používať opakovane bez toho, aby ste ich znečisťovali údajmi. Po naplnení údajmi sa zošit šablóny uloží ako súbor xlsx, čím sa zachová panenský stav samotného súboru xltx.

V tomto cvičení použijeme kód VBA na otvorenie a vyplnenie šablóny. Šablóna možno nájsť tu a použité Excel Makro nájdete tu.

Tento článok predpokladá, že čitateľ má zobrazenú pásku pre vývojárov a je oboznámený s editorom VBA. Ak nie, navštívte Google kartu „Vývojár Excel“ alebo „Okno kódu Excel“.

Šablóna

Najskôr vytvoríme fiktívnu šablónu vyplnenú údajmi, kontingenčnou tabuľkou a grafom, a to nasledovne:

Otvorte nový súbor programu Excel. Premenujte „List1“ na „Graf“ a „List2“ na „Údaje“

Skopírujte nasledujúci text vrátane nadpisov do časti D1 na karte „Údaje“:

Vyberte všetky údaje vrátane nadpisov stĺpcov a vložte kontingenčnú tabuľku do bodu A1 listu „Údaje“, ako je uvedené nižšie.

Vytvorte graf na karte „Graf“ pomocou kontingenčnej tabuľky ako zdroja údajov.

Odstráňte údaje z D2: F15. Nie je potrebné resetovať rozsah údajov kontingenčnej tabuľky; nechajte ho vyplnený, aj keď nie sú k dispozícii žiadne údaje.  

Uložte zošit ako „VacancyTemplate.xltx. “ v podadresári toho, v ktorom sa má nachádzať zošit makra. Počas ukladania odpovedzte „nie“ na akékoľvek upozornenia z Excelu.

Budeme tiež potrebovať podadresár Reports. Napríklad:

Správy programu Excel (xlsm uložený tu)

| _šablóny (tu je uložený xlxt)

       | _Správy (každý xlsx tu bol uložený)

Po uložení ako xltx, zavrite šablónu

Makro

Otvorte nový zošit, v ktorom bude uložený náš kód. Premenujte „List1“ na „Hlavný“ a „List2“ na „Databáza“.

Ak chcete spustiť aplikáciu, umiestnite tlačidlo na „Hlavná“.

Za normálnych okolností sa údaje získavajú z databáz. Pretože nie každý má databázu po ruke, list „Databáza“ bude emulovať databázovú tabuľku.

Skopírujte údaje nájdené na start tohto článku na kartu „Databáza“ na A1 ...

Kódex

Štruktúra kódu nižšie jasne definuje procesy:

• Získajte údaje z „databázy“;
• Otvorte šablónu;
• Vyplňte šablónu údajmi a vynulujte rozsah údajov kontingenčnej tabuľky;
• Šablónu uložte ako správu

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

Dátové objekty ActiveX

Aby sme mohli simulovať načítanie databázy, musíme odkazovať na knižnicu Active X librarr. Urobíte to cez Nástroje> Odkazy v okne kódu.

Vyskúšajte kód

Priraďte tlačidlo na „hlavnej“ k Vedľajší openworkbook. Uložte zošit ako „Populating Templates.xlsm“.

ZATVORTE zošit a znova ho otvorte.

Stlačením tlačidla zobrazíte výsledok. Zvýšte počet riadkov údajov v databáze a znova spustite databázu, aby ste zistili, či bol graf aktualizovaný o ďalšie informácie.

V kóde vyššie sme šablónu zobrazili skôr, s XL.Visible = Pravda. V živom prostredí je to možné urobiť úplne na konci, takže aktualizácie obrazovky nie sú viditeľné.

Zaoberajte sa katastrofou dát!

Máločo je frustrujúcejšie ako zlyhanie veľmi vyvinutého súboru programu Excel, ktorý poškodí zdrojový súbor a nie je k dispozícii žiadna záložná kópia. V takých prípadoch, keď program Excel nedokáže obnoviť poškodený súbor, bude všetka práca na ňom vykonaná lost pokiaľ nemáte po ruke nejaký nástroj opraviť Excel súbory.

Je tiež rozumné často zálohovať hodnotnú prácu.

Úvod autora:

Felix Hooker je expert na obnovu dát v DataNumen, Inc., ktorá je svetovým lídrom v oblasti technológií obnovy dát, vrátane rar oprava a softvérové ​​produkty na obnovenie sql. Pre viac informácií navštívte www.datanumen. S