如何使用VBA从Sharepoint加载/检索Excel工作簿？

Sub LoadWorkbookFromSharepoint()
    Dim SharepointURL As String
    Dim Filename As String
    Dim xmlHTTP As Object
    Dim oStream As Object
    Dim myWorkbook As Workbook
    
    ' 设置Sharepoint URL和文件名
    SharepointURL = "https://example.sharepoint.com/sites/MySite/Shared%20Documents/"
    Filename = "Workbook.xlsx"
    
    ' 创建HTTP请求对象
    Set xmlHTTP = CreateObject("MSXML2.XMLHTTP")
    
    ' 发送HTTP请求
    xmlHTTP.Open "GET", SharepointURL & Filename, False
    xmlHTTP.Send
    
    ' 创建文件流对象
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    
    ' 将HTTP响应保存到文件流
    oStream.Write xmlHTTP.responseBody
    oStream.SaveToFile "C:\Path\To\Save\Workbook.xlsx", 2 ' 保存路径自行修改
    
    ' 关闭文件流和HTTP请求对象
    oStream.Close
    Set oStream = Nothing
    xmlHTTP.abort
    Set xmlHTTP = Nothing
    
    ' 打开从Sharepoint加载的工作簿
    Set myWorkbook = Workbooks.Open("C:\Path\To\Save\Workbook.xlsx") ' 保存路径和文件名需与上面保持一致
    
    ' 在此处添加对工作簿的处理代码
    
    ' 关闭工作簿
    myWorkbook.Close SaveChanges:=False
End Sub