Excel VBA 应用案例分享/电子发票管理小助手/电子发票信息读取

本文于2023年5月31日首发于本人同名公众号：Excel活学活用，更多文章案例请搜索关注！

☆本期内容概要☆

• 发票文件选择，取得文件名
• VBA正则表达式
• PDF文件信息读取
• OFD文件信息读取

大家好，我是冷水泡茶，这几天一直在修改我的《电子发票管理系统》，这是我一年多以前做的一个应用，在公司里正常使用。搞这个系统的缘由很简单，发票数字化、电子化是一个不可阻挡的趋势，看看最近多家省市又开始全电发票试点的新闻就知道了。数字发票好处是很多，但是对我们财务人员来讲，却谈不上有多少好处。困难倒是不少，主要有两个，一是电子发票可以重复打印，如何避免重复报销入账（不管是有意还是无意），都是一个难题；二是电子数据归档问题，财政部和国家档案局都有明确规定，简要说来就是，各个会计主体、核算单位，都要把电子发票的数据文件保存下来，如果有ERP、OA等信息系统的，可以只保存电子文档，其他以打印出来的纸质发票报销入账的单位，要同时保存相应的电子发票文档，并且要求与会计凭证能够对应，方便查询，至少要把每个月的电子发票归类到一个独立的目录中，比如202305之类的。

虽然很多单位都没能做到这一点，好象也没有谁来查，但我们不能就这样糊弄着过吧，总得做点什么。

我看到有的财务是自己登记的，把发票号码输入到一个EXCEL表中，可以检查是否重复，但不仅工作量大，而且容易出错，万一号码登记错了呢？增加了重复报销的可能性，那不都变成财务的责任了？这样的事，咱是不干的；还有发票电子文档的收集、整理，那也是让人崩溃的事，所有这些，最后都集中到财务这来，真是压力山大呀。这咱也不能干。

事情还是要做的，那就寻找解决办法呗。

经过一段时间（具体多少日子记不清了），我终于完成了《电子发票管理系统》的设计，以EXCEL为客户端，Access为数据存储端，以共享文件夹为发票电子文档、Access数据库保存目录，实现每人可以在自己的电脑登录操作。基本流程是这样的：

1、报销人下载、打印好电子发票，然后登录系统，进入“发票登记”模块，填写发票的关键信息，选择电子发票文件完整路径。

2、填写完毕后保存，程序会检验发票号码是否重复，重复则不能保存。各种校验无误后，自动将电子发票的文档复制到共享的数据库文件夹里，同时可以自动打印“报销单”，这个功能是省事了。

3、报销人持报销单及发票打印件到财务审核，财务人员登录系统，进入“发票审核”模块，双击一条记录，可以打开发票电子文档，进行核对，核对无误后点审核。

4、财务审核后，报销人将报销单报领导审批（线下签字），审批完成后，交财务出纳处付款。出纳登录系统，进入“出纳付款”模块，列出已审核未付款的报销单，确认无误后点付款。

5、最后，财务记账归档，财务人员登录系统，进入“发票记账“模块，列出所有已付款未记账的报销单，对应到财务记账凭证号，把凭证号录入系统，这样可以方便地查询。整个流程结束。

看起来还是不错的，就这样运行了一年多，也没出什么问题。我是财务审核人，不忙的时候就看得仔细点，忙的时候就看得马虎些，自始至终没有审出一张有问题的发票来。但心里总是隐隐感觉有那么点不踏实。

本来也没有想去更新这个系统，直到我自己填了几次报销单，就一个字：烦！虽然我也搞了一些复制发票、项目选择的功能，以减轻录入发票的工作量，但是真的比较烦，工作量确实比较大。于是萌生了一个念头：要是能自动读取发票信息就好了！

于是就上网各种搜，“电子发票信息读取”、“EXCEL操作PDF”......

最后，搜到一个公众号文章：【电子发票台账T006（V1.1.3）】，他提供了文件下载，于是赶紧下载来看看。他的功能是是读取一个文件夹下所有发票文件，然后把关键信息登记下来，形成一个发票台账。

他的代码对我来说非常有价值，在此表示感谢。他的思路简要说明如下：

1、PDF文档可以转换成WORD文档，然后读取WORD文档的内容，运用正则表达式提取发票关键信息，要求电脑上装有“Adobe Acrobat Pro“。

2、OFD文档可以通过RAR解压，得到XML文档，然后读取XML文档内容，提取发票关键信息，要求电脑上装有RAR压缩软件。

