Hoe een sjabloon te openen en te vullen met Excel VBA

Excel-sjablonen zijn doorgaans werkmappen, met een rapportagekader, vaak ondersteund door functies. Een sjablonen (xltx) kunnen keer op keer worden gebruikt zonder deze te vervuilen met gegevens. Na het vullen met gegevens wordt een sjabloonwerkmap opgeslagen als een xlsx, waarbij de oorspronkelijke staat van de xltx zelf behouden blijft.

In deze oefening gebruiken we VBA-code om een ​​sjabloon te openen en in te vullen. Het sjabloon is te vinden hier en de gebruikte Excel-macro kan worden gevonden hier.

In dit artikel wordt ervan uitgegaan dat de lezer het ontwikkelaarslint heeft weergegeven en bekend is met de VBA-editor. Als dit niet het geval is, gebruik dan Google "Excel Developer Tab" of "Excel Code Window".

De sjabloon

Eerst bouwen we als volgt een dummysjabloon, gevuld met gegevens, een draaitabel en een diagram:

Open een nieuw Excel-bestand. Wijzig de naam van "Blad1" in "Diagram" en "Blad2" als "Gegevens"

Kopieer de volgende tekst, inclusief koppen, naar D1 van het tabblad "Gegevens":

Selecteer alle gegevens, inclusief kolomkoppen, en voeg een draaitabel in op A1 van het blad "Gegevens", zoals hieronder weergegeven.

Maak een diagram op het tabblad "Diagram" en gebruik de draaitabel als gegevensbron.

Verwijder de gegevens in D2: F15. Het is niet nodig om het gegevensbereik van de draaitabel opnieuw in te stellen; laat het ingevuld, zelfs als er geen gegevens zijn.  

Sla het werkboek op als “VacatureTemplate.xltx. " in een submap van degene waarin de macrowerkmap zich zal bevinden. Reageer tijdens het opslaan met "Nee" op waarschuwingen van Excel.

We hebben ook een submap Rapporten nodig. Bijvoorbeeld:

Excel-rapporten (xlsm hier opgeslagen)

| _Templates (xlxt hier opgeslagen)

       | _Rapporten (elke xlsx wordt hier opgeslagen)

Eenmaal opgeslagen als een xltx, sluit u de sjabloon

De macro

Open een nieuwe werkmap om onze code te bewaren. Hernoem "Blad1" als "Hoofd" en "Blad2" als "Database".

Plaats een knop op "Hoofd" om de applicatie te sturen.

Normaal gesproken worden gegevens opgehaald uit databases. Aangezien niet iedereen een database bij de hand heeft, zal het “Database” -blad een databasetabel emuleren.

Kopieer de gegevens die aan het begin van dit artikel staan ​​naar het tabblad 'Database' bij A1…

De code

De onderstaande codestructuur definieert duidelijk de processen:

• Haal de gegevens uit de "database";
• Open de sjabloon;
• Vul de sjabloon met de gegevens en reset het gegevensbereik van de draaitabel;
• Sla de sjabloon op als een 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-gegevensobjecten

Om een ​​databaseleesbewerking te simuleren, moeten we de ActiveX-bibliotheek toevoegen. Dit doen we via Extra > Verwijzingen in het codevenster.

Test de code

Wijs de knop op "Hoofd" toe aan Sub Openwerkboek. Sla de werkmap op als "Sjablonen.xlsm vullen".

SLUIT de werkmap en open deze opnieuw.

Druk op de knop bekijk het resultaat. Verhoog het aantal gegevensrijen in "Database", en voer het opnieuw uit om te zien of het diagram is bijgewerkt met de aanvullende informatie.

In de bovenstaande code hebben we de sjabloon vroeg getoond, met XL.Visible = Waar. In de live-omgeving kan dit helemaal aan het einde worden gedaan, zodat schermupdates niet zichtbaar zijn.

Omgaan met gegevensrampen!

Er is weinig zo frustrerend als een Excel-bestand waar je al flink aan hebt gewerkt dat vastloopt, het bronbestand beschadigt en er geen back-up beschikbaar is. In zulke gevallen, wanneer Excel het beschadigde bestand niet kan herstellen, gaat al het werk dat eraan is gedaan verloren, tenzij je een tool bij de hand hebt om het te herstellen. Excel repareren bestanden.

Het is ook verstandig om regelmatig een back-up te maken van waardevol werk.

Auteur Introductie:

Felix Hooker is een expert op het gebied van gegevensherstel DataNumen, Inc., de wereldleider in technologieën voor gegevensherstel, waaronder zeldzame reparatie en sql-herstelsoftwareproducten. Voor meer informatie bezoek www.datanumen.com