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“:

riaditeľstvo JobRef Rod
Deti a rodina CH SW2588 Žena
Deti a rodina CH RS2775 Žena
Deti a rodina CH SW2630 Žena
Deti a rodina CH RS2775 Muž
Deti a rodina CH CC2628 Žena
Deti a rodina CH HT2579 Žena
Zdravie Spoločenstva CW T (2559 Žena
Zdravie Spoločenstva CW QS2774 Žena
Zdravie Spoločenstva CW O2745 Muž
prostredie EE SM2814 Žena
prostredie EE IT2772 Muž
prostredie EE SO2784 Muž
zdroje RS CO2557 Žena
zdroje RS HO2539 Muž

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.Vložte kontingenčnú tabuľku do A1 tabuľky s údajmi

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

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.  Odstráňte údaje z D2: F15

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 ...Skopírujte údaje nájdené v 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
Explicitná možnosť „Vytvoriť objekty, ktoré budú predstavovať zošit šablóny a pracovné hárky Verejné wb Ako objekt Verejné XL ako objekt verejné connDB ako nové ADODB. GetData Call OpenTemplate Call PopulateTemplate 'Uložiť šablónu ako datestamped xlsx Pri chybe Pokračovať ďalej dDate = Format (Now (), "yyyy.mm.dd") wb.SaveAs Názov súboru: = ActiveWorkbook.Path & "\ Reports \ Vacancies" & dDate & ".xlsx", _ FileFormat: = xlOpenXMLWorkbook, CreateBackup: = Falošné hárky ("hlavné"). Aktivovať 'Presunúť mimo kartu databázy wb.Activate' Presunúť graf do popredia Nastaviť wb = Nič nastaviť XL = Nič nastaviť rs = Nič nastavené connDB = Nič End Sub Sub GetData () 'Napodobniť načítanie databázy If connDB.State = 1 Potom connDB.Close Sheets ("Database"). Activate Sheets ("Database"). Range ("A1"). Select Selection. Koniec (xlDown). Vyberte eRec = ActiveCell.Row - 1 'určte, koľko záznamov bude v množine záznamov.' Tento krok nebude potrebný v prostredí databázy eRow = ActiveCell.Row 'koncový riadok, ktorý sa neskôr použije v šablóne connDB. .Otvorte „Poskytovateľ = Microsoft.ACE.OLEDB.12.0;“ & _ "Zdroj údajov =" & ActiveWorkbook.FullName & ";" & _ "Rozšírené vlastnosti = 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 'nám umožňuje vidieť, čo sa deje pri ladení. XL.Workbooks.Add ActiveWorkbook.Path & "\ Templates \ VacancyTemplate.xltx" Set wb = XL.ActiveWorkbook 'na nový zošit odkazuje "wb" End Sub Sub PopulateTemplate () wb.Sheets ("Data"). Aktivovať wb .Hárky („Údaje“). Rozsah („D2“). CopyFromRecordset rs wb.Sheets („Údaje“). Rozsah („A1“). Pomocou premennej eRow vyberte „veľkosť rozsahu, ktorý vedie kontingenčnú tabuľku. wb.Hárky („Údaje“). Kontingenčné tabuľky („PivotTable1“). ChangePivotCache wb. _ PivotCaches.Create (SourceType: = xlDatabase, SourceData: = "Údaje! R1C4: R" & eRow & "C6", _ Verzia: = xlPivotTableVersion14) wb.Hacety ("Graf"). Vyberte 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.Referencia The Active X Library

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

2 odpovede na „Ako otvoriť a vyplniť šablónu pomocou Excel VBA“

  1. veľkolepý post, veľmi informatívne. Pýtam sa, prečo si to opační špecialisti tohto sektora nevšimnú. Musíte pokračovať v písaní. Som si istý, že už teraz máte skvelú čitateľskú základňu!

Nechaj odpoveď

Vaša e-mailová adresa nebude zverejnená. Povinné položky sú označené *