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

Excel 模板通常是工作簿，带有报告框架，通常由函数支持。 模板 (xltx) 可以反复使用而不会被数据污染。 用数据填充后，模板工作簿保存为 xlsx，保留 xltx 本身的原始状态。

在本练习中，我们将使用 VBA 代码打开和填充模板。 模板 可以被找寻到 开始 和使用的Excel宏可以找到 开始.

本文假定读者已显示开发人员功能区并且熟悉 VBA 编辑器。 如果没有，请谷歌“Excel Developer Tab”或“Excel Code Window”。

模板

首先，我们将构建一个虚拟模板，填充数据、数据透视表和图表，如下所示：

打开一个新的 Excel 文件。 将“Sheet1”重命名为“Chart”，将“Sheet2”重命名为“Data”

将以下文本（包括标题）复制到“数据”选项卡的 D1 中：

选择所有数据，包括列标题，并在“数据”工作表的 A1 插入数据透视表，如下所示。

使用数据透视表作为数据源，在“图表”选项卡上创建图表。

删除 D2:F15 中的数据。 无需重新设置数据透视表数据范围； 即使没有数据，也要让它填充。  

将工作簿另存为“VacancyTemplate.lt” 在宏工作簿所在的子目录中。 在保存期间对来自 Excel 的任何警报回答“否”。

我们还需要一个 Reports 子目录。 例如：

Excel报表 （xlsm 存储在这里）

|_模板 （这里存放xlxt）

       |_报告 （每个 xlsx 都保存在这里)

一旦保存为 lt, 关闭模板

宏

打开一个新的工作簿来保存我们的代码。 将“Sheet1”重命名为“Main”，将“Sheet2”重命名为“Database”。

在“Main”上放置一个按钮来驱动应用程序。

通常，数据是从数据库中检索的。 由于不是每个人都有方便的数据库，因此“数据库”表将模拟数据库表。

将本文开头的数据复制到 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 数据对象

为了模拟数据库读取，我们必须引用 ActiveX 库。在代码窗口中，通过“工具”>“引用”来完成此操作。

测试代码

将“Main”上的按钮分配给 子开放式工作簿。 将工作簿另存为“Populating Templates.xlsm”。

关闭工作簿，然后重新打开它。

按下按钮查看结果。 增加“数据库”中的数据行数，然后再次运行，查看图表是否已使用附加信息更新。

在上面的代码中，我们已经提前展示了模板， XL.Visible = True. 在实时环境中，这可以在最后完成，因此屏幕更新是不可见的。

应对数据灾难！

没有什么比精心开发的 Excel 文件崩溃、源文件损坏且没有备份更令人沮丧的了。在这种情况下，如果 Excel 无法恢复损坏的文件，那么除非你手头有工具可以恢复，否则所有已完成的工作都将丢失。 修复Excel 文件。

经常备份有价值的工作也是明智的。

作者简介：

Felix Hooker 是一位数据恢复专家 DataNumen, Inc.，它是数据恢复技术领域的世界领先者，包括 rar修复 和sql恢复软件产品。 欲了解更多信息，请访问 datanumen.com