Hur man öppnar och fyller i mall med Excel VBA

Excel-mallar är vanligtvis arbetsböcker med ett rapporteringsramverk som ofta stöds av funktioner. En mall (xltx) kan användas om och om igen utan att förorena den med data. Efter population med data sparas en mallarbetsbok som en xlsx, vilket bevarar jungfruligt tillstånd i själva xltx.

I den här övningen använder vi VBA-kod för att öppna och fylla i en mall. Mallen kan hittas här. och Excel-makro som används kan hittas här..

Den här artikeln förutsätter att läsaren har utvecklarbandet och är bekant med VBA Editor. Om inte, vänligen Google "Excel-fliken för utvecklare" eller "Excel-kodfönstret".

Mallen

Först bygger vi en dummy-mall, fylld med data, en pivottabell och ett diagram, enligt följande:

Öppna en ny Excel-fil. Byt namn på "Sheet1" som "Diagram" och "Sheet2" som "Data"

Kopiera följande text, inklusive rubriker, till D1 på fliken "Data":

Markera alla data, inklusive kolumnrubriker, och infoga en pivottabell vid A1 på "Data" -bladet, som visas nedan.

Skapa ett diagram på fliken "Diagram" med hjälp av pivottabellen som datakälla.

Ta bort data i D2: F15. Det är inte nödvändigt att återställa pivottabellens dataintervall; lämna den befolkad även om det inte finns några data.  

Spara arbetsboken som “VacancyTemplate.xltx. ” i en underkatalog av den där makroarbetsboken ska finnas. Svara ”Nej” på alla varningar från Excel under sparandet.

Vi behöver också en underkatalog för rapporter. Till exempel:

Excel-rapporter (xlsm lagrad här)

| _Mallar (xlxt lagrad här)

       | _Rapporter (varje xlsx sparas här)

En gång sparat som en xltx, stäng mallen

Makroen

Öppna en ny arbetsbok för att hålla vår kod. Byt namn på "Sheet1" som "Main" och "Sheet2" som "Database".

Placera en knapp på "Main" för att köra applikationen.

Normalt hämtas data från databaser. Eftersom inte alla har en databas till hands kommer "Databas" -arket att emulera en databastabell.

Kopiera informationen som finns i början av den här artikeln till fliken ”Databas” på A1…

Koden

Kodstrukturen nedan definierar tydligt processerna:

• Hämta data från "databasen";
• Öppna mallen;
• Fyll mallen med data och återställ pivottabellens dataintervall;
• Spara mallen som en rapport

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-dataobjekt

För att simulera en databasläsning måste vi referera till Active X-biblioteket. Gör detta via Verktyg>Referenser i kodfönstret.

Testa koden

Tilldela knappen på "Main" till Under öppen arbetsbok. Spara arbetsboken som "Populating Templates.xlsm".

STÄNG arbetsboken och öppna den igen.

Tryck på knappen för att visa resultatet. Öka antalet datarader i "Databas" och kör igen, se om diagrammet har uppdaterats med tilläggsinformation.

I koden ovan har vi visat mallen tidigt, med XL.Visible = Sant. I den levande miljön kan detta göras i slutet, så att skärmuppdateringar inte syns.

Hantera datakatastrofer!

Få saker är mer frustrerande än en välutvecklad Excel-fil som kraschar, korrumperar källfilen och utan någon säkerhetskopia tillgänglig. I sådana fall, där Excel misslyckas med att återställa den skadade filen, går allt arbete som gjorts på den förlorat om du inte har ett verktyg till hands för att fixa Excel filer.

Det är också klokt att ofta säkerhetskopiera värdefullt arbete.

Författarintroduktion:

Felix Hooker är en dataåterställningsexpert i DataNumen, Inc., som är världsledande inom teknik för återställning av data, inklusive rar-reparation och mjukvaruprodukter för SQL-återställning. För mer information besök www.datanumen.com