VBA upload of file to SharePoint using REST API (download already implemented in example)-CodePudding

I have implemented a Sharepoint API connect/authorize/download functionality in an Excel workbook using VBA - see my implementation snippet below:

base_url = "SP URL/_api/web/"
end_url = "GetFileByServerRelativeUrl('" & endpoint_url
url = base_url & end_url & filepath & "')/$value"
HttpRequest.Open "GET", url
HttpRequest.setRequestHeader "Authorization", "Bearer " & auth_token
HttpRequest.Send
sresult = HttpRequest.responseText
sObject = HttpRequest.responseBody
filepath = Replace(filepath, " ", " ")
filestring = Replace(filestring, " ", " ")
'MsgBox HttpRequest.Status
If HttpRequest.Status = 200 Then
    'MsgBox HttpRequest.responseBody
    Set MyStream = CreateObject("ADODB.Stream")
    MyStream.Open
    MyStream.Type = 1
    MyStream.Write HttpRequest.responseBody
    MyStream.SaveToFile filestring, 2
    MyStream.Close
Else
MsgBox HttpRequest.Status
MsgBox "Error - Connection to server failed"
End If

I am struggling with how to adapt this for an upload use case.

From reading the SP API docs I can see that I need to adjust the url endpoint to /GetFolderByServerRelativeUrl('/Library Name/Folder Name')/Files/add(url='example.txt',overwrite=true)

I am however unsure on the adaptation of the HttpRequest part, is it as simple as changing the `HttpRequest.Open "GET", url' to 'HttpRequest.Open "SEND", url' and then alterating the below part?

Set MyStream = CreateObject("ADODB.Stream")
    MyStream.Open
    MyStream.Type = 1
    MyStream.Write HttpRequest.responseBody
    MyStream.SaveToFile filestring, 2
    MyStream.Close

I've been at this for a few hours now, have tried to rewrite the MyStream part of the script but I am really unfamiliar with constructing this type of upload request.

Have attempted to write a SEND version of the function but am unclear on the full scope of changes I need to make.

CodePudding user response：

For upload use .LoadFromFile and then .read on the ADO stream.

' read  file as binary
    Dim ado As Object
    Set ado = CreateObject("ADODB.Stream")
    With ado
        .Type = 1 'binary
        .Open
        .LoadFromFile filepath & filename
        .Position = 0
    End With

    ' request
    Dim client As Object
    Set client = CreateObject("MSXML2.XMLHTTP.6.0")
    With client
        .Open "POST", Url, False
        .setRequestHeader "Authorization", "Bearer " & AUTH_TOKEN
        .send ado.read
        ado.Close
        
        Debug.Print .responseText
        If .Status = 200 Then '200 = OK
            MsgBox ("Upload completed successfully")
        Else
            MsgBox .Status & ": " & .statusText
        End If

    End With