Kako otvoriti i popuniti predložak s Excel VBA

Predlošci programa Excel obično su radne knjige s okvirom za izvješćivanje, često podržanim funkcijama. Predlošci (xltx) mogu se koristiti uvijek iznova bez zagađivanja podacima. Nakon punjenja podacima, radna knjiga predloška sprema se kao xlsx, čuvajući izvorno stanje samog xltx-a.

U ovoj vježbi koristit ćemo VBA kod za otvaranje i popunjavanje predloška. Predložak može se naći ovdje i Excel Macro koji se koristi mogu se pronaći ovdje.

Ovaj članak pretpostavlja da čitatelj ima prikazanu vrpcu za razvojne programere i da je upoznat s VBA uređivačem. Ako ne, molimo proguglajte “Excel Developer Tab” ili “Excel Code Window”.

Predložak

Prvo ćemo izraditi 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 stupaca, i umetnite zaokretnu tablicu na A1 lista "Podaci", kao što je prikazano u nastavku.

Napravite grafikon na kartici "Grafikon", koristeći zaokretnu tablicu kao izvor podataka.

Uklonite podatke u D2:F15. Nije potrebno resetirati raspon podataka zaokretne tablice; ostavite ga popunjenim čak i ako nema podataka.  

Spremite radnu knjižicu kao "VacancyTemplate.xltx.” u poddirektoriju onog u kojem će se nalaziti radna knjiga makroa. Odgovorite "Ne" na bilo kakva upozorenja iz Excela tijekom spremanja.

Trebat će nam i poddirektorij izvješća. Na primjer:

Excel izvješća (xlsm pohranjen ovdje)

|_Predlošci (xlxt pohranjeno ovdje)

       |_Izvješća (svaki xlsx spremljen ovdje)

Jednom spremljen kao xltx, zatvorite predložak

Makro

Otvorite novu radnu knjigu da biste sadržavali naš kod. Preimenujte “Sheet1” u “Main” i “Sheet2” u “Database”.

Postavite gumb na "Glavno" za pokretanje aplikacije.

Obično se podaci preuzimaju iz baza podataka. Budući da nema svatko bazu podataka pri ruci, list "Baza podataka" oponašat će tablicu baze podataka.

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

Kodeks

Struktura koda u nastavku jasno definira procese:

• Dobiti podatke iz “baze podataka”;
• Otvorite predložak;
• Popunite predložak podacima i poništite raspon podataka zaokretne tablice;
• Spremite predložak kao izvješće

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 podatkovni objekti

Kako bismo simulirali čitanje baze podataka, moramo se referencirati na Active X biblioteku. To možemo učiniti putem Tools>References u prozoru s kodom.

Testirajte kod

Dodijelite gumb na "Main" za Sub Openworkbook. Spremite radnu knjigu kao "Popunjavanje predložaka.xlsm".

ZATVORITE radnu knjigu i ponovno je otvorite.

Pritisnite gumb pogledajte rezultat. Povećajte broj redaka s podacima u "Bazi podataka" i pokrenite ponovno da biste vidjeli je li grafikon ažuriran dodatnim informacijama.

U gornjem kodu smo rano prikazali predložak, sa XL.Visible = Istina. U okruženju uživo to se može učiniti na samom kraju, tako da ažuriranja zaslona nisu vidljiva.

Nosite se s podatkovnom katastrofom!

Malo je stvari koje su frustrirajuće od pada dugo razvijane Excel datoteke, oštećenja izvorne datoteke i nedostupne 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 slika.

Također je mudro često sigurnosno kopirati vrijedan rad.

Uvod za autora:

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