制造企业财务的VBA系统开发:以ADO连接金蝶ERP,管理物流运费与分类统计报表

8 次浏览

摘要

制造企业财务人员W先生*分享了学习VBA后开发物流运费登记系统的经历。文章详细介绍Excel窗体前端、基础资料缓存、ADO数据库连接,以及读取金蝶ERP出库信息、登记运费、批量更新和分类查询报表的设计。作者记录三个版本的迭代及使用反馈,也坦诚列出SQL拼接、连接参数存放和权限管理等待改进之处。运行规模与应用效果为作者自述。

作者:W先生* · 学员投稿;文中效果与效率为作者自述。

——GGG公司物流运费登记系统开发纪实

文 / W先生*(GGG公司 财务部) 2026年9月

摘 要:本人是一名70后、非计算机科班出身的制造企业财务人员。通过系统学习郑广学老师的《Excel VBA 175例实战》与附赠的《Excel函数408例》课程,从零基础起步,独立开发出连接公司金蝶ERP系统的“物流运费登记系统”。系统自2022年9月上线以来持续迭代三个版本,实现了运费登记、修改、批量更新与十类报表一键查询统计。本文记录开发历程、系统设计、关键技术与心得体会,供同样身处“业务数字化”一线的同仁参考。

关键词:Excel VBA;ADO;金蝶ERP;物流运费管理;二次开发

目 录

一、缘起:被运费台账“逼”出来的开发冲动 3

二、学习路径:175例VBA+408例函数 3

三、系统总体设计 4

3.1 设计思路 4

3.2 工作表结构 4

四、系统功能与使用 5

4.1 主菜单与功能总览 5

4.2 基础资料:一键同步ERP档案 6

4.3 日常操作:出库单查询、运费登记、修改与删除 6

4.4 批量查询与更新、报表统计、回货登记 8

五、关键技术解析 10

5.1 clsE8ADO:一个类模块打通三种数据库 10

5.2 与金蝶ERP的数据集成 11

5.3 运费登记的保存:查重、校验、写库 11

5.4 报表统计:让SQL干聚合的活 12

5.5 版本校验与权限控制 12

六、版本迭代:在真实使用中长出来的系统 12

七、应用成效与心得体会 13

7.1 应用成效 13

7.2 几点心得 14

八、结语 14

一、缘起:被运费台账“逼”出来的开发冲动

GGG公司是一家电子原材料加工制造企业,产品以内销、出口并举,物流方式涵盖公路整车、集装箱海运和客户自提。每一票货的陆运费、海运费、保险费、杂费、扣款、实际结算金额……分散在ERP的销售出库单、运输商的对账单和财务的付款记录里。

过去物流内勤的做法是:月底从ERP里把出库单导出来,对照运输商发来的对账单,在一张手工Excel台账里逐条登记、逐条核对。出一次差错就要整表返工,运输商一多、月份一长,查询统计基本靠“筛选+求和”,费时费力还容易出错。

我在公司财务部工作,深知找软件公司定制一套系统的报价,也清楚这种“小而关键”的需求往往排不上信息化预算。于是我心里冒出一个念头:能不能自己动手,用Excel+VBA做一套“够用、好用、改得动”的运费登记系统?

二、学习路径:175例VBA+408例函数

下这个决心之前,我的编程基础几乎为零——70后,大学学的不是计算机,工作后用Excel基本停留在函数和透视表层面。2022年,我购入了郑广学老师(Excel880站长)的《Excel VBA 175例实战教学》视频课程,课程附赠《Excel函数408例》实战教程。

这套课程最合我胃口的地方是“无理论、纯实战”:175个VBA案例逐行手写,从变量、循环、数组,到工作簿事件、UserForm窗体、类模块,再到ADO数据库连接,每个案例都解决一个具体问题;408例函数课程则把日常取数、汇总、查重的“手工活”先压缩了一遍。我的学习方法是:每天固定1小时,先自己照着需求写一遍,再对照老师的写法复盘,看不懂的地方反复看视频、在学员群里提问。

