如何用VBA SendKeys获取Excel 365中同步文件的本地路径？

方法1：直接通过Workbook属性获取本地路径（推荐） #

OneDrive同步完成的文件，Excel的FullName属性会直接返回本地路径，完全不需要依赖菜单操作，稳定性远高于SendKeys：

Sub GetLocalPathDirectly()
    Dim wb As Workbook
    Dim CamLocalComplete As String
    Dim CamLocal As String
    
    Set wb = ThisWorkbook
    CamLocalComplete = wb.FullName
    
    ' 提取不含文件名的本地路径
    If InStr(CamLocalComplete, "\") > 0 Then
        CamLocal = Left(CamLocalComplete, InStrRev(CamLocalComplete, "\") - 1)
        Range("E26").Value = CamLocal
    Else
        MsgBox "无法获取本地路径，文件可能未同步到本地"
    End If
End Sub

方法2：解析OneDrive在线URL为本地路径 #

如果FullName返回的是OneDrive在线链接（比如https://d.docs.live.net/xxxx/...），可以用以下函数转换为本地路径：

Function ConvertOneDriveURLToLocalPath(onlinePath As String) As String
    Dim regEx As Object
    Dim oneDriveLocalPath As String
    Dim match As Object
    
    ' 获取本地OneDrive路径（优先个人版，再尝试商业版）
    oneDriveLocalPath = Environ("OneDrive")
    If oneDriveLocalPath = "" Then
        oneDriveLocalPath = Environ("OneDriveCommercial")
    End If
    
    If oneDriveLocalPath = "" Then
        ConvertOneDriveURLToLocalPath = ""
        Exit Function
    End If
    
    ' 匹配个人版OneDrive URL格式
    Set regEx = CreateObject("VBScript.RegExp")
    regEx.Pattern = "https://d\.docs\.live\.net/[0-9a-f]+/(.*)"
    Set match = regEx.Execute(onlinePath)
    
    If match.Count > 0 Then
        ConvertOneDriveURLToLocalPath = oneDriveLocalPath & "\" & match(0).SubMatches(0)
    Else
        ' 匹配商业版SharePoint/OneDrive URL格式
        regEx.Pattern = "https://.*my\.sharepoint\.com/personal/.*?/(.*)"
        Set match = regEx.Execute(onlinePath)
        If match.Count > 0 Then
            ConvertOneDriveURLToLocalPath = oneDriveLocalPath & "\" & Replace(match(0).SubMatches(0), "/", "\")
        End If
    End If
End Sub

Sub GetLocalPathFromOneDriveURL()
    Dim wb As Workbook
    Dim onlinePath As String
    Dim localPath As String
    Dim CamLocal As String
    
    Set wb = ThisWorkbook
    onlinePath = wb.FullName
    
    localPath = ConvertOneDriveURLToLocalPath(onlinePath)
    If localPath <> "" Then
        CamLocal = Left(localPath, InStrRev(localPath, "\") - 1)
        Range("E26").Value = CamLocal
    Else
        MsgBox "无法解析本地路径，请确保文件已同步到OneDrive本地文件夹"
    End If
End Sub

SendKeys失效的核心原因 #

• Excel 365的界面快捷键或菜单结构可能随版本更新发生变化，原%ail~对应的操作路径已失效
• SendKeys完全依赖窗口焦点、界面加载速度，稍有延迟或焦点丢失就会执行失败
• 不同版本的Excel/OneDrive菜单逻辑存在差异，通用性极差

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