Excel VBA -查找SharePoint上文件的本地文件位置

Sub FindFileLocalPath(sharePointFileName As String, Optional searchFolder As String = "C:\")
    Dim fso As Object
    Dim folder As Object
    Dim subFolder As Object
    Dim filePath As String
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set folder = fso.GetFolder(searchFolder)
    
    ' 搜索当前文件夹
    filePath = folder.Path & "\" & sharePointFileName
    If fso.FileExists(filePath) Then
        MsgBox "找到文件：" & vbCrLf & filePath
        Exit Sub
    End If
    
    ' 递归搜索子文件夹
    For Each subFolder In folder.SubFolders
        Call FindFileLocalPath(sharePointFileName, subFolder.Path)
    Next subFolder
    
    MsgBox "未找到文件：" & sharePointFileName
End Sub

' 调用示例
Sub TestFindFile()
    Dim fileName As String
    fileName = "example.xlsx" ' 替换为你要查找的文件名
    FindFileLocalPath fileName
End Sub

Sub DownloadSharePointFile(sharePointUrl As String, localFilePath As String, siteUrl As String, accessToken As String)
    Dim http As Object
    Dim url As String
    Dim response As String
    
    Set http = CreateObject("MSXML2.XMLHTTP")
    
    ' 构建下载文件的URL
    url = siteUrl & "/_api/web/GetFileByServerRelativeUrl('" & sharePointUrl & "')/$value"
    
    With http
        .Open "GET", url, False
        .SetRequestHeader "Authorization", "Bearer " & accessToken
        .SetRequestHeader "Accept", "application/octet-stream"
        .Send
    End With
    
    If http.Status = 200 Then
        Open localFilePath For Binary Access Write As #1
        Put #1, , http.responseBody
        Close #1
        MsgBox "文件已下载到：" & localFilePath
    Else
        MsgBox "下载失败，状态码：" & http.Status
    End If
End Sub

' 调用示例（需要获取 accessToken）
Sub TestDownloadFile()
    Dim sharePointUrl As String
    Dim localFilePath As String
    Dim siteUrl As String
    Dim accessToken As String
    
    sharePointUrl = "/sites/YourSite/Shared%20Documents/example.xlsx" ' 替换为文件的服务器相对URL
    localFilePath = "C:\Temp\example.xlsx" ' 本地保存路径
    siteUrl = "https://your-sharepoint-site.com" ' 替换为你的SharePoint站点URL
    accessToken = "YOUR_ACCESS_TOKEN" ' 需要通过OAuth等方式获取
    
    DownloadSharePointFile sharePointUrl, localFilePath, siteUrl, accessToken
End Sub