一年一度的年报审计即将开幕。作为审计程序中必不可少的一环,针对应收以及应付账款的函证程序是每年审计的保留项目。动辄上百份的询证函以及相应审计工作底稿记录多少 企业财务/事务所小朋友们的血泪往事;抑或是收入相关的审计调整之后,更新所有附注披露数据时,被一百多页财报所支配的恐惧让多少财务人从噩梦中惊醒。
财务自动化提供了完美的解决方案。
定义模板与字段,从统一数据源自动更新数据是MVC设计模式给我们的启示。以询证函为例,其中的可变字段无非 客户或供应商地址,被审计单位名称,函证截止日期,函证日期,余额以及往来金额,即 手动复制粘贴的内容。
先通过Word 域 来设定模板,定义可变字段 (View, 视图层) 并通过 Excel VBA调用 Word 给模板中的字段赋值来达成动态更新 (Controller 控制层)。基于Excel的数据处理以及存储的优势,工作表提供了最简便直接的数据源(Model, 模型层)。通过Office各组件之间的联动,并且通过统一的VBA的API进行功能上的整合,从而实现财务自动化的无限可能。这种模式,根据实际需求, 也可轻易拓展至Outlook Access 以及PPT。
说完宏观思路,我们就来着手进行代码层面的实现。由于此解决方案着眼于一类处理模式,因此代码的可扩展性是我们的核心考量之一。
第一步,采用 Word 域 定义模板。
关于域的详细介绍请看官方文档。
Word 中的域代码列表support.office.com域 本身就是一个极其强大的功能,在此不作展开。展示之用,我们只涉及到 DOCVARIABLE 这个域 。
示例中使用的DOCVARIABLE 域定义如下,对于变量名称为 addressee 的域
DOCVARIABLE addressee * MERGEFORMAT
以普通文本形式输入DOCVARIABLE 域之后,选中该定义并按下 Ctrl + F9, 会自动将文本转换成相应的域。
演示之用,定义如下字段
addressee 被函证对象,street 街道以及门牌,city城市以及邮编,auditee 被审计单位,year 审计年度
变量名称自定,通过变量名称,将视图以及数据源进行映射。
第二步,添加数据源
此例中只是给出数据源的一种形式,
第一行为模板中的字段名称,第一列为所需要生成的最终文件名称。
实际当中字段可能上百个,并且也可能单独存放在SQL文件或是数据库中。但都可以通过VBA转化成这种基本样式。
第三步,基于数据源自动生成所需文件
Sub
自动生成的文件
自动填充的字段
完美保留原始格式,并且与数据源完全统一,再也不用担心无聊的复制粘贴中出错。
按 Alt + F9 在Word中 切换 模板 编辑与显示 模式。
由于模板样式以及数据模式已经剥离出去,VBA操作只涉及到数据更新,因此代码本身相对而言十分明晰。
此处用到本人的 Dicts库,用于结构化读取存储在表中的数据。
processWdTpl 用于绑定相关变量到各模板,具体通过 doc.Variables.add 进行相关操作。
Github 库后附,对于 Dicts 相关功能请参考代码注释
6234456/Excel-VBA-Dictsgithub.com后附示例文件
http://qiou.eu/xl/xlTmpl.zipqiou.eu实践当中,这种解决方案有广泛的适用性。从Excel当中自动读取数据,依据简单的条件判断,从语料库选取预设评判中生成评述性的Word分析报告, 并且操作PPT生成相应图表,再通过Outlook定时发送至指定收件人,由于VBA这种MS Office通用编程语言的存在,使得全流程的自动化变成现实。
作为对于ERP系统的一种轻便的拓展,VBA的自动化的潜力等你来发掘。
加强版
杨风飒:Excel VBA 实战(10) -Excel/Word/Outlook/PPT 联动zhuanlan.zhihu.com