如何列出指定目录下多个Excel文件的Power Query源依赖清单

针对你需要批量提取指定目录下Excel文件PQ关联数据源的需求，以下是两种可直接运行的实现方案：

VBA方案 #

你可以直接调用Excel自带的对象模型访问PQ查询内容，不需要解析底层文件，代码逻辑如下：

Sub 提取PQ数据源()
    Dim fso As Object
    Dim folderPath As String, file As Object
    Dim wb As Workbook
    Dim qry As WorkbookQuery
    Dim regEx As Object
    Dim matches As Object, match As Object
    Dim result As Collection
    Dim i As Integer
    
    ' 初始化工具对象
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set regEx = CreateObject("VBScript.RegExp")
    Set result = New Collection
    regEx.Global = True
    ' 正则匹配M代码中带Excel后缀的文件路径
    regEx.Pattern = "[""']([^""']*\.(xlsx|xlsm|xlsb|xls))[""']"
    ' 替换为你的目标目录路径
    folderPath = "C:\你的目标目录路径"
    
    ' 遍历目录下所有Excel文件
    For Each file In fso.GetFolder(folderPath).Files
        If LCase(fso.GetExtensionName(file.Name)) Like "xls*" Then
            ' 后台打开文件不显示
            Set wb = Workbooks.Open(file.Path, ReadOnly:=True, Visible:=False)
            ' 遍历当前文件所有PQ查询
            For Each qry In wb.Queries
                Set matches = regEx.Execute(qry.Formula)
                For Each match In matches
                    On Error Resume Next ' 重复值添加会报错，直接跳过实现去重
                    result.Add fso.GetFileName(match.SubMatches(0)), CStr(match.SubMatches(0))
                    On Error GoTo 0
                Next
            Next
            wb.Close SaveChanges:=False
        End If
    Next
    
    ' 结果输出到当前工作表A列
    For i = 1 To result.Count
        Cells(i, 1) = result(i)
    Next
End Sub

使用前需要先在Excel信任中心开启「信任对VBA工程对象模型的访问」，执行前替换代码里的目标目录路径即可。

Python方案 #

不需要依赖第三方解析库，直接用Python标准库解压读取xlsx包内的PQ定义文件即可，代码如下：

import os
import re
import zipfile
from xml.etree import ElementTree as ET

# 配置项，替换为实际目标目录
target_dir = r"C:\你的目标目录路径"
excel_suffix = ("xlsx", "xlsm", "xlsb")
# 正则匹配M代码中的Excel文件路径
path_pattern = re.compile(r"['\"]([^'\"]*\.(xlsx|xlsm|xlsb|xls))['\"]")
# PQ定义文件的XML命名空间
ns = {"m": "http://schemas.microsoft.com/DataMashup"}

result = set()

# 遍历目录下所有Excel文件
for root, _, files in os.walk(target_dir):
    for file in files:
        if file.lower().endswith(excel_suffix):
            file_path = os.path.join(root, file)
            try:
                with zipfile.ZipFile(file_path, "r") as zf:
                    # 筛选所有PQ查询的定义文件
                    query_files = [f for f in zf.namelist() if f.startswith("xl/queries/") and f.endswith(".xml")]
                    for qf in query_files:
                        with zf.open(qf) as f:
                            tree = ET.parse(f)
                            # 提取查询对应的M代码
                            formula_nodes = tree.findall(".//m:formula", ns)
                            for node in formula_nodes:
                                m_code = node.text
                                # 匹配所有Excel文件路径并提取文件名
                                matches = path_pattern.findall(m_code)
                                for match in matches:
                                    excel_file = os.path.basename(match[0])
                                    result.add(excel_file)
            except Exception as e:
                print(f"处理文件{file_path}失败: {str(e)}")

# 输出结果
print("提取到的PQ关联Excel文件清单：")
for item in sorted(result):
    print(item)

该代码不需要额外安装依赖，直接运行即可，兼容所有主流Excel格式。

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