扯了这么多，终于到我今天要分享的工具了“电子发票管理助手”。在“电子发票台账T006”在基础上，按照我自己需求，进行了重新编写：

1、参考的地方：

（A）总体思路，发票文件的读取、转化方法。

（B）部分正则表达式。

（C）其他

2、优化与改进的地方：

（A）RAR程序是否安装的判断与路径的直接获取。

（B）临时文件夹建立、删除方式。

（C）OFD文件的信息提取方式，并增加了最新发票格式的读取。

（D）正则表达式优化与扩展，增加了2种版本的发票读取。

（E）获取的发票信息采取变量方式存储。

下面来说一说我的“电子发票管理助手”设计过程与思路吧

1、定义一个过程 ReadInvoiceFile

Sub ReadInvoiceFile()
    On Error Resume Next
    Dim FileExtn As String
    Dim iRow As Integer
    InvoiceCode = ""
    InvoiceNo = ""
    SellerName = ""
    SellerTaxID = ""
    Amount = ""
    TaxAmount = ""
    invoiceDate = ""
    ItemName = ""
    BuyerName = ""
    currInvoiceFile = FileSelected
    If currInvoiceFile = "" Then Exit Sub


    FileExtn = GetExtn(currInvoiceFile)
    'Stop
    If FileExtn = ".pdf" Then
        Call ReadPDFInvoiceInfo
    ElseIf FileExtn = ".ofd" Then
        Call ReadOFDInvoiceInfo
    End If
    '发票信息写入工作表
    Sheets("Result").Activate
    With ActiveSheet
        iRow = .UsedRange.Rows.Count + 1
        Cells(iRow, 1) = BuyerName
        Cells(iRow, 2) = invoiceDate
        Cells(iRow, 3) = "'" & InvoiceCode
        Cells(iRow, 4) = "'" & InvoiceNo
        Cells(iRow, 5) = SellerName
        Cells(iRow, 6) = "'" & SellerTaxID
        Cells(iRow, 7) = ItemName
        Cells(iRow, 8) = Amount
        Cells(iRow, 9) = TaxAmount
        Cells(iRow, 10) = CDbl(Amount) + CDbl(TaxAmount)
        .Hyperlinks.Add Anchor:=.Cells(iRow, 11), _
        Address:=currInvoiceFile, _
        TextToDisplay:="打开文件"
    End With
    MsgBox "发票信息读取完毕，请仔细核对！ " & Chr(10) & "若有错误，请手工修改！" & Chr(10) & "空白部分，请手工填写！"
End?Sub

代码解析：

先把一些公共变量值清空，然后选择文件，根据文件后缀判断，是PDF的，我们运行ReadPDFInvoiceInfo过程，是OFD的，我们运行ReadOFDInvoiceInfo过程，这两个过程都是用来读取发票信息的，读取到的信息存到变量中，完成后，回到本过程再把发票信息写入工作表，并添加发票文件链接。

2、读取PDF文件过程ReadPDFInvoiceInfo，代码较多，我贴到第二条文章。

（1）设置临时文件夹c:\temp\，如果没有则创建。

tempFolder = "c:\temp\"
If?Dir(tempFolder,?vbDirectory)?=?""?Then
????MkDir?tempFolder
End?If

（2）创建 Acrobat 应用程序对象，打开PDF发票文件，转存为WORD文件。

'创建?Acrobat?应用程序对象
Set acrobatApp = CreateObject("AcroExch.App")
'创建 Acrobat AVDoc 对象
Set AcrobatAVDoc = CreateObject("AcroExch.AVDoc")
'打开选择的 PDF 文件
AcrobatAVDoc.Open currInvoiceFile, ""
acrobatApp.Hide
Set AcrobatPDDoc = AcrobatAVDoc.GetPDDoc
Set jsObj = AcrobatPDDoc.getjsobject
'创建 Word 应用程序对象
Set WordApp = CreateObject("Word.Application")
'关闭安全提示
WordApp.Application.AutomationSecurity = msoAutomationSecurityForceDisable
'将 PDF 转换为 Word
WordFilePath = tempFolder & Format(Time, "hhmmss") & ".docx"
jsObj.SaveAs WordFilePath, "com.adobe.acrobat.docx"
'关闭和清理 Acrobat
AcrobatAVDoc.Close 1
acrobatApp.Exit

（3）打开WORD文件，读取信息到变量wordContent，然后通过正则表达式提取相应的发票关键字信息存入变量。