学到类模块和ADO部分时我明显感觉“开窍”了——原来Excel不只是表格,它可以是一个前端:窗体是录入界面,工作表是数据缓存,后台可以直连SQL Server。这直接催生了本文要讲的系统。

三、系统总体设计

3.1 设计思路

系统的定位很明确:不推翻现有流程,给物流内勤一个“顺手的前端”。设计上把握三条原则:

一是数据不动原始账。ERP(金蝶)里的销售出库单、客户与供应商等基础档案照旧维护,系统只读取、不回写ERP业务数据;运费登记数据写入专门建立的运费台账表 zdy_transfee,与ERP数据物理隔离,风险可控。

二是把专业活封装起来。数据库连接、SQL执行这些“程序员活”全部封装进通用类模块 clsE8ADO,窗体代码只管业务逻辑;连接参数(服务器、库名、账号)集中放在 sysinfo 参数表里,换环境只改一处。

三是操作要“傻瓜化”。打开文件即弹出登录窗体,非当前版本禁止使用;主菜单只有四个按钮:基础资料、日常操作、报表查询、系统管理。

制造企业财务的VBA系统开发:以ADO连接金蝶ERP,管理物流运费与分类统计报表

图1 系统总体架构

3.2 工作表结构

整个系统就是一个 .xlsm 文件,但内部按功能严格分区,核心工作表结构如表1所示。

表1 系统工作表结构一览

工作表 可见性 用途
main 可见 系统主菜单,含基础资料、日常操作、报表查询、系统管理四个入口
Fitem 隐藏 基础资料缓存:客户、供应商、车型、地区、车队、币别、税率、报关方式等,供窗体下拉列表调用
salestock 隐藏 从ERP提取的销售出库单临时区,作为运费登记的数据来源
transfee 可见 运费登记明细区(46个字段),保存后写入后台台账表
rpt 可见 运费查询结果区,支持按日期、客户、运输商、车队组合查询
sysinfo 隐藏 系统参数:数据库连接串、版本号、登录用户、各窗体默认查询条件
使用及版本更新说明 可见 面向使用者的操作说明与版本更新记录

四、系统功能与使用

4.1 主菜单与功能总览

双击打开工作簿,Workbook_Open 事件自动隐藏全部工作表并弹出登录窗体,校验用户名、密码与版本号后方可进入主菜单(图2)。菜单功能与《使用及版本更新说明》工作表的对应关系见图3。

制造企业财务的VBA系统开发:以ADO连接金蝶ERP,管理物流运费与分类统计报表

图2 系统主菜单(公司名称已作代称处理)

制造企业财务的VBA系统开发:以ADO连接金蝶ERP,管理物流运费与分类统计报表

图3 “使用及版本更新说明”工作表:功能菜单

4.2 基础资料:一键同步ERP档案

点击“基础资料”按钮,系统通过ADO一次性从金蝶ERP提取客户、供应商、车型、地区、物流车队、币别汇率、税率、报关方式、扣款种类、产成品等十类档案,刷新到 Fitem 表,作为各窗体下拉列表的数据源。档案维护仍在财务部ERP岗位上进行,物流端只“取数”,从机制上保证了口径一致。

4.3 日常操作:出库单查询、运费登记、修改与删除

“日常操作”是使用频率最高的模块(图4~图7):先在“出库单信息查询”页输入日期与客户条件,从ERP调取出库单列表;再到“运费登记”页选择单据,出库信息自动代入,只需补充车型、运输商和各项费用,含税转不含税、实际结算金额等字段点击即自动计算;保存时系统自动做单据号查重。修改需先经批量查询把记录提取出来,鼠标定位A列时窗体自动跟踪刷新;删除则输入流水号二次确认。

制造企业财务的VBA系统开发:以ADO连接金蝶ERP,管理物流运费与分类统计报表

图4 出库单信息查询:输入条件从ERP调取出库单

制造企业财务的VBA系统开发:以ADO连接金蝶ERP,管理物流运费与分类统计报表

