使用VBA结合Adobe Acrobat XI Standard批量转换PDF为文本

任务目标 #

遍历Excel中的PDF URL列表，为每个URL生成完整的文本文件，解决部分PDF内容被识别为图片导致的文本缺失问题。

当前背景 #

此前采用Word直接打开PDF URL提取文本的方案，虽优于Power Query，但部分PDF首页文本会被识别为图片，导致提取的文本内容不完整。现在已安装Adobe Acrobat XI Standard并启用Adobe Acrobat 10.0类型库引用，计划通过先下载PDF到本地，再用Adobe Acrobat进行精准文本转换。

核心需求 #

• 从URL批量下载PDF到指定文件夹
• 使用Adobe Acrobat将PDF转换为文本文件
• 批量处理过程中标记成功/失败的URL，同时处理非Unicode字符

优化后的完整VBA代码 #

#If VBA7 Then
    Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
      Alias "URLDownloadToFileA" (ByVal pCaller As LongPtr, ByVal szURL As String, _
        ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As LongPtr) As Long
#Else
    Private Declare Function URLDownloadToFile Lib "urlmon" _
    Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, _
        ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
#End If

Sub BatchPDFURLToText()
    Dim ws As Worksheet
    Dim fileRoot As String, url As String
    Dim c As Range, pdfPath As String, textPath As String
    Dim isSuccess As Boolean
    
    ' Adobe Acrobat对象
    Dim AcroXApp As Acrobat.AcroApp
    Dim AcroXAVDoc As Acrobat.AcroAVDoc
    Dim AcroXPDDoc As Acrobat.AcroPDDoc
    Dim jsObj As Object
    
    ' 初始化工作表和存储路径
    Set ws = Worksheets("Data")
    fileRoot = ws.Range("C2").Value
    If Right(fileRoot, 1) <> "\" Then fileRoot = fileRoot & "\"
    
    ' 初始化Adobe Acrobat应用
    On Error Resume Next
    Set AcroXApp = CreateObject("AcroExch.App")
    If Err.Number <> 0 Then
        MsgBox "无法启动Adobe Acrobat，请确认已安装并启用类型库引用！", vbCritical
        Exit Sub
    End If
    On Error GoTo 0
    AcroXApp.Hide ' 隐藏Acrobat窗口
    
    ' 遍历URL列表
    For Each c In ws.Range("B2:B" & ws.Cells(Rows.Count, "B").End(xlUp).Row).Cells
        url = Trim(c.Value)
        isSuccess = False
        c.Interior.ColorIndex = xlColorIndexNone ' 重置单元格颜色
        
        If LCase(url) Like "http?:*" Then
            ' 定义PDF和文本文件路径
            pdfPath = fileRoot & "PDF_" & c.Offset(0, -1).Value & ".pdf"
            textPath = fileRoot & c.Offset(0, -1).Value & ".txt"
            
            Debug.Print "处理URL: " & url
            Debug.Print "PDF路径: " & pdfPath
            Debug.Print "文本路径: " & textPath
            
            ' 下载PDF文件
            If Not DownloadFile(url, pdfPath) Then
                c.Interior.Color = vbRed
                Debug.Print "下载失败"
                GoTo NextURL
            End If
            
            ' 转换PDF为文本
            On Error Resume Next
            Set AcroXAVDoc = CreateObject("AcroExch.AVDoc")
            If AcroXAVDoc.Open(pdfPath, "Acrobat") Then
                Set AcroXPDDoc = AcroXAVDoc.GetPDDoc
                Set jsObj = AcroXPDDoc.GetJSObject
                
                If Not jsObj Is Nothing Then
                    ' 保存为纯文本，处理非Unicode字符
                    jsObj.SaveAs textPath, "com.adobe.acrobat.plain-text"
                    isSuccess = True
                End If
                
                AcroXAVDoc.Close False
                Set AcroXAVDoc = Nothing
                Set AcroXPDDoc = Nothing
                Set jsObj = Nothing
            End If
            On Error GoTo 0
            
            ' 标记结果
            If isSuccess Then
                c.Interior.Color = vbGreen
                Debug.Print "转换成功"
                ' 可选：删除临时PDF文件，注释此行可保留PDF
                Kill pdfPath
            Else
                c.Interior.Color = vbRed
                Debug.Print "转换失败"
            End If
        End If
        
NextURL:
    Next c
    
    ' 清理Acrobat对象
    AcroXApp.Exit
    Set AcroXApp = Nothing
    MsgBox "批量处理完成！", vbInformation
End Sub

Function DownloadFile(sURL As String, sSaveAs As String) As Boolean
    ' 下载文件，返回是否成功
    DownloadFile = (URLDownloadToFile(0, sURL, sSaveAs, 0, 0) = 0)
End Function

代码说明 #

1. URL下载：使用URLDownloadToFileAPI从指定URL下载PDF到本地文件夹，下载失败则标记单元格为红色。
2. Adobe Acrobat集成：初始化Acrobat应用后循环处理PDF，通过JS对象将PDF保存为纯文本，解决Word方案的图片识别问题。
3. 错误处理：对Acrobat启动、PDF打开、文本保存等环节添加错误捕获，避免程序崩溃。
4. 非Unicode处理：借助Adobe Acrobat的原生转换能力，自动处理多数非Unicode字符，确保文本内容完整。
5. 结果标记：成功转换的URL单元格标记为绿色，失败则标记为红色，方便快速排查问题。
6. 临时文件清理：可选删除下载的临时PDF文件，注释掉Kill pdfPath即可保留PDF。

内容的提问来源于stack exchange，提问作者Nick