（4）读取完成后，删除临时文件夹。这里建立了一个删除文件夹的过程，看看代码就知道是ChatGPT生成的，基本没改。

Sub DeleteDirectory(ByVal folderPath As String)
    On Error Resume Next
    Dim fs As Object
    Dim folder As Object
    Dim subFolder As Object
    Dim file As Object
    ' 创建 FileSystemObject
    Set fs = CreateObject("Scripting.FileSystemObject")
    ' 获取目录对象
    Set folder = fs.GetFolder(folderPath)
    ' 遍历目录中的子目录并递归删除
    For Each subFolder In folder.SubFolders
        DeleteDirectory subFolder.Path
    Next subFolder
    ' 删除目录中的文件
    For Each file In folder.Files
        file.Delete
    Next file
    ' 删除目录
    folder.Delete
    ' 释放对象引用
    Set file = Nothing
    Set subFolder = Nothing
    Set folder = Nothing
    Set fs = Nothing
End Sub

3、读取OFD文件过程ReadOFDInvoiceInfo，代码也不少，同样我贴到第二条文章。

（1）检查有没有RAR压缩文件，有就取得其安装路径，没有就退出程序。这里定义了一个自定义函数GetRARPath，也是ChatGPT贡献的

Function GetRARPath()
    Dim rarPath As String
    On Error Resume Next
    ' 打开WinRAR的注册表项
    rarPath = CreateObject("WScript.Shell").RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\WinRAR.exe\")
    If Err.Number = 0 Then
        ' 获取WinRAR的安装路径
        GetRARPath = rarPath
    Else
        ' RAR未安装
        GetRARPath = ""
    End If
    On Error GoTo 0
End Function

（2）把OFD文件解压到临时文件夹下，这里通过shell函数执行RAR解压命令，由于解压需要点时间，所以用了个Sleep函数暂停1秒，不然没完全解压就执行后面的代码会报错，虽然后面Do Until~Loop也是起等待的作用，在原来只有一种发票格式的情况下可行，后来增加了一种发票就不太行了，有时会报错，这里以后再优化：

destFolder = tempFolder & Format(Time, "hhmmss") & "\"
rarCmd?=?rarPath?&?"?X?"?&?currInvoiceFile?&?"?"?&?destFolder
Result?=?shell(rarCmd,?vbHide)
Sleep?1000?'?Delay?for?2?seconds?(2000?milliseconds)
'等待解压完成
Do?Until?Dir(destFolder?&?"Doc_0\Attachs\original_invoice.xml")?<>?""?_
    Or Dir(destFolder & "Doc_0\Pages\Page_0\Content.xml") <> ""
        DoEvents
Loop

（3）读取发票信息

 NewDOMD.Load destFolder & "Doc_0\Attachs\original_invoice.xml"
?Set?xmld?=?NewDOMD.DocumentElement.SelectSingleNode("//eInvoice")
?Set?SelectedNode?=?xmld.SelectSingleNode("//fp:InvoiceCode")
?InvoiceCode?=?SelectedNode.Text
?
......


NewDOMD.Load destFolder & "Doc_0\Pages\Page_0\Content.xml"
Set?xmld?=?NewDOMD.DocumentElement.SelectSingleNode("//ofd:Page")
Set?SelectedNode?=?xmld.SelectSingleNode("//ofd:TextObject[@ID='6922']/ofd:TextCode")
InvoiceCode?=?Left(SelectedNode.Text,?12)
InvoiceNo?=?Right(SelectedNode.Text,?8)

代码解析：

（A） NewDOMD.Load加载XML文件

（B）Set xmld取得一个节点

（C）Set SelectedNode选中一个具体的节点

（D）InvoiceCode = SelectedNode.Text ，把节点TEXT赋值给变量。

（E）开头是旧式的电子发票，有密码区的，后面的最近的数电发票，两者的结构是不一样的，有兴趣的朋友可以找来这样的发票解压后分析。新式发票没有发票代码，发票号码是20位，这里我们还是把它处理成12位发票代码和8位发票号码。

其他一些过程什么的就不说了，上面的示例代码仅为部分代码，并且后续可能会修改，感兴趣的朋友可以参看下一篇文章。好，今天就到这吧，欢迎点赞、留言、分享，谢谢大家，我们下期再会。

☆猜你喜欢☆

本文于2023年5月31日首发于本人同名公众号：Excel活学活用，更多文章案例请搜索关注！