图5 运费登记:出库信息自动代入,金额点击自动计算

制造企业财务的VBA系统开发:以ADO连接金蝶ERP,管理物流运费与分类统计报表

图6 修改功能:A列跟踪定位,红框内据实修改

制造企业财务的VBA系统开发:以ADO连接金蝶ERP,管理物流运费与分类统计报表

图7 删除功能:输入流水号调出记录后删除

4.4 批量查询与更新、报表统计、回货登记

回单日期、扣款信息、实际结算运输商等字段往往要等运输商月度对账后才能补齐,“批量查询与更新”专为这种集中性补录设计(图8):检索出待更新记录后直接在表格里修改,点“批量更新”一次写回数据库。报表模块内置地区车次统计、单位运费、供应商运费统计、客户运费统计、客户出库运费汇总/明细、运输商运费明细/结算明细、物流回单明细、回货运费明细共十类报表(图9),财务相关报表另设权限校验。针对回货场景,系统在V20220902版本增加了回货运费登记(图10、图11),单据编号由客户编码+单据日期自动生成。

制造企业财务的VBA系统开发:以ADO连接金蝶ERP,管理物流运费与分类统计报表

图8 批量查询与更新

制造企业财务的VBA系统开发:以ADO连接金蝶ERP,管理物流运费与分类统计报表

图9 报表查询与统计:十类报表一键生成

制造企业财务的VBA系统开发:以ADO连接金蝶ERP,管理物流运费与分类统计报表

图10 回货运费登记

制造企业财务的VBA系统开发:以ADO连接金蝶ERP,管理物流运费与分类统计报表

图11 客户检索:输入关键字从ERP客户列表中选择

五、关键技术解析

5.1 clsE8ADO:一个类模块打通三种数据库

系统最核心的“基础设施”,是课程中讲到的通用ADO工具类 clsE8ADO。它把 ADODB.Connection 和 Recordset 封装成一个类,实例化即自动连接;一个类多个实例还能同时连接多个数据库目标。OpenSqlServer、OpenExcel、OpenAccess 三个方法分别对接SQL Server、Excel文件和Access数据库,SqlToRng 把查询结果一次性写入单元格区域,SqlToArr 返回二维数组,SqlExcute 直接执行无返回集的SQL。本系统所有取数、写数都经由它完成。

代码1 clsE8ADO 通用数据库连接类(节选)

Public Conn As Object '连接对象(后期绑定,免引用)

Public Rst As Object '记录集

Public Sub OpenSqlServer(Ip, dbname, username, Password)

Conn.Close

Conn.Open Sheets("sysinfo").Range("B9") '连接参数集中配置在sysinfo表

End Sub

Public Sub OpenExcel(XlsFilePath) '把Excel文件当数据库连接

Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Extended Properties=Excel 12.0;Data Source=" & XlsFilePath

End Sub

Public Sub OpenAccess(AcFilePath) '连接Access数据库

Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & AcFilePath

End Sub

Sub SqlToRng(sql, rng As Range, Optional WithHeader As Boolean = False)

If Rst.State <> 0 Then Rst.Close

Rst.Open sql, Conn, 3, 1 '执行SQL获得记录集

If WithHeader = True Then '输出表头

For i = 0 To Rst.Fields.Count – 1

rng.Offset(0, i).Value = Rst.Fields(i).Name

Next

rng.Offset(1, 0).CopyFromRecordset Rst

Else

rng.CopyFromRecordset Rst '结果一次性写入单元格区域

End If

End Sub

有了这个类,一段“从ERP取供应商档案”的代码只剩下几行:拼SQL、开连接、落表。这正是类模块“一次封装、处处复用”的价值——对我这样的业余开发者尤其友好。

5.2 与金蝶ERP的数据集成

基础档案同步直接查询金蝶ERP云星空-style的数据表,例如供应商档案取自 t_bd_supplier 及其多语言表(代码2);销售出库单则调用ERP端预置的存储过程按日期、客户提取,二次开发不必碰ERP核心逻辑。

