PDF转Excel批量转换报错：文件过大无法转换，求解决方案

原代码通过Word中转后一次性复制全部内容到Excel，大文件会触发Word/Excel的内存或容量限制，导致“PDF file too big for conversion to Excel”报错。以下是几种可行的解决方案：

方案一：用Excel原生Power Query批量导入（推荐，无Word依赖） #

Power Query是Excel内置的高效数据导入工具，对大PDF的支持远优于Word中转方案，无需额外依赖。

修改后的代码：

Sub BatchPDFToExcel_PowerQuery()
    Dim PDFFol As String, Targetdir As String
    Dim fso As New FileSystemObject
    Dim fo As Folder, f As File
    Dim wb As Workbook, ws As Worksheet
    
    '选择PDF所在文件夹
    PDFFol = GetFolder
    If PDFFol = "" Then Exit Sub
    
    Targetdir = PDFFol & Application.PathSeparator & "Converted"
    '创建输出文件夹
    If Not fso.FolderExists(Targetdir) Then MkDir Targetdir
    
    '记录路径到Sheet1
    ThisWorkbook.Sheets("Sheet1").Range("C2:C3") = Array(PDFFol, Targetdir)
    
    Set fo = fso.GetFolder(PDFFol)
    
    '遍历处理每个PDF
    For Each f In fo.Files
        If LCase(fso.GetExtensionName(f.Path)) = "pdf" Then
            Set wb = Workbooks.Add
            Set ws = wb.Sheets(1)
            
            '用Power Query导入PDF内容
            On Error Resume Next
            ws.QueryTables.Add( _
                Connection:="URL;" & f.Path, _
                Destination:=ws.Range("A1")).Refresh BackgroundQuery:=False
            On Error GoTo 0
            
            '保存并关闭工作簿
            wb.SaveAs Targetdir & Application.PathSeparator & Replace(f.Name, ".pdf", ".xlsx"), xlOpenXMLWorkbook
            wb.Close SaveChanges:=False
        End If
    Next f
    
    MsgBox "批量转换完成！", vbInformation
End Sub

'辅助函数：选择文件夹
Function GetFolder() As String
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    With fd
        .Title = "选择PDF所在文件夹"
        If .Show = -1 Then GetFolder = .SelectedItems(1)
    End With
    Set fd = Nothing
End Function

方案二：Word中转时逐页复制内容 #

如果必须依赖Word中转，可改为逐页复制内容，避免一次性加载全部大文件到内存：

Sub BatchPDFToExcel_PageByPage()
    Dim PDFFol As String, Targetdir As String
    Dim fso As New FileSystemObject
    Dim fo As Folder, f As File
    Dim wa As Object, doc As Object
    Dim wb As Workbook, ws As Worksheet
    Dim pageCount As Integer, i As Integer
    
    PDFFol = GetFolder
    If PDFFol = "" Then Exit Sub
    
    Targetdir = PDFFol & Application.PathSeparator & "Converted"
    If Not fso.FolderExists(Targetdir) Then MkDir Targetdir
    
    ThisWorkbook.Sheets("Sheet1").Range("C2:C3") = Array(PDFFol, Targetdir)
    
    '后台启动Word，减少资源占用
    Set wa = CreateObject("Word.Application")
    wa.Visible = False
    
    Set fo = fso.GetFolder(PDFFol)
    
    For Each f In fo.Files
        If LCase(fso.GetExtensionName(f.Path)) = "pdf" Then
            Set doc = wa.Documents.Open(f.Path, False, Format:="PDF Files")
            pageCount = doc.ComputeStatistics(wdStatisticPages)
            
            Set wb = Workbooks.Add
            Set ws = wb.Sheets(1)
            
            '逐页复制内容到Excel
            For i = 1 To pageCount
                doc.Bookmarks("\Page").Range.Copy
                ws.Cells(ws.Cells(Rows.Count, 1).End(xlUp).Row + 1, 1).PasteSpecial xlPasteAll
            Next i
            
            wb.SaveAs Targetdir & Application.PathSeparator & Replace(f.Name, ".pdf", ".xlsx")
            wb.Close SaveChanges:=False
            doc.Close SaveChanges:=False
        End If
    Next f
    
    wa.Quit
    MsgBox "转换完成！", vbInformation
End Sub

额外注意事项 #

• 方案一要求Excel 2016及以上版本（Power Query为默认功能），方案二需安装Word
• 大文件转换前关闭其他占用内存的程序，避免内存不足
• 扫描版PDF无法通过文本转换，需额外OCR工具支持

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