How to download Excel file from my.sharepoint.com (onedrive)

Question

I have an Excel file which is shared via Onedrive link. I need to download this file to PC (c:\TEST).

I found this code, it downloaded the file, but the file is corrupted or something.

Sub DownloadFile()
    MsgBox ("12")
    Dim myURL As String
    myURL = "https://foxhunter1-my.sharepoint.com/:x:/g/personal/name_example/Eexy0m7o08hBlRJAP_xh64wBN45j70JIw2E-CDlJGgZILg?e=cbs4iF"

    Dim WinHttpReq As Object
    Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
    With WinHttpReq
        WinHttpReq.Open "GET", myURL, False, "contoso\user", "password"
        WinHttpReq.send
    
        If WinHttpReq.Status = 200 Then
            Set oStream = CreateObject("ADODB.Stream")
            oStream.Open
            oStream.Type = 1
            oStream.Write WinHttpReq.responseBody
            oStream.SaveToFile "C:\TEST\TEST.xlsx", 2 ' 1 = no overwrite, 2 = overwrite
            oStream.Close
        End If
    End With
End Sub

ERROR:

Answer 1

Try the following code (I got it very likely from here as stated in Tragamor's comment)

Option Explicit
#If VBA7 Then
    Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
        ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
#Else
    Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
        ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
#End If  

    Function DownloadFileFromWeb(strURL As String, strSavePath As String) As Long

        ' strSavePath includes filename
        DownloadFileFromWeb = URLDownloadToFile(0, strURL, strSavePath, 0, 0)
    End Function

Then you only need to

Sub GetFile()
    Dim urlName  As String
    urlName = "your Url"
    Dim fName As String
    fName = "C:\TEST\test.xlsx"
    URLDownloadToFile 0, urlName, fName, 0 , 0

End Sub