代码2 基础档案同步 v_supplier(节选,账号口令已脱敏)

Function v_supplier() '更新供应商档案

Dim myado As New clsE8ADO

myado.OpenSqlServer Sheet2.Range("b7"), Sheet2.Range("B8"), "***", "***"

Sheet3.Range("E2:G300").ClearContents

sqlcx = "select a.FNumber as 供应商编码, b.FShortName as 供应商简称," & _

"b.FName as 供应商名称 from t_bd_supplier a" & _

" inner join t_BD_Supplier_L b on a.FSupplierId=b.FSupplierId" & _

" and a.FUseOrgId=1 and FDOCUMENTSTATUS='c' and b.FLOCALEID=2052" & _

" order by 供应商编码"

Call myado.SqlToRng(sqlcx, Sheet3.[E2], True) '结果写入Fitem缓存表

End Function

5.3 运费登记的保存:查重、校验、写库

“保存”按钮背后是 transfeeadd 过程:先把窗体上四十多个控件的值经工作表过渡区整理为公共变量,自动计算不含税运费与实际结算金额,再做发货日期、客户、运输商等必填项校验和单据号查重,最后拼装INSERT语句写库(代码3)。

代码3 运费登记保存 transfeeadd(节选)

Function transfeeadd() '把登记的运费明细写入数据库

Dim myado As New clsE8ADO

运费明细表赋值 '窗体控件值 → 工作表过渡区

单据编号 = Sheet4.Range("B3").Value

'……(中间字段取值略)

不含税运费 = 运保费合计 / (1 + 税率)

实际结算金额 = 运保费合计 – 扣款金额

myado.OpenSqlServer Sheet2.Range("b7"), Sheet2.Range("B8"), "***", "***"

'单据号查重

sqlcc = "select top 1 单据编号 from zdy_transfee" & _

" where 单据编号='" & 单据编号 & "'"

Call myado.SqlToRng(sqlcc, Sheet2.[B17])

If 发货日期 = "" Or 客户编码 = "" Or 运输商编码 = "" Or _

车型柜型 = "" Or 销售部门 = "" Then

MsgBox ("发货日期或客户与运输商或车型柜型或销售部门不得为空")

Exit Function

End If

sqlcx = "insert into zdy_transfee values('" & 单据编号 & "', …)"

myado.SqlExcute sqlcx

End Function

5.4 报表统计:让SQL干聚合的活

十类报表的思路一致:窗体收集查询条件,sqlcxbuild 动态拼装WHERE子句,聚合统计全部交给SQL的GROUP BY完成,Excel只负责展示(代码4)。比如“供应商运费统计表”按月份、运输商、车队汇总物流运费与不含税运费;“单位运费”则用 CASE WHEN 防止除零错误,算出每千克、每米的单位运费,为与运输商议价提供了直接依据。

代码4 供应商运费统计表(节选)

Function 供应商运费统计表()

Dim myado As New clsE8ADO

myado.OpenSqlServer Sheet2.Range("b7"), Sheet2.Range("B8"), "***", "***"

Sheet7.Range("A2").CurrentRegion.ClearContents

sqlcxbuild '根据窗体条件动态拼装WHERE子句

sqlcx = "select month(发货日期) 月份,运输商简称,物流车队," & _

"sum(陆运费) 物流运费,sum(运保费合计) 运保费合计," & _

"sum(不含税运费) 不含税运费 from zdy_transfee " & sqlcc & _

" group by 运输商简称,month(发货日期),物流车队" & _

" order by month(发货日期),运输商简称"

Call myado.SqlToRng(sqlcx, Sheet7.[A2], True)

formrpt.Hide

End Function

5.5 版本校验与权限控制

考虑到多人使用、文件易拷贝扩散,系统在登录环节做了两道关卡:一是版本校验,把本地版本号与服务器端存储的最新版本号比对,不一致则拒绝进入,避免新旧版本混用造成数据口径混乱;二是权限标志,财务相关报表仅对权限为“A”的用户开放。配合《使用及版本更新说明》工作表的版本记录,每次升级改了什么、使用者该怎么操作,都有据可查。

