如何通过VBA获取Excel表格外部数据源文件名并写入单元格

你之前读取的属性不适用于Power Query（获取和转换数据）导入的外部工作簿数据源场景，可通过获取表格关联的工作簿连接字符串提取目标路径，参考代码如下：

Sub GetExternalSourceFile()
    Dim ws As Worksheet
    Dim objList As ListObject
    Dim conn As WorkbookConnection
    Dim connStr As String
    Dim filePath As String
    Dim fileName As String
    
    Set ws = ThisWorkbook.Sheets("Material Data")
    Set objList = ws.ListObjects("Materials")
    
    ' 获取表格关联的工作簿连接
    Set conn = objList.WorkbookConnection
    
    ' 读取OLEDB连接字符串
    connStr = conn.OLEDBConnection.Connection
    
    ' 提取连接字符串中Data Source对应的完整路径
    filePath = Split(Split(connStr, "Data Source=")(1), ";")(0)
    
    ' 若只需要文件名不需要完整路径，用下面的代码提取
    fileName = Mid(filePath, InStrRev(filePath, "\") + 1)
    
    ' 输出到指定单元格，按需选择输出完整路径还是文件名即可
    ThisWorkbook.Worksheets("Quote").Range("A15").Value = fileName
End Sub

补充说明 #

• 连接字符串中Data Source=后紧跟的就是外部数据源的完整存储路径，拆分后即可提取所需内容
• 如果数据源是通过旧版查询向导导入的，可增加conn.Type属性判断逻辑，根据连接类型读取对应字段即可

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