Kako otvoriti i popuniti predložak pomoću Excel VBA

Excel predlošci su obično radne knjige, sa okvirom za izveštavanje, često podržanim funkcijama. Predlošci (xltx) se mogu koristiti iznova i iznova bez zagađivanja podacima. Nakon popunjavanja podacima, radna knjiga šablona se sprema kao xlsx, čuvajući prvobitno stanje samog xltx-a.

U ovoj vježbi ćemo koristiti VBA kod za otvaranje i popunjavanje šablona. Šablon može se naći OVDJE i Excel Macro koji se koristi može se pronaći OVDJE.

Ovaj članak pretpostavlja da čitač ima prikazanu traku programera i da je upoznat sa VBA Editorom. Ako ne, molimo Google "Excel Developer Tab" ili "Excel Code Window".

The Template

Prvo ćemo napraviti lažni predložak, popunjen podacima, zaokretnom tablicom i grafikonom, kako slijedi:

Otvorite novu Excel datoteku. Preimenujte “Sheet1” u “Chart” i “Sheet2” u “Data”

Kopirajte sljedeći tekst, uključujući naslove, u D1 kartice "Podaci":

Odaberite sve podatke, uključujući naslove kolona, ​​i umetnite stožernu tabelu na A1 lista „Podaci“, kao što je prikazano ispod.

Napravite grafikon na kartici „Grafikon“, koristeći zaokretnu tabelu kao izvor podataka.

Uklonite podatke u D2:F15. Nije potrebno resetovati opseg podataka zaokretne tabele; ostavite ga popunjenim čak i ako nema podataka.  

Sačuvajte radnu svesku kao „VacancyTemplate.xltx.” u poddirektoriju onog u kojem se nalazi makro radna knjiga. Odgovorite “Ne” na sva upozorenja iz programa Excel tokom spremanja.

Trebat će nam i poddirektorij Reports. Na primjer:

Excel Izvještaji (xlsm pohranjen ovdje)

|_Obrasci (xlxt pohranjen ovdje)

       |_Izvještaji (svaki xlsx sačuvan ovdje)

Jednom sačuvan kao xltx, zatvorite šablon

Makro

Otvorite novu radnu svesku da držite naš kod. Preimenujte “Sheet1” u “Main” i “Sheet2” u “Database”.

Postavite dugme na “Glavno” da pokrenete aplikaciju.

Obično se podaci preuzimaju iz baza podataka. Pošto nemaju svi pri ruci bazu podataka, list „Baza podataka“ će emulirati tabelu baze podataka.

Kopirajte podatke s početka ovog članka u karticu "Baza podataka" na A1...

Kodeks

Struktura koda u nastavku jasno definira procese:

• Uzmite podatke iz “baze podataka”;
• Otvorite šablon;
• Popunite predložak podacima i resetirajte raspon podataka zaokretne tablice;
• Sačuvajte šablon kao izveštaj

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

ActiveX objekti podataka

Da bismo simulirali čitanje baze podataka, moramo referencirati Active X biblioteku. To možemo učiniti putem Tools>References iz prozora koda.

Testirajte kod

Dodijelite dugme na “Main” za Sub Openworkbook. Sačuvajte radnu svesku kao “Populating Templates.xlsm”.

ZATVORI radnu svesku i ponovo je otvori.

Pritisnite dugme da vidite rezultat. Povećajte broj redova podataka u „Bazi podataka“ i pokrenite ponovo, da vidite da li je grafikon ažuriran dodatnim informacijama.

U kodu iznad prikazali smo predložak ranije, sa XL.Visible = Tačno. U živom okruženju to se može uraditi na samom kraju, tako da ažuriranja ekrana nisu vidljiva.

Suočite se s katastrofom podataka!

Malo je stvari koje su frustrirajuće od pada dugo razvijane Excel datoteke, oštećenja izvorne datoteke, a pritom nema dostupne sigurnosne kopije. U takvim slučajevima, kada Excel ne uspije oporaviti oštećenu datoteku, sav rad obavljen na njoj se gubi, osim ako nemate alat pri ruci za to. popraviti Excel datoteke.

Takođe je mudro da često pravite rezervnu kopiju vrednog rada.

Uvod za autora:

Felix Hooker je stručnjak za oporavak podataka DataNumen, Inc., koji je svjetski lider u tehnologijama za oporavak podataka, uključujući popravak rar-a i sql softverski proizvodi za oporavak. Za više informacija posjetite www.datanumen.com