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

Direktoratet	JobRef	Kön
Barn och familj	CH SW2588	Kvinna
Barn och familj	CH RS2775	Kvinna
Barn och familj	CH SW2630	Kvinna
Barn och familj	CH RS2775	man
Barn och familj	CH CC2628	Kvinna
Barn och familj	CH HT2579	Kvinna
Grupphälsa	CW T (2559	Kvinna
Grupphälsa	CW QS2774	Kvinna
Grupphälsa	CW O2745	man
Miljö	EE SM2814	Kvinna
Miljö	EE IT2772	man
Miljö	EE SO2784	man
Resurser	RS CO2557	Kvinna
Resurser	RS HO2539	man

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 de data som finns på starden 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

Alternativ Explicit 'Skapa objekt för att representera mallens arbetsbok och kalkylblad Public wb Som Object Public XL Som Object Public connDB Som New ADODB.Connection Public rs Som ADODB.Recordset Public eRow As Integer Public eRec As Integer Public dDate As String Sub openWorksheet () Call GetData Call OpenTemplate Call PopulateTemplate 'Spara mall som datastämplad xlsx On Error Resume Next dDate = Format (Now (), "yyyy.mm.dd") wb.SaveAs Filnamn: = ActiveWorkbook.Path & "\ Reports \ Vacancies" & dDate & ".xlsx", _ FileFormat: = xlOpenXMLWorkbook, CreateBackup: = False Sheets ("Main"). Aktivera "Flytta från databasfliken wb.Activate" Ta diagrammet framåt Set wb = Nothing Set XL = Nothing Set rs = Nothing Set connDB = Nothing End Sub Sub GetData () 'Emulera databashämtning Om connDB.State = 1 Sedan connDB.Close Sheets ("Database"). Aktivera Sheets ("Database"). Område ("A1"). Välj markering. Slutet (xlDown). Välj eRec = ActiveCell.Row - 1 'fastställ hur många poster som kommer att finnas i postuppsättningen' Detta steg behövs inte i en databasmiljö eRow = ActiveCell.Row 'slutraden, som används senare i mallen connDB .Öppna "Provider = Microsoft.ACE.OLEDB.12.0;" & _ "Datakälla =" & ActiveWorkbook.FullName & ";" & _ "Utökade egenskaper = Excel 12.0;" Ställ in rs = Ny ADODB.Recordset rs.Öppna "Välj topp" & eRec & "* från [Databas $]", connDB,,, adCmdText End Sub Sub OpenTemplate () Ställ in XL = CreateObject ("Excel.Application") XL. Visible = True 'gör att vi kan se vad som händer vid felsökning. XL.Workbooks.Add ActiveWorkbook.Path & "\ Templates \ VacancyTemplate.xltx" Set wb = XL.ActiveWorkbook 'the new workbook is refered by "wb" End Sub Sub PopulateTemplate () wb.Sheets ("Data"). Activate wb .Sheets ("Data"). Range ("D2"). CopyFromRecordset rs wb.Sheets ("Data"). Range ("A1") Välj "ändra storlek på det område som driver pivottabellen med hjälp av eRow-variabeln. wb.Sheets ("Data"). Pivottabeller ("Pivottabell1"). ChangePivotCache wb. _ PivotCaches.Create (SourceType: = xlDatabase, SourceData: = "Data! R1C4: R" & eRow & "C6", _ Version: = xlPivotTableVersion14) wb.Sheets ("Chart"). Välj End Sub

ActiveX-dataobjekt

För att simulera en databasläsning måste vi referera till Active X library. Gör detta genom Verktyg> Referenser från 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 mycket utvecklad Excel-fil som kraschar, skadar källfilen och utan säkerhetskopia tillgänglig. I sådana fall där Excel misslyckas med att återställa den skadade filen, är allt arbete som utförs på den lost om du inte har ett verktyg till hands 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