如何通过名称查找Python脚本路径并使用Excel宏调用

可以仅通过脚本名称查找其路径，下面是具体的实现方案和代码：

基础实现（仅遍历指定根目录） #

利用VBA的Dir函数在指定目录中匹配包含目标名称的.py文件，返回完整路径：

Private Sub button_Click()
    Dim vbash As Object

    Set vbash = VBA.CreateObject("Wscript.Shell")
    
    Dim pythonScriptPath As String
    pythonScriptPath = FindPythonScript()
    
    If pythonScriptPath <> "" Then
        MsgBox "找到Python脚本: " & pythonScriptPath, vbInformation, "成功"
        vbash.Run "cmd /c python """ & pythonScriptPath & """"
    Else
        MsgBox "未找到目标Python脚本", vbExclamation, "提示"
    End If
End Sub

Function FindPythonScript() As String
    Dim fileName As String
    Dim targetName As String
    Dim searchPath As String
    
    searchPath = "C:\" ' 指定搜索的根目录
    targetName = "Fulle_word_tamplat" ' 脚本名称关键词
    
    ' 查找目录中包含关键词的.py文件
    fileName = Dir(searchPath & "*" & targetName & "*.py")
    
    If fileName <> "" Then
        FindPythonScript = searchPath & fileName
    Else
        FindPythonScript = ""
    End If
End Function

扩展优化：递归遍历子目录 #

如果脚本可能存放在子目录中，可使用递归搜索遍历所有层级的目录：

Private Sub button_Click()
    Dim vbash As Object

    Set vbash = VBA.CreateObject("Wscript.Shell")
    
    Dim pythonScriptPath As String
    pythonScriptPath = FindPythonScript()
    
    If pythonScriptPath <> "" Then
        MsgBox "找到Python脚本: " & pythonScriptPath, vbInformation, "成功"
        vbash.Run "cmd /c python """ & pythonScriptPath & """"
    Else
        MsgBox "未找到目标Python脚本", vbExclamation, "提示"
    End If
End Sub

Function FindPythonScript() As String
    Dim targetName As String
    Dim rootPath As String
    targetName = "Fulle_word_tamplat"
    rootPath = "C:\"
    
    ' 调用递归搜索函数
    FindPythonScript = RecursiveSearch(rootPath, targetName)
End Function

Function RecursiveSearch(searchPath As String, targetName As String) As String
    Dim fileName As String
    Dim subDir As String
    Dim result As String
    
    ' 查找当前目录下的目标文件
    fileName = Dir(searchPath & "*" & targetName & "*.py")
    If fileName <> "" Then
        RecursiveSearch = searchPath & fileName
        Exit Function
    End If
    
    ' 遍历子目录
    subDir = Dir(searchPath, vbDirectory)
    Do While subDir <> ""
        ' 跳过当前目录和上级目录标记
        If subDir <> "." And subDir <> ".." Then
            ' 判断是否为目录
            If (GetAttr(searchPath & subDir) And vbDirectory) = vbDirectory Then
                result = RecursiveSearch(searchPath & subDir & "\", targetName)
                ' 找到结果则直接返回
                If result <> "" Then
                    RecursiveSearch = result
                    Exit Function
                End If
            End If
        End If
        subDir = Dir()
    Loop
    
    ' 未找到返回空字符串
    RecursiveSearch = ""
End Function

注意事项 #

• 确保Excel宏已启用，且当前用户对搜索目录有读取权限。
• 如果脚本名称唯一，可去掉通配符*，直接匹配全名（如Dir(searchPath & targetName & ".py")），提升搜索准确性。
• 递归遍历子目录会增加搜索时间，建议将rootPath设置为脚本可能存在的具体目录，缩小搜索范围。

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