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

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.

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

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.  

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 începutul acestui 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

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

Obiecte de date ActiveX

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

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 prea elaborat care se blochează, corupe fișierul sursă și nu are nicio copie de rezervă disponibilă. În astfel de cazuri, în care Excel nu reușește să recupereze fișierul deteriorat, toată munca depusă asupra acestuia se pierde, cu excepția cazului în care aveți la îndemână un instrument pentru a... 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 reparare rar și produse software de recuperare sql. Pentru mai multe informații vizitați www.datanumen.com