So öffnen und füllen Sie Vorlagen mit Excel VBA

Excel-Vorlagen sind in der Regel Arbeitsmappen mit einem Berichtsframework, das häufig von Funktionen unterstützt wird. Eine Vorlage (xltx) kann immer wieder verwendet werden, ohne sie mit Daten zu verschmutzen. Nach dem Auffüllen mit Daten wird eine Vorlagenarbeitsmappe als xlsx gespeichert, wobei der jungfräuliche Status des xltx selbst erhalten bleibt.

In dieser Übung verwenden wir VBA-Code, um eine Vorlage zu öffnen und zu füllen. Die Vorlage gefunden werden kann werden auf dieser Seite erläutert und verwendetes Excel-Makro können gefunden werden werden auf dieser Seite erläutert.

In diesem Artikel wird davon ausgegangen, dass der Reader das Developer Ribbon angezeigt hat und mit dem VBA-Editor vertraut ist. Wenn nicht, klicken Sie auf Google "Excel Developer Tab" oder "Excel Code Window".

Die Vorlage

Zuerst erstellen wir eine Dummy-Vorlage mit Daten, einer Pivot-Tabelle und einem Diagramm wie folgt:

Öffnen Sie eine neue Excel-Datei. Benennen Sie "Sheet1" in "Chart" und "Sheet2" in "Data" um

Kopieren Sie den folgenden Text einschließlich der Überschriften in D1 der Registerkarte "Daten":

Wählen Sie alle Daten einschließlich der Spaltenüberschriften aus und fügen Sie eine Pivot-Tabelle an A1 des Blattes „Daten“ ein, wie unten gezeigt.

Erstellen Sie auf der Registerkarte "Diagramm" ein Diagramm, wobei Sie die Pivot-Tabelle als Datenquelle verwenden.

Entfernen Sie die Daten in D2: F15. Es ist nicht erforderlich, den Datenbereich der Pivot-Tabelle zurückzusetzen. Lassen Sie es ausgefüllt, auch wenn keine Daten vorhanden sind.  

Speichern Sie die Arbeitsmappe als "VacancyTemplate".XLTX. ” in einem Unterverzeichnis desjenigen, in dem sich die Makroarbeitsmappe befinden soll. Antworten Sie während des Speicherns auf Warnungen aus Excel mit „Nein“.

Wir benötigen auch ein Unterverzeichnis für Berichte. Zum Beispiel:

Excel-Berichte (xlsm hier gespeichert)

| _Template (xlxt hier gespeichert)

       | _Berichte (jedes xlsx hier gespeichert)

Einmal als gespeichert XLTXSchließen Sie die Vorlage

Das Makro

Öffnen Sie eine neue Arbeitsmappe, um unseren Code zu speichern. Benennen Sie "Sheet1" in "Main" und "Sheet2" in "Database" um.

Platzieren Sie eine Taste auf "Main", um die Anwendung zu steuern.

Normalerweise werden Daten aus Datenbanken abgerufen. Da nicht jeder eine Datenbank zur Hand hat, emuliert das Blatt „Datenbank“ eine Datenbanktabelle.

Kopieren Sie die Daten vom Anfang dieses Artikels in den Reiter „Datenbank“ in Zelle A1…

Der Code des Chamäleons

Die folgende Codestruktur definiert die Prozesse klar:

• Holen Sie sich die Daten aus der "Datenbank";
• Öffnen Sie die Vorlage.
• Füllen Sie die Vorlage mit den Daten und setzen Sie den Datenbereich der Pivot-Tabelle zurück.
• Speichern Sie die Vorlage als Bericht

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

Um einen Datenbankzugriff zu simulieren, muss die ActiveX-Bibliothek referenziert werden. Dies erfolgt über „Tools > Verweise“ im Codefenster.

Testen Sie den Code

Weisen Sie die Schaltfläche auf "Main" zu Unter Openworkbook. Speichern Sie die Arbeitsmappe als "Templates.xlsm füllen".

Schließen Sie die Arbeitsmappe und öffnen Sie sie erneut.

Drücken Sie die Taste, um das Ergebnis anzuzeigen. Erhöhen Sie die Anzahl der Datenzeilen in „Datenbank“ und führen Sie sie erneut aus, um festzustellen, ob das Diagramm mit den zusätzlichen Informationen aktualisiert wurde.

Im obigen Code haben wir die Vorlage frühzeitig mit gezeigt XL.Sichtbar = Wahr. In der Live-Umgebung kann dies ganz am Ende erfolgen, sodass Bildschirmaktualisierungen nicht sichtbar sind.

Umgang mit Datenkatastrophen!

Kaum etwas ist frustrierender, als wenn eine aufwendig bearbeitete Excel-Datei abstürzt, die Quelldatei beschädigt und keine Sicherungskopie vorhanden ist. In solchen Fällen, in denen Excel die beschädigte Datei nicht wiederherstellen kann, sind alle daran vorgenommenen Arbeiten verloren, es sei denn, man hat ein geeignetes Tool zur Hand. Beheben Sie Excel Dateien.

Es ist auch ratsam, häufig wertvolle Arbeit zu sichern.

Einführung des Autors:

Felix Hooker ist ein Datenrettungsexperte in DataNumen, Inc., das weltweit führend bei Datenwiederherstellungstechnologien ist, einschließlich seltene Reparatur und SQL Recovery-Softwareprodukte. Für weitere Informationen besuchen Sie www.datanumen.com €XNUMX