VBA专题06-6：利用Excel中的数据自动化构建Word文档—Excel与Word整合示例3

fanjy

发布于 2019-10-09 14:51:26

2.1K0

发布于 2019-10-09 14:51:26

文章被收录于专栏：完美Excel完美Excel

示例3：从Excel中提取数据生成不同的Word报表

从前面的学习中，我们已经学会了使用书签将Excel中提取的数据放置到文档中指定的位置。下面的示例演示如何运用这些技巧，使用Excel分析得来的数据来生成多个报表。

如下图12所示的工作表，左侧是数据区域，右侧使用数据透视表来分析这些数据。

图12：数据工作表

现在，要生成3份Word文档，分别报告Central、East、West这三个部门的业绩。为方便编写程序，工作表中使用了名称来代替单元格或单元格区域：定义了一个名为rngBookMarks的书签区域I20:J22，与Word模板中的书签相对应；将单元格J20命名为ptrDivName，在程序中更新该单元格的内容。并且，该单元格内容更新后，使用查询VLookup函数来更新单元格J21和J22中的内容。

创建一个名为SalaryReport.dotx的Word文档模板，如下图13所示，在3个位置分别定义了3个书签，与Excel工作表中的数据一致，并且段落开头的词与书签DivName链接。

图13：SalaryReport.dotx模板文档

在Excel工作簿中，打开VBE，插入标准模块，输入代码：

' 从Excel获取数据填充Word模板
' 来源于Professional ExcelDevelopment(2nd Edition)
Sub WordGenerateDivisionSummaries()
   Dim wrdApp As Word.Application
   Dim wrdDoc As Word.Document
   Dim wrdrngBM As Word.Range
   Dim piDiv As Excel.PivotItem
   Dim rngBookmark As Excel.Range
   Dim sPath As String
   Dim sBookmarkName As String
   On Error GoTo ErrorHandler
    '启动Word
   Set wrdApp = CreateObject("Word.Application")
    '获取路径
   sPath = ThisWorkbook.Path & "\"
    '基于模板创建新文档
   Set wrdDoc = wrdApp.Documents.Add(Template:=sPath &"SalaryReport.dotx")
    '遍历数据透视表中的每个部门
   For Each piDiv In wksData.PivotTables(1).PivotFields("Division").PivotItems
        '填充部门名称单元格
       wksData.Range("ptrDivName") = piDiv.Value
        '更新该部门相应的数据
       wksData.Calculate
        '使用工作表数据填充模板中的书签
       For Each rngBookmark In wksData.Range("rngBookmarks").Rows
            '获取书签名
            sBookmarkName =rngBookmark.Cells(1, 1).Value
            '获取书签相关的Word范围区域
            Set wrdrngBM =wrdDoc.Bookmarks(sBookmarkName).Range
            '设置该区域的文本(这将删除该书签)
            wrdrngBM.Text =rngBookmark.Cells(1, 2).Text
            '重新创建书签以便下次循环
            wrdDoc.Bookmarks.Add sBookmarkName,wrdrngBM
       Next rngBookmark
        '更新与这些书签链接的字段
       wrdDoc.Fields.Update
        '保存填充好的文档
       wrdDoc.SaveAs sPath & "SalaryResults - " & piDiv.Value& ".doc"
   Next piDiv
   MsgBox "部门薪酬汇总报表已成功生成."
ErrorExit:
   On Error Resume Next
    '关闭Word文档
   wrdDoc.Close
   Set wrdDoc = Nothing
    '退出Word程序
   wrdApp.Quit False
   Set wrdApp = Nothing
   Exit Sub
ErrorHandler:
    '显示错误号和错误描述
    '在标题栏备注程序
   MsgBox "错误 " &Err.Number & vbLf & Err.Description, _
          vbCritical, "程序:WordGenerateDivisionSummaries"
   Resume ErrorExit
End Sub

注意，代码使用了后期绑定。运行代码后，每基于Word模板生成一份文档，都会修改模板中相应书签位置的内容并更新链接处的内容，以生成具体的文档。代码运行成功后，会弹出如下图14所示的信息提示。