Downloading file from sharepoint via VBA problem/oddity

Hi there,

I am trying to download a file from sharepoint and I'm running into a little oddity.

If I try and run this code:

Sub test()
    Dim myURL, FileName, FilePath As String
    'Dim ReturnArray As Variant
    
    myURL = "SharePointUrl\Filename.Ext"

    FilePath = "\\FilePath"
    FileName = "test.xlsx"
    
    Dim WinHttpReq As Object
    Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")

    If FileExists(FilePath & "\" & FileName) Then 'See above
        ' First remove readonly attribute, if set
        SetAttr FilePath & "\" & FileName, vbNormal
        ' Then delete the file
        Kill FilePath & "\" & FileName
    End If
    
    WinHttpReq.Open "GET", myURL, False
    WinHttpReq.Send
    
    myURL = WinHttpReq.ResponseBody
    If WinHttpReq.Status = 200 Then
        Set ostream = CreateObject("ADODB.Stream")
        ostream.Open
        ostream.Type = 1
        ostream.Write WinHttpReq.ResponseBody
        ostream.SaveToFile (FilePath & "\" & FileName)
        ostream.Close
    End If

End Sub

I get an access denied message on the line WinHttpReq.Send

however if run this first.

Sub test2()

With Application.FileDialog(msoFileDialogOpen)
    .InitialFileName = "sharepointURL\Filename.ext"
    .AllowMultiSelect = False
    .Show
End With

End Sub

and close the window, and then run the first script it works flawlessly. I'm guessing its some sort of authentication check or something.

is there anyway I combine whatever is working in the second routine in the first at all?

Thank you for taking a look. I'm sure I'm missing something but I'm not seeing any difference between my original code snippet and your suggestion.

If you are getting an error on WinHttpReq.Send, it is most likely that the URL is wrong.

Hi and thank you for taking a look.

The URL is definitely correct - I'm pretty sure this is down to authentication, as after I have logged into the sharepoint service manually the code runs smoothly. But this then defeats the purpose of my attempts at automation.

I've tried a couple of different methods of sending my user credentials, but both have failed, someone managerd to get a 0 return on WinHttpReq.Status