如何使用Excel VBA打開和填充模板

Excel模板通常是工作簿，帶有報錶框架，通常受功能支持。 可以反複使用模板（xltx），而不會污染數據。 填充數據後，將模板工作簿另存為xlsx，以保留xltx本身的原始狀態。

在本練習中，我們將使用VBA代碼打開並填充模板。 模板 可以發現 此處 和使用的Excel宏可以找到 此處.

本文假定讀者已顯示“開發人員”功能區，並且熟悉VBA編輯器。 如果沒有，請使用Google“ Excel開發人員標籤”或“ Excel代碼窗口”。

模板

首先，我們將構建一個虛擬模板，其中填充了數據，數據透視表和圖表，如下所示：

打開一個新的Excel文件。 將“ Sheet1”重命名為“ Chart”，將“ Sheet2”重命名為“ Data”

將以下文本（包括標題）複製到“數據”選項卡的D1中：

選擇所有數據，包括列標題，然後在“數據”表的A1處插入數據透視表，如下所示。

使用數據透視表作為數據源，在“圖表”選項卡上創建圖表。

刪除D2：F15中的數據。 無需重置數據透視表數據范圍； 即使沒有數據，也要填充它。  

將工作簿另存為“ VacancyTemplate。lt。” 在宏工作簿所在的子目錄中。 在保存過程中，對來自Excel的任何警報響應“否”。

我們還將需要一個Reports子目錄。 例如：

Excel報表 （xlsm存儲在此處）

| _模板 （xlxt存儲在此處）

       | _報告 （每個xlsx保存在此處)

一旦另存為 lt，關閉模板

宏

打開一個新的工作簿來保存我們的代碼。 將“ Sheet1”重命名為“ Main”，將“ Sheet2”重命名為“ Database”。

在“ Main”上放置一個按鈕以驅動該應用程序。

通常，數據是從數據庫中檢索的。 由於並非所有人都可以使用數據庫，因此“數據庫”表將模擬數據庫表。

複製在s找到的數據tar將本文的t放入A1的“數據庫”標籤中…

守則

下面的代碼結構清楚地定義了過程：

• 從“數據庫”中獲取數據；
• 打開模板；
• 用數據填充模板並重置數據透視表數據范圍；
• 將模板另存為報告

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數據對象

為了模擬數據庫讀取，我們必須引用Active X庫rary。 通過代碼窗口中的“工具”>“參考”來執行此操作。

測試代碼

將“主”上的按鈕分配給 子Openworkbook。 將工作簿另存為“ Populating Templates.xlsm”。

關閉工作簿，然後重新打開它。

按下按鈕查看結果。 增加“數據庫”中的數據行數，然後再次運行，查看圖表是否已使用其他信息進行了更新。

在上面的代碼中，我們已經儘早顯示了模板， XL.Visible = 真。 在實時環境中，這可以在最後完成，這樣就不會顯示屏幕更新。

處理數據災難！

沒有什麼比發達的Excel文件崩潰，破壞源文件且沒有可用的備份副本更令人沮喪的了。 在這種情況下，如果Excel無法恢復損壞的文件，則對它所做的所有工作都是ost 除非您有方便的工具 修復Excel 文件。

經常備份有價值的工作也是謹慎的。

作者簡介：

Felix Hooker是的數據恢復專家 DataNumen，Inc.是數據恢復技術的全球領導者，包括 rar 修復 和sql恢復軟件產品。 欲了解更多信息，請訪問 萬維網。datanumen.COM