六、版本迭代:在真实使用中长出来的系统

系统不是一次设计出来的,而是在使用反馈中一轮轮迭代出来的。《使用及版本更新说明》工作表里记录了三个正式版本(图12、表2)。

制造企业财务的VBA系统开发:以ADO连接金蝶ERP,管理物流运费与分类统计报表

图12 版本更新记录(使用及版本更新说明工作表)

表2 系统版本演进

序号 版本号 主要功能
1 V20220901 与ERP系统基础档案集成;出库退库运费登记、修改、删除、批量更新
2 V20220902 增加回货运费登记与明细功能
3 V20221006 修正运保费合计数不能按运输商变化的差错;出库单及报表查询自动代入上次查询方案

V20220901解决了“有没有”的问题;V20220902回应了业务上“回货也要算运费”的真实需求;V20221006则纯粹来自使用中的“报错”——某运输商的运保费合计没有随币别汇率联动,排查后修正了计算逻辑,并顺手把各查询窗体改进为自动记忆上次的查询方案。三个版本,正好对应一套小系统从上线到好用的完整路径:先跑通主流程,再补业务场景,最后打磨细节。

七、应用成效与心得体会

7.1 应用成效

系统自2022年7月数据起持续运行至今,运费台账累计登记流水4万人余条、字段46个,覆盖出库、退库、回货全场景。物流内勤月底对账从“翻台账、拉计算器”变成选条件、点按钮;管理层随时可以拿到按运输商、按客户、按地区的运费统计和单位运费,运费议价和供应商考核第一次有了数据抓手。作为开发者,我最大的成就感来自同事的一句话:“现在查运费,不用再找你要表了。”

7.2 几点心得

第一,非科班不是障碍,“场景+坚持”才是关键。我没有系统学过计算机,靠175个案例一个个啃下来,第一个可用版本用了约一个半月的业余时间。成年人的学习,见效最快的路径就是带着真实需求学。

第二,函数和VBA不是替代关系。408例函数课程解决“临时取数”的效率问题,VBA解决“固定流程”的自动化问题,先函数后VBA的顺序很适合业务人员进阶。

第三,好的类模块是业余开发者的“拐杖”。clsE8ADO把最难的数据库连接封装掉之后,剩下的开发就是“拼业务积木”,这也是我推荐每个VBA学习者尽早掌握类模块的原因。

第四,小系统也要讲工程化。版本校验、使用说明、更新记录这些“不起眼”的部分,恰恰是系统能在企业里活下来的原因——使用者信任一个“有 changelog 的工具”。当然,系统还有明显改进空间:SQL采用字符串拼接存在注入与出错风险,连接参数明文存放在工作表中,权限体系也较简单。这些已列入下一版本的改造计划,比如参数化查询、连接串加密和分级权限。

八、结语

回看这段经历:一次课程购买,换来的是一套运行四年、同事天天在用的系统,更是“普通人也能给自己造工具”的底气。感谢郑广学老师的课程——175个案例教的不只是代码,而是一套“把重复劳动交给程序”的思维方式。如果你也身处业务一线,被某张重复填报的表格困扰着,不妨从第一个VBA案例开始试一试。

制造企业财务的VBA系统开发:以ADO连接金蝶ERP,管理物流运费与分类统计报表插图12

工具名称:VBAYYDS语音编程助手
适合人群:常用Excel且希望自动化操作但不想深学VBA的职场人
体验方式:下载地址vbayyds.com
✨ 让Excel听懂你的需求,或许只需要一次尝试。
✨ 你的时间,值得用在更值得的事情上。
官网下载地址 vbayyds.com
淘宝搜索 vbayyds
购买方式 加微信EXCEL880B
制造企业财务的VBA系统开发:以ADO连接金蝶ERP,管理物流运费与分类统计报表插图13