Hvordan åpne og fylle ut mal med Excel VBA

Excel-maler er vanligvis arbeidsbøker, med et rapporteringsrammeverk, ofte støttet av funksjoner. En mal (xltx) kan brukes om og om igjen uten å forurense den med data. Etter populasjon med data, lagres en malarbeidsbok som en xlsx, og bevarer jomfrutilstanden til selve xltx.

I denne øvelsen vil vi bruke VBA-kode for å åpne og fylle ut en mal. Malen kan finnes her. og Excel-makro som brukes kan bli funnet her..

Denne artikkelen forutsetter at leseren har utviklerbåndet vist og er kjent med VBA Editor. Hvis ikke, vennligst Google "Excel Developer Tab" eller "Excel Code Window".

Malen

Først bygger vi en dummy-mal, fylt med data, en pivottabell og et diagram, som følger:

Åpne en ny Excel-fil. Gi nytt navn til "Sheet1" til "Chart" og "Sheet2" til "Data"

Kopier følgende tekst, inkludert overskrifter, til D1 i "Data"-fanen:

Velg alle dataene, inkludert kolonneoverskrifter, og sett inn en pivottabell ved A1 på "Data"-arket, som vist nedenfor.

Lag et diagram på "Diagram"-fanen, bruk pivottabellen som en datakilde.

Fjern dataene i D2:F15. Det er ikke nødvendig å tilbakestille pivottabellens dataområde; la den være fylt ut selv om det ikke er data.  

Lagre arbeidsboken som "VacancyTemplate.xltx." i en underkatalog til den der makroarbeidsboken skal ligge. Svar "Nei" på alle varsler fra Excel under lagringen.

Vi trenger også en underkatalog for rapporter. For eksempel:

Excel-rapporter (xlsm lagret her)

|_maler (xlxt lagret her)

       |_Rapporter (hver xlsx lagret her)

En gang lagret som en xltx, lukk malen

Makroen

Åpne en ny arbeidsbok for å holde koden vår. Gi nytt navn til "Sheet1" til "Main" og "Sheet2" til "Database".

Plasser en knapp på "Main" for å kjøre applikasjonen.

Normalt hentes data fra databaser. Siden ikke alle har en database tilgjengelig, vil "Database"-arket emulere en databasetabell.

Kopier dataene som finnes i begynnelsen av denne artikkelen til «Database»-fanen på A1…

Koden

Kodestrukturen nedenfor definerer klart prosessene:

• Få dataene fra "databasen";
• Åpne malen;
• Fyll malen med dataene og tilbakestill pivottabellens dataområde;
• Lagre malen 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-dataobjekter

For å simulere en databaselesing må vi referere til Active X-biblioteket. Gjør dette via Verktøy>Referanser fra kodevinduet.

Test koden

Tilordne knappen på "Main" til Under åpen arbeidsbok. Lagre arbeidsboken som "Populating Templates.xlsm".

LUKK arbeidsboken, og åpne den på nytt.

Trykk på knappen for å se resultatet. Øk antall datarader i "Database", og kjør på nytt for å se om diagrammet har blitt oppdatert med tilleggsinformasjonen.

I koden ovenfor har vi vist malen tidlig, med XL.Synlig = Sant. I live-miljøet kan dette gjøres helt på slutten, slik at skjermoppdateringer ikke er synlige.

Håndter datakatastrofe!

Få ting er mer frustrerende enn en velutviklet Excel-fil som krasjer, ødelegger kildefilen og uten en sikkerhetskopi tilgjengelig. I slike tilfeller, der Excel ikke klarer å gjenopprette den skadede filen, går alt arbeid som er gjort på den tapt med mindre du har et verktøy for hånden. fikse Excel filer.

Det er også klokt å ofte sikkerhetskopiere verdifullt arbeid.

Forfatterintroduksjon:

Felix Hooker er en datagjenopprettingsekspert innen DataNumen, Inc., som er verdensledende innen datagjenopprettingsteknologier, inkludert rar-reparasjon og sql-programvareprodukter. For mer informasjon besøk www.datanumen. Med