Excel VBA 实战(9) - 与Word联动自动化生成报告

一年一度的年报审计即将开幕。作为审计程序中必不可少的一环，针对应收以及应付账款的函证程序是每年审计的保留项目。动辄上百份的询证函以及相应审计工作底稿记录多少 企业财务/事务所小朋友们的血泪往事；抑或是收入相关的审计调整之后，更新所有附注披露数据时，被一百多页财报所支配的恐惧让多少财务人从噩梦中惊醒。

财务自动化提供了完美的解决方案。

定义模板与字段，从统一数据源自动更新数据是MVC设计模式给我们的启示。以询证函为例，其中的可变字段无非 客户或供应商地址，被审计单位名称，函证截止日期，函证日期，余额以及往来金额，即 手动复制粘贴的内容。

先通过Word 域 来设定模板，定义可变字段 (View, 视图层) 并通过 Excel VBA调用 Word 给模板中的字段赋值来达成动态更新 (Controller 控制层)。基于Excel的数据处理以及存储的优势，工作表提供了最简便直接的数据源(Model, 模型层)。通过Office各组件之间的联动，并且通过统一的VBA的API进行功能上的整合，从而实现财务自动化的无限可能。这种模式，根据实际需求, 也可轻易拓展至Outlook Access 以及PPT。

说完宏观思路，我们就来着手进行代码层面的实现。由于此解决方案着眼于一类处理模式，因此代码的可扩展性是我们的核心考量之一。

第一步，采用 Word 域 定义模板。

关于域的详细介绍请看官方文档。

域 本身就是一个极其强大的功能，在此不作展开。展示之用，我们只涉及到 DOCVARIABLE 这个域 。

示例中使用的DOCVARIABLE 域定义如下，对于变量名称为 addressee 的域

DOCVARIABLE addressee \* MERGEFORMAT

以普通文本形式输入DOCVARIABLE 域之后，选中该定义并按下 Ctrl + F9, 会自动将文本转换成相应的域。

演示之用，定义如下字段

addressee 被函证对象，street 街道以及门牌，city城市以及邮编，auditee 被审计单位，year 审计年度

变量名称自定，通过变量名称，将视图以及数据源进行映射。

第二步，添加数据源

此例中只是给出数据源的一种形式，

第一行为模板中的字段名称，第一列为所需要生成的最终文件名称。

实际当中字段可能上百个，并且也可能单独存放在SQL文件或是数据库中。但都可以通过VBA转化成这种基本样式。

第三步，基于数据源自动生成所需文件

Sub main()
    Dim wd As Object
    Set wd = CreateObject("Word.Application")
    Dim d As New Dicts
    d.load valCol:=d.rng(2, d.x)
    d.label = Range(Cells(1, 2), Cells(1, d.x))
    Dim k
    Dim tplPath As String
    tplPath = "tpl.docx"
    For Each k In d.keys
        Call processWdTpl(wd.documents.Open(ThisWorkbook.Path & "\" & tplPath, 0, True), k, d, ThisWorkbook.Path & "\" & k)
    Next k
End Sub
Private Sub processWdTpl(ByRef doc As Object, ByVal e, ByRef data As Dicts, ByVal saveAsFile As String)
    Dim k
    For Each k In data.label.keys
        doc.Variables.add Name:=k, Value:=data.dict(e)(data.label.dict(k))
    Next k
    doc.SaveAs2 saveAsFile