Cum să deschideți și să completați șablonul cu Excel VBA

Șabloanele Excel sunt de obicei registre de lucru, cu un cadru de raportare, adesea susținut de funcții. Un șabloane (xltx) poate fi folosit din nou și din nou fără a-l polua cu date. După popularea cu date, un registru de lucru șablon este salvat ca xlsx, păstrând starea virgină a xltx-ului însuși.

În acest exercițiu vom folosi codul VBA pentru a deschide și a popula un șablon. Șablonul pot fi găsite aici și Macro Excel utilizate pot fi găsite aici.

Acest articol presupune că cititorul are afișată panglica pentru dezvoltatori și este familiarizat cu Editorul VBA. Dacă nu, vă rugăm să Google „Fila Dezvoltator Excel” sau „Fereastra Cod Excel”.

Şablonul

Mai întâi, vom construi un șablon inactiv, populat cu date, un tabel pivot și o diagramă, după cum urmează:

Deschideți un nou fișier Excel. Redenumiți „Sheet1” ca „Chart” și „Sheet2” ca „Date”

Copiați următorul text, inclusiv titlurile, în D1 din fila „Date”:

Direcţie JobRef Gen
Copii și familie CH SW2588 Femeie
Copii și familie CH RS2775 Femeie
Copii și familie CH SW2630 Femeie
Copii și familie CH RS2775 Masculin
Copii și familie CH CC2628 Femeie
Copii și familie CH HT2579 Femeie
Sănătate comunitară CW T(2559 Femeie
Sănătate comunitară CW QS2774 Femeie
Sănătate comunitară CW O2745 Masculin
Mediu inconjurator EE SM2814 Femeie
Mediu inconjurator EE IT2772 Masculin
Mediu inconjurator EE SO2784 Masculin
Resurse RS CO2557 Femeie
Resurse RS HO2539 Masculin

Selectați toate datele, inclusiv titlurile coloanelor și introduceți un tabel pivot la A1 din foaia „Date”, așa cum se arată mai jos.Introduceți un tabel pivot la A1 al fișei „Date”.

Creați o diagramă în fila „Grafic”, folosind tabelul pivot ca sursă de date.Creați o diagramă în fila „Grafic”.

Eliminați datele din D2:F15. Nu este necesar să resetați intervalul de date din tabelul pivot; lăsați-l populat chiar dacă nu există date.  Eliminați datele din D2:F15

Salvați registrul de lucru ca „VacancyTemplate.xltx.” într-un subdirector al celui în care urmează să se găsească registrul de lucru macro. Răspundeți „Nu” la orice alerte din Excel în timpul salvării.

De asemenea, vom avea nevoie de un subdirector Rapoarte. De exemplu:

Rapoarte Excel (xlsm stocat aici)

|_Șabloane (xlxt stocat aici)

       |_Rapoarte (fiecare xlsx salvat aici)

Odată salvat ca un xltx, închideți șablonul

Macro

Deschideți un nou registru de lucru pentru a păstra codul nostru. Redenumiți „Sheet1” ca „Main” și „Sheet2” ca „Database”.

Plasați un buton pe „Principal” pentru a conduce aplicația.

În mod normal, datele sunt preluate din baze de date. Deoarece nu toată lumea are o bază de date la îndemână, foaia „Bază de date” va emula un tabel al bazei de date.

Copiați datele găsite la start din acest articol în fila „Bază de date” la A1...Copiați datele găsite la Start din acest articol în fila Bază de date la A1

Codul

Structura de cod de mai jos definește clar procesele:

  • Obțineți datele din „baza de date”;
  • Deschide șablonul;
  • Completați șablonul cu datele și resetați intervalul de date din tabelul pivot;
  • Salvați șablonul ca raport
Opțiune Explicit „Creează obiecte pentru a reprezenta registrul de lucru șablon și foile de lucru Public wb ca obiect Public XL ca obiect Public connDB ca nou ADODB.Connection Public rs ca ADODB.Recordset Public eRow ca întreg Public eRec ca întreg Public dDate ca șir Sub openWorksheet() Call GetData Apel OpenTemplate Apel PopulateTemplate 'Salvare șablon ca datatamped xlsx La eroare Resume Next dDate = Format(Now(), "aaaa.mm.dd") wb.SaveAs Nume fișier:=ActiveWorkbook.Path & "\Rapoarte\Vacancies" & dDate & „.xlsx”, _ FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False Sheets(„Main”).Activați „Mutați din fila bazei de date wb.Activate „Aduceți diagrama în prim-plan Set wb = Nimic Set XL = Nimic Set rs = Nothing Set connDB = Nothing End Sub Sub GetData() 'Emulează preluarea bazei de date Dacă connDB.State = 1 Then connDB.Close Sheets("Database").Activate Sheets("Database").Range("A1").Select Selection. End(xlDown).Select eRec = ActiveCell.Row - 1 'stabiliți câte înregistrări vor fi în setul de înregistrări 'Acest pas nu va fi necesar într-un mediu de bază de date eRow = ActiveCell.Row 'rândul final, utilizat mai târziu în șablon connDB.Open „Provider=Microsoft.ACE.OLEDB.12.0;” & _ "Data Source=" & ActiveWorkbook.FullName & ";" & _ „Proprietăți extinse=Excel 12.0;” Set rs = New ADODB.Recordset rs.Open "Select top " & eRec & " * din [Database$]", connDB, , , adCmdText End Sub Sub OpenTemplate() Set XL = CreateObject("Excel.Application") XL. Vizibil = Adevărat „ne permite să vedem ce se întâmplă la depanare. XL.Workbooks.Add ActiveWorkbook.Path & „\Templates\VacancyTemplate.xltx” Set wb = XL.ActiveWorkbook „noul registru de lucru este referit prin „wb” End Sub Sub PopulateTemplate() wb.Sheets(„Date”).Activate wb .Sheets(„Date”).Range(„D2”).CopyFromRecordset rs wb.Sheets(„Date”).Range(„A1”). Selectați „redimensionați intervalul care conduce tabelul pivot, folosind variabila eRow”. wb.Sheets(„Date”).PivotTables(„PivotTable1”).ChangePivotCache wb. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Data!R1C4:R" & eRow & "C6", _ Version:=xlPivotTableVersion14) wb.Sheets("Chart").Select End Sub

Obiecte de date ActiveX

Pentru a simula o citire a bazei de date, trebuie să facem referire la lib. Active Xrary. Faceți acest lucru prin Instrumente> Referințe din fereastra de cod.Faceți referire la Active X Library

Testați Codul

Atribuiți butonul de pe „Principal”. Sub Caiet de lucru deschis. Salvați registrul de lucru ca „Populating Templates.xlsm”.

ÎNCHIDE registrul de lucru și redeschide-l.

Apăsați butonul pentru a vedea rezultatul. Măriți numărul de rânduri de date din „Bază de date” și rulați din nou, văzând dacă diagrama a fost actualizată cu informații suplimentare.

În codul de mai sus am arătat șablonul devreme, cu XL.Vizibil = Adevărat. În mediul live, acest lucru se poate face chiar la sfârșit, astfel încât actualizările ecranului să nu fie vizibile.

Faceți față dezastrului de date!

Puține lucruri sunt mai frustrante decât un fișier Excel mult dezvoltat care se prăbușește, corupând fișierul sursă și fără nicio copie de rezervă disponibilă. În astfel de cazuri, în care Excel nu reușește să recupereze fișierul deteriorat, toată munca efectuată pe acesta este lost cu excepția cazului în care aveți un instrument la îndemână repara Excel fișiere.

De asemenea, este prudent să faceți backup frecvent pentru lucrările valoroase.

Introducerea autorului:

Felix Hooker este un expert în recuperarea datelor DataNumen, Inc., care este lider mondial în tehnologiile de recuperare a datelor, inclusiv rar repara și produse software de recuperare sql. Pentru mai multe informații vizitați www.datanumen.com

2 răspunsuri la „Cum să deschideți și să completați șablonul cu Excel VBA”

  1. magnific post, foarte informativ. Ma intreb de ce specialistii opusi ai acestui sector nu observa acest lucru. Trebuie să-ți continui scrisul. Sunt încrezător, ai deja o bază excelentă de cititori!

Lasă un comentariu

Adresa ta de email nu va fi publicată. Câmpurile obligatorii sunt marcate *