用VBA实现防伪开票系统数据导入用友软件

【摘要】在增值税纳税企业中，企业税务会计根据业务部门提出的申请，在开票系统中开具发票，销售会计则根据发票信息在财务软件中编制记账凭证。在这个过程中，税务会计需要将开票信息输入开票系统，销售会计需要将发票信息输入财务软件，原始数据的反复输入，既重复了工作，又降低了准确性。本文采用EXCELVBA技术，以增值税防伪税控开票子系统V7.23.10版本和用友U8软件为例，设计开发了一款将开票系统数据转换为财务系统数据的接口（以下简称数据转换接口），实现了开票系统数据向用友软件自动导入。
　　【关键词】VBA 防伪开票系统 用友软件 数据导入
　　一、数据转换接口的开发既具有必要性又具有可能性
　　增值税防伪税控系统是1994年国家税务总局联合航天工业总公司等部门研制开发的，作为国家金税工程的重要组成部分，其在全国各企业中普遍使用。防伪开票子系统，是增值税防伪税控系统的四个子系统之一，是专门用于企业开具专用发票的应用软件。然而，在V7.23.10之前的版本（以下简称旧版），防伪开票子系统在发票填开过程中只能采用手动填开的方式，且其开票数据不支持导出。因此，在销售业务中，税务会计需要将业务部门提交的开票信息输入开票系统开具发票，销售会计需要将已开具发票信息输入财务软件生成记账凭证。原始数据的重复输入造成工作繁琐、效率低下、出错率高等问题，同时也大大地消耗了企业的人力和时间。因此，设计一款数据转换接口，实现开票系统数据直接导入财务系统生成记账凭证，十分必要。
　　防伪开票子系统的升级为数据转换接口的开发提供了保障。实现数据导入，前提是获得需要导入的数据。然而，旧版防伪开票子系统是不支持已开具发票数据导出的，为此，在防伪开票子系统接口领域，出现了许多商业化软件。由于相关标准的缺失，各商业化接口价格差异较大，技术水平也良莠不齐，价格低的，纳税人担心接口是否会对原系统造成损害，后续服务是否及时；价格高的，对于中小企业纳税人来讲，是一笔额外的负担。然而，随着国家税务总局2014年第17号公告的发布，这个问题得到了较好地解决。公告中指出，为进一步减轻纳税人负担，优化纳税服务，国家税务总局决定对纳税人使用的增值税发票税控开票软件数据接口规范予以发布。根据公告的要求，新版的防伪开票子系统可以将已开具的发票数据从税控开票软件中导出（导出数据提供XLS和XML两种格式），满足纳税人内部管理信息系统与税控开票软件的衔接需要，数据接口规范的发布使得个人用VBA开发数据转换接口的成为了可能。
　　用友U8软件总账工具提供了良好的数据导入接口。在用友U8软件中，总账工具是总账系统中携带的一个应用子程序，它可以实现不同账套或同一账套不同机器之间的数据传递，总账工具中的凭证引入功能可以按标准格式将TXT文本文件引入到凭证库中。了解了标准格式TXT文件，用VBA技术将防伪开票系统导出的数据转换成标准文件，即可实现数据的引入。
　　VBA 是Visual Basic for Application的简称，可用来扩展Windows应用程序（特别是Excel）的功能。Excel与VBA的结合，可以使大量重复、繁琐的工作自动化，成倍地提高工作效率，在办公自动化中应用广泛。此外，VBA代码并不需要逐句编写，用户可通过录制宏来产生所需要的代码，然后对录制的代码稍加修改便可得到自己需要的代码，这大大降低了学习VBA的门槛。本文利用VBA处理表格方面的优越特性和易学性，将开票系统导出的XLS文件，加工处理后生成能够直接导入用友U8软件的标准格式TXT文件，实现开票数据的共享。
　　二、数据转换接口设计思路
　　首先，先从“防伪税控开票子系统”中导出开票数据。打开开票系统，点击“报税处理＼抄报税管理＼开票数据导出”功能，系统弹出文件导出对话框，输入月份、开票日期及购方信息等，点击“确定”按钮，在系统弹出的对话框中选择导出文件存放路径，输入文件名，选择保存类型为XLS，点击“保存”按钮即可完成数据导出，在导出文件存放路径下可以查看到导出的文件。
　　从开票系统导出的数据表（以下简称数据表）结构如下：第1至第5行为纳税企业的基本信息；第6行为发票各项目的名称，A～P列依次为：A发票代码，B发票号码，C购方企业名称，D购方税号，E银行账号，F地址电话，G开票日期，H单据号，I商品名称，J规格，K单位，L数量，M单价，N金额，O税率，P税额；第7行及以下为所导出发票的具体数据。以第8行为例，列示数据如下：
　　A8：000001，B8：01002，C8：B公司，D8：11B，E8：X银行，F8：XX市，G8： 2014-1-2，H8：00B，I8：商品2，J8：GG02，K8：EA，L8：10，M8：20.00，N8：200.00，O8：17%，P8：34.00
　　假设数据表所在工作表的名称为“发票数据”，在VBA中表示为Sheets（"发票数据"）。“发票数据”的第6行显示发票各项目名称，如发票代码、发票号码等，表的第7行及以下是对应项目的值，如C8单元格表示票号为“01002”的发票所对应的购方企业为“B公司”，C8单元格的值在VBA中表示为Cells（8，3），Cells两个参数8和3分别指单元格所在的行和列。根据“发票数据”中的数据，足以编制出对应的分录，以第8行为例，假设出售商品2为XX公司主营业务，记入“主营业务收入”科目，XX公司设置“应收账款”科目，该科目按客户分类核算，则该分录如下：
　　摘要：销售B公司商品2#1002
　　借：应收账款（1122）——B公司（11B） 234.00
　　贷：主营业务收入（6001） 200.00
　　应交税费——应交销项税（22210105）
　　34.00
　　在该分录中，“摘要”并不能直接获取，它是将几个单元格的值和一些其他字符重新组合而成，若以ZY表示摘要，则ZY=“销售”&Cells（8，3）&Cells（8，9）&“#”&Right（Cells（8，2），4），&为字符串连接符号，Right函数用于取发票号的后四位。
　　其次，需要了解用友软件可识别的标准格式TXT文件。标准格式TXT文件内容如下：第1行数据“填制凭证，V800”，第2行及以下数据“日期，类型，凭证号，附件张数，摘要，科目，借方 ，贷方，数量，外币，汇率，制单人，结算方式，发票号码，发生日期，部门，个人，客户，供应商”。
　　其中，“填制凭证，V800”是文件标识，表示该文件可被用友U8软件识别且执行“填制凭证”功能，该标识对每一个TXT文件只有一个，位于文件第一行。标识以下是该文件所包含的会计分录信息，其中：“日期”指填制凭证的日期，格式为“XXXX-XX-XX”；“类型”指凭证类型，值为“现”“银”“转”；“科目”指分录中对应的科目编码；“借方”“贷方”指借贷金额，每行数据只能包含“借方”“贷方”二者之一；“部门”“个人”“客户”“供应商”均指该项对应编码。在标准格式TXT文件中，每一行数据描述分录中某一会计科目的各项信息，由此不难得出，上文中提到的会计分录转换成标准格式TXT文件如下：
　　20X4-1-2，"转"，"1"，1，"销售B公司商品2#1002"，"1122"，234.00，，，，，"张三"，，"01002"，，""，""，"11B"，""，
　　20X4-1-2，"转"，"1"，1，"销售B公司商品2#1002"，"6001"，，200.00，，，，" 张三"，，""，，""，""，""，""，
　　20X4-1-2，"转"，"1"，1，"销售B公司商品2#1002"，"22210105"，，34.00，，，，" 张三"，，""，，""，""，""，""，
　　通过以上的分析得出，对于“发票数据”中的每行数据，在产生的标准格式TXT文件中对应3行数据。考虑构造一个辅助表格（简称为辅助表），在该表格的第1行A～S列依次输入：“日期”“类型”“凭证号”“附件张数”“摘要”“科目”“借方”“贷方”“数量”“外币”“汇率”“制单人”“结算方式”“发票号码”“发生日期”“部门”“个人”“客户”“供应商”，假设其所在工作表名为“凭证”。“凭证”的格式已类似于标准格式TXT文件，只要调用VBA中文件操作的相关函数，依次将表中的数据写入TXT文件中即可。
　　再次，考虑如何建立“发票数据”与“凭证”的联系。不难看出，“发票数据”中的第7行数据，对应“凭证”中第2、第3、第4行数据，分析这种对应关系，得出“发票数据”中的第i行数据（i>=7），对应“凭证”中的第3*i-19，3*i-18，3*i-17行数据。以“日期”为例，“发票数据”中“开票日期”在G列，“凭证”中“日期”在A列，假定凭证填制日期为开票日期，对于“发票数据”中的第i行数据，则有：
　　Sheets（“凭证”）.Cells（3*i-19，1）= Sheets（"发票数据"）.Cells（i，7）
　　Sheets（“凭证”）.Cells（3*i-18，1）= Sheets（"发票数据"）.Cells（i，7）
　　Sheets（“凭证”）.Cells（3*i-17，1）= Sheets（"发票数据"）.Cells（i，7）
　　“=”为赋值符号，含义为将等号右边的值赋给等号左边。
　　再来看一下借贷方金额的表示，“发票数据”中 N7单元格对应“凭证”中H3单元格，P7单元格对应H4单元格，N7与P7单元格数值加和对应G2单元格，于是对于“发票数据”中的第i行数据，则有：
　　Sheets（“凭证”）.Cells（3*i-19，7）= Sheets（"发票数据"）.Cells（i，14）+Sheets（"发票数据"）.Cells（i，16）
　　Sheets”凭证”）.Cells（3*i-18，8）= Sheets（"发票数据"）.Cells（i，14）
　　Sheets（“凭证”）.Cells（3*i-17，8）= Sheets（"发票数据"）.Cells（i，16）
　　同理，也可建立其他数据之间的对应关系。
　　最后，看一下如何将“凭证”中的数据输出到TXT文件。VBA中的“Print #语句”可以将EXCEL中的数据顺序写入文件，具体语法可参考VBA书籍。对于“凭证”中每一行数据，连接规则是：同行中不同单元格数据用“&”连接，保证其在输出TXT文件中位于一行；不同单元格之间以“，”分割，但若单元格值为字符串，则该单元格前后需添加“””。以Line2表示第2行数据，SDH表示"，" ，SYH表示""""，则：
　　Line2 = Sheets（"凭证"）.Cells（2， 1） &SDH
　　Line2 = Line2&SYH& Sheets（"凭证"）.Cells（2， 2）.&SYH&SDH
　　以此类推，通过循环赋值，可将第2行所有单元格的值赋给Line2，完成赋值后，使用Print#语句可将其输出到文件。
　　三、数据转换接口设计实例
　　以上分析厘清了接口制作的思路，并对制作过程中的关键点作了说明，为了更直观地了解整个过程，下面提供一个简化案例作为展示，对该案例，作如下假设：
　　公司每卖一批商品开具一张发票，开票系统中导出的工作表名称为“发票数据”，“发票数据”所在工作簿含有名称为“凭证”的工作表。
　　公司根据每张发票生成1张记账凭证，凭证类型为“转”，附件张数为2，填制日期为“开票日期”，制单人为“张三”。
　　公司出售商品为主营业务，主营业务科目编码为6001，公司应收账款按客户分类核算，应收账款科目编码为1122，客户编码为“购方税号”。
　　根据以上假设，做如下操作，打开存放开票信息的EXCEL表格，使用“Alt+F11”快捷键，进入VBA界面，点击“插入＼模块”子菜单，在插入的代码输入窗口中输入以下代码：
　Sub 数据转换接口（）
　　Dim Line_head As String
　　Dim sLine As String
　　Dim sFName As String
　　Dim iFNnumber As Integer
　　Const SDH As String = "，"
　　Const SYH As String = """"
　　On Error Resume Next
　　sFName = "E：＼凭证引入.txt"
　　sLine_head = "填制凭证，V800"
　　hmax = Sheets（"发票数据"）.Range（"A65 535"）. End（xlUp）.Row
　　With Sheets（"凭证"）
　　For i = 7 To hmax
　　.Cells（3 * i - 19， 6） = "2211"
　　.Cells（3 * i - 18， 6） = "6001"
　　.Cells（3 * i - 17， 6） = "22210105"
　　.Cells（3 * i - 19， 7） = Sheets（"发票数据"）.Cells（i， 14）
　　.Cells（3 * i - 19， 7） =.Cells（3 * i - 19， 7） + Sheets（"发票数据"）.Cells（i， 16）
　　.Cells（3 * i - 18， 8） = Sheets（"发票数据"）.Cells（i， 14）
　　.Cells（3 * i - 17， 8） = Sheets（"发票数据"）.Cells（i， 16）
　　.Cells（3 * i - 19， 14） = Sheets（"发票数据"）.Cells（i， 2）
　　.Cells（3 * i - 19， 18） = Sheets（"发票数据"）.Cells（i， 4）
　　For j = 0 To 2
　　.Cells（3 * i + j - 19， 1） = Sheets（"发票数据"）.Cells（i， 7）
　　.Cells（3 * i + j - 19， 2） = "转"
　　.Cells（3 * i + j - 19， 3） = i - 6
　　.Cells（3 * i + j - 19， 4） = 2
　　.Cells（3 * i + j - 19， 5） = "销售" & Sheets（"发票数据"）.Cells（i， 3）
　　.Cells（3 * i + j - 19， 5）= .Cells（3 * i + j - 19， 5） & Sheets（"发票数据"）.Cells（i， 9）
　　.Cells（3 * i + j - 19， 12） = "张三"
　　Next j
　　Next i
　　iFNnumber = FreeFile
　　Open sFName For Output As #iFNnumber
　　Print #iFNnumber， sLine_head
　　t = 2
　　Do
　　sLine = .Cells（t， 1） & SDH
　　sLine = sLine & SYH & .Cells（t， 2） & SYH & SDH
　　sLine = sLine & SYH & .Cells（t， 3） & SYH & SDH
　　sLine = sLine & .Cells（t， 4） & SDH
　　sLine = sLine & SYH & .Cells（t， 5） & SYH & SDH
　　sLine = sLine & SYH & .Cells（t， 6） & SYH & SDH
　　sLine = sLine & Format（.Cells（t， 7）， "0.00"） & SDH
　　sLine = sLine & Format（.Cells（t， 8）， "0.00"） & SDH
　　sLine = sLine & .Cells（t， 9） & SDH
　　sLine = sLine & .Cells（t， 10） & SDH
　　sLine = sLine & .Cells（t， 11） & SDH
　　sLine = sLine & SYH & .Cells（t， 12） & SYH & SDH
　　sLine = sLine & .Cells（t， 13） &S DH
　　sLine = sLine & SYH & .Cells（t， 14） & SYH & SDH
　　sLine = sLine & .Cells（t， 15） & SDH
　　sLine = sLine & SYH & .Cells（t， 16） & SYH & SDH
　　sLine = sLine & SYH & .Cells（t， 17） & SYH & SDH
　　sLine = sLine & SYH & .Cells（t， 18） & SYH & SDH
　　sLine = sLine & SYH & .Cells（t， 19） & SYH & SDH
　　Print #iFNnumber， sLine
　　t = t + 1
　　Loop Until IsEmpty（.Cells（t， 1））
　　End With
　　Close #iFNnumber
　　End Sub
　　点击“保存”按钮，关闭VBE界面，点击“开发工具＼代码＼宏”，在弹出的对话框中选中“数据转换接口”，点击“执行”，即可看到处理后的工作表，并且在E盘下找到“凭证引入.txt”文件。利用VBA的封装技术可以将以上的代码封装成dll文件或EXE文件，注册后即以按钮的形式存在于EXCEL选项卡中，点击按钮即可实现相应功能，在此不作详述。
　　点击“用友U890＼系统服务＼总账工具”，打开总账工具界面，在“数据源＼文件数据”处选择“E：＼凭证引入.txt”，双击“凭证引入”，即可将凭证引入到用友软件中。
　　四、小结
　　本文利用Excel VBA在处理表格方面的优越特性，将新版防伪开票子系统导出的数据加工处理，生成了能直接导入用友软件的标准格式TXT文件，减轻了会计人员账务处理的负担。然而，针对该问题本文更多地是提供一种处理思路，虽然文中给出了一个案例的完整代码，但所用案例建立在众多假设之上。在实际会计业务中，这些假设不一定全部满足，需要具体问题具体分析。尽管如此，本文提出的处理思路仍具有重要的理论价值和应用价值，各企业可根据企业的实际情况，结合VBA的强大功能，设计出适合于自己企业的数据转换接口。
　　参考文献
　　[1] 郭刚.Excel VBA 入门与应用典型实例[M].北京：北京希望电子出版社，2009.
　　[2] 韩良智.Excel 在财务管理与分析中的应用[M].北京：中国水利水电出版社，2004.
　　[3] 孙裕亮，等.增值税防伪税控系统设计存在的问题及改进措施[J].财会月刊，2012（3）.