Jak otevřít a naplnit šablonu pomocí aplikace Excel VBA

Šablony aplikace Excel jsou obvykle sešity s frameworkem pro vytváření sestav, často podporované funkcemi. Šablony (xltx) lze použít znovu a znovu, aniž byste je znečišťovali daty. Po naplnění daty se sešit šablony uloží jako xlsx, čímž se zachová panenský stav samotného xltx.

V tomto cvičení použijeme kód VBA k otevření a naplnění šablony. Šablona je k dispozici zde a použité makro Excel lze nalézt zde.

Tento článek předpokládá, že čtenář má zobrazený pás karet pro vývojáře a je obeznámen s editorem VBA. Pokud ne, prosím Google „Excel Developer tab“ nebo „Excel Code Window“.

Šablona

Nejprve vytvoříme fiktivní šablonu naplněnou daty, kontingenční tabulkou a grafem, a to následovně:

Otevřete nový soubor aplikace Excel. Přejmenujte „List1“ na „Graf“ a „List2“ na „Data“

Zkopírujte následující text, včetně nadpisů, do D1 na kartě „Data“:

Vyberte všechna data, včetně záhlaví sloupců, a vložte kontingenční tabulku na A1 listu „Data“, jak je uvedeno níže.

Vytvořte graf na kartě „Graf“ pomocí kontingenční tabulky jako zdroje dat.

Odstraňte data v D2: F15. Není nutné resetovat rozsah dat kontingenční tabulky; ponechte jej naplněný, i když nejsou k dispozici žádná data.  

Uložte sešit jako „VacancyTemplate.xltx. “ v podadresáři toho, ve kterém se má sešit makra nacházet. Během ukládání odpovězte „Ne“ na veškerá upozornění z aplikace Excel.

Budeme také potřebovat podadresář Zprávy. Například:

Zprávy aplikace Excel (xlsm zde uložen)

| _Šablony (xlxt zde uložen)

       | _Zprávy (každý xlsx zde uložen)

Po uložení jako xltx, zavřete šablonu

Makro

Otevřete nový sešit, ve kterém bude náš kód. Přejmenujte „List1“ na „Hlavní“ a „List2“ na „Databáze“.

Chcete-li spustit aplikaci, umístěte tlačítko na „Hlavní“.

Normálně se data načítají z databází. Protože ne každý má databázi po ruce, list „Databáze“ bude emulovat databázovou tabulku.

Zkopírujte data z počátku tohoto článku do záložky „Databáze“ v bodě A1…

Kodex

Struktura kódu níže jasně definuje procesy:

• Získejte data z „databáze“;
• Otevřete šablonu;
• Naplňte šablonu daty a resetujte rozsah dat kontingenční tabulky;
• Uložte šablonu jako sestavu

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

Datové objekty ActiveX

Abychom mohli simulovat čtení z databáze, musíme odkazovat na knihovnu Active X. To provedeme v okně kódu pomocí nabídky Nástroje>Odkazy.

Vyzkoušejte kód

Přiřaďte tlačítko na „Hlavní“ k Dílčí openworkbook. Uložte sešit jako „Population Templates.xlsm“.

ZATVORTE sešit a znovu jej otevřete.

Stisknutím tlačítka zobrazíte výsledek. Zvyšte počet datových řádků v „Databázi“ a spusťte znovu. Zjistíte, zda byl graf aktualizován o další informace.

Ve výše uvedeném kódu jsme šablonu ukázali brzy, s XL. Viditelné = Pravda. V živém prostředí to lze provést na samém konci, takže aktualizace obrazovky nejsou viditelné.

Vypořádejte se s katastrofou dat!

Jen málo věcí je frustrujících než zhroucení rozpracovaného souboru aplikace Excel, poškození zdrojového souboru a absence záložní kopie. V takových případech, kdy se aplikaci Excel nepodaří obnovit poškozený soubor, se veškerá práce s ním provedená ztratí, pokud nemáte po ruce nástroj, který by to umožnil. opravit Excel soubory.

Je také rozumné často zálohovat hodnotnou práci.

Úvod autora:

Felix Hooker je odborník na obnovu dat v oboru DataNumen, Inc., která je světovým lídrem v oblasti technologií pro obnovu dat, včetně oprava rar souborů a SQL softwarové produkty pro obnovu. Pro více informací navštivte www.datanumen.com