如何编写VBA宏，通过Power Query动态提取不同PDF的表格数据

前置依赖 #

• Office版本要求为2019及以上/365，内置Power Query PDF连接器
• 需提前开启VBA权限：文件→选项→信任中心→信任中心设置→宏设置，勾选「信任对VBA工程对象模型的访问」

通用宏代码实现 #

核心逻辑：不硬编码PDF路径、页数、表格索引，先调用Power Query动态扫描PDF全量表格元数据，可灵活配置筛选规则适配不同PDF结构。

Sub 通用提取PDF表格数据()
    Dim pdfPath As String
    Dim fd As FileDialog
    Dim qry As WorkbookQuery
    Dim qryFormula As String
    Dim ws As Worksheet
    
    ' 1. 动态选择PDF文件，无需硬编码路径
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .Filters.Clear
        .Filters.Add "PDF文件", "*.pdf"
        .Title = "选择要提取的PDF文件"
        If .Show <> -1 Then Exit Sub
        pdfPath = .SelectedItems(1)
    End With
    
    ' 2. 清理旧查询与工作表，避免冲突
    On Error Resume Next
    ThisWorkbook.Queries("PDF动态提取结果").Delete
    ThisWorkbook.Worksheets("PDF提取结果").Delete
    On Error GoTo 0
    
    ' 3. 生成动态Power Query M公式，无硬编码页数/表格序号
    qryFormula = "let" & vbCrLf & _
                "    源 = Pdf.Tables(File.Contents(""" & pdfPath & """), [Implementation=""1.0""])," & vbCrLf & _
                "    // 可自定义筛选规则：示例为过滤空行、自动提升表头，可按需修改" & vbCrLf & _
                "    #'筛选有效表格' = Table.SelectRows(源, each Table.RowCount([Data])>2)," & vbCrLf & _
                "    #'展开表格数据' = Table.ExpandTableColumn(#'筛选有效表格', ""Data"", Table.ColumnNames(#'筛选有效表格'{0}[Data]))," & vbCrLf & _
                "    #'提升表头' = Table.PromoteHeaders(#'展开表格数据', [PromoteAllScalars=true])" & vbCrLf & _
                "in" & vbCrLf & _
                "    #'提升表头'"
    
    ' 4. 创建查询并加载数据到工作表
    Set qry = ThisWorkbook.Queries.Add(Name:="PDF动态提取结果", Formula:=qryFormula)
    Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws.Name = "PDF提取结果"
    
    With ws.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=PDF动态提取结果;Extended Properties=""""" _
        , Destination:=ws.Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [PDF动态提取结果]")
        .RefreshStyle = xlInsertDeleteCells
        .AdjustColumnWidth = True
        .Refresh BackgroundQuery:=False
    End With
    
    MsgBox "PDF提取完成，结果保存在「PDF提取结果」工作表，对应Power Query查询名为「PDF动态提取结果」"
End Sub

自定义适配规则 #

根据不同PDF的特征修改M公式的筛选逻辑即可，无需改动其他代码：

• 按页数筛选：添加each [Page] = 3条件，即可仅提取第3页的表格
• 按表头关键词筛选：添加each List.Contains(Table.ColumnNames([Data]), "订单编号")条件，即可仅提取含指定表头的表格
• 提取所有表格：删除筛选步骤，遍历源的所有条目，将每个表格加载到单独工作表即可

从Power Query获取数据的方法 #

其他VBA逻辑需要调用提取结果时，直接调用以下两种方式即可：

• 读取已加载的工作表数据：ThisWorkbook.Worksheets("PDF提取结果").ListObjects(1).DataBodyRange
• 直接调用查询结果：修改ThisWorkbook.Queries("PDF动态提取结果").Formula可动态调整查询规则，调用Refresh方法即可刷新数据

内容的提问来源于stack exchange，提问作者Ngo Thien Duyen