Using MSXML2.ServerXMLHTTP.6.0 in VBA file upload does not upload the to SharePOint

Question

I am trying to automate a process to extract data from Excel 2016 and upload as a text file to a Sharepoint document library which I am the owner of the Sharepoint.  The problem I have run into is the function will complete without error if I use the POST command, however the file is not uploaded.  Now when I change the POST to PUT, I get an error 405 Access Denied.  I have beat my head on this one and unable to find a solution. 

I am not very knowledgable of HTTP coding and did a good bit of reading to get to this striped down function.  Any help is greatly appreciated.

Sub TestUpLoad()

    Dim v As Variant

    Dim strURL As String

    '

    ' Tried the URL as below and with %20 in place of the space in "Shared Documents"

    ' Also tried the name as Documents all with the same result

    '

    strURL = "HTTP://microfocusinternational.sharepoint.com/teams/psdc-ba-dev/Shared Documents/Dashboard/"

    v = HTMLFileUpload(strURL, "c:\temp\SP_TestFile.txt")

End Sub

Function HTMLFileUpload(strURL As String, _

            strFileFQN As String, _

            Optional strUserName As String = "", _

            Optional strPWD As String = "")

    Dim pwd As String

    Dim objXMLHTTP As Object

    Dim objADOStream As Object

    Dim arrbuffer As Variant

    Dim strTargetFileURL As String

    Dim strFileName As String

On Error GoTo HTMLFileUpload_Error

    '

    ' Get just the filename from the FQN

    '

    strFileName = Right(strFileFQN, (Len(strFileFQN) - InStrRev(strFileFQN, "")))

    strTargetFileURL = strURL + strFileName

    '

    ' Read source file into buffer stream

    '

    Set objADOStream = CreateObject("ADODB.Stream")

    objADOStream.Open

    objADOStream.Type = 1

    objADOStream.LoadFromFile strFileFQN

    arrbuffer = objADOStream.Read()

    '

    ' Upload file to SharePoint URL supplied

    '

    Set objXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP.6.0")

    If (strUserName = "" Or strPWD = "") Then

        objXMLHTTP.Open "PUT", strTargetFileURL, False

    Else

        objXMLHTTP.Open "POST", strTargetFileURL, False, strUserName, strPWD

    End If

    ' Return values of objXMLHTTP

    ' readyState : 1 : Long : modDashboard.HTMLFileUpload

    ' status : <This method cannot be called until the Send method has been called> : Long : modDashboard.HTMLFileUpload

    ' statusText : <This method cannot be called until the Send method has been called> : String : modDashboard.HTMLFileUpload

    '

    ' File is not created in the sharepoint folder

    '

    objXMLHTTP.send arrbuffer

    ' Return values of objXMLHTTP using POST

    ' readyState : 4 : Long : modDashboard.WebUploadFile

    ' status : 200 : Long : modDashboard.WebUploadFile

    ' statusText : "OK" : String : modDashboard.WebUploadFile

    '

    ' Return values of objXMLHTTP using PUT

    ' readyState : 4 : Long : modDashboard.HTMLFileUpload

    ' status : 405 : Long : modDashboard.HTMLFileUpload

    ' statusText : "Method Not Allowed" : String : modDashboard.HTMLFileUpload

    If (objXMLHTTP.Status <> 200) Then

        Err.Raise objXMLHTTP.Status, , objXMLHTTP.statusText

    End If

HTMLFileUpload_Exit:

    Set objADOStream = Nothing

    Set objXMLHTTP = Nothing

    On Error GoTo 0

    Exit Function

HTMLFileUpload_Error:

    ' Use typical error handler here

    MsgBox "Error " & Err.Number & " - " & Err.Description & " Occured!", vbOKOnly + vbExclamation

    Resume HTMLFileUpload_Exit

End Function

Answer 1

Does anyone have any guidance on this?  I am at a dead-end currently.  I appreciate any help on this.