VBA download a File from OneDrive

office365api, onedrive, vba

Solution

This code works for me. Thanks everyone for your advice.

Sub DownloadFile()

'Declare the Object and URL
Dim myURL As String
Dim WinHttpReq As Object

'Assign the URL and Object to Variables
myURL = "https://xxx-my.sharepoint.com/personal/Sidxxx/Documents/test.xlsx"
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")

'Provide Access Token and PWD to the URL for getting the service from API
WinHttpReq.Open "GET", myURL, False, "abcdef", "12345"
WinHttpReq.send

Debug.Print WinHttpReq.Status

myURL = WinHttpReq.responseBody

    If WinHttpReq.Status = 200 Then

        Set oStream = CreateObject("ADODB.Stream")

        oStream.Open

        oStream.Type = 1

         oStream.Write WinHttpReq.responseBody

        oStream.SaveToFile "C:\testdownload.xlsx", 2

        oStream.Close

    End If

End Sub

Problem

I am trying to download a file from OneDrive. I am new to API Concepts and here is Code, ``` Sub OneDrive_Download() 'Declare the Object Dim oRequest As Object 'Create and Assign Object Set oRequest = CreateObject("MSXML2.XMLHTTP") 'Input User OneDrive URL URL = "https://xxxx-my.sharepoint.com/personal/sidxxx_ie/" 'Post the URL in the Object oRequest.Open "POST", URL, False 'Send Keys to the API oRequest.send ("{""client_id"":myclientid,""CLIENT_SECRET"":myclientsecret}") 'Print the Response in the Immediate Window Debug.Print oRequest.ResponseText End Sub ``` And this is the Response that I got from Debug.Print on my Immediate window, ``` // Setup cta message fields. window.$Do.when("User", 0, function () { User.setupCallToActionMessages(); }); // Other tile var Tiles = Tiles || {}; Tiles.otherJSON = { 'name': 'Use another account', 'login': '', 'imageAAD': 'other_glyph.png', 'imageMSA': 'other_glyph.png', 'isLive': false, 'link': 'other', 'authUrl': '', 'sessionID': '', 'domainHint': 'other' }; </script> </body> </html> ``` Now I am looking to download a file named as test.xlsx in my OneDrive. What is the way to go about it. UPDATED - CODE ``` Sub DownloadFile() 'Declare the Object and URL Dim myURL As String Dim WinHttpReq As Object 'Assign the URL and Object to Variables myURL = "https://xxx-my.sharepoint.com/personal/Sidxxx/Documents/test.xlsx" Set WinHttpReq = CreateObject("Microsoft.XMLHTTP") 'Provide Access Token and PWD to the URL for getting the service from API WinHttpReq.Open "GET", myURL, False, "abcdef", "12345" WinHttpReq.send Debug.Print WinHttpReq.Status myURL = WinHttpReq.responseBody If WinHttpReq.Status = 200 Then Set oStream = CreateObject("ADODB.Stream") oStream.Open oStream.Type = 1 oStream.SaveToFile "C:\testdownload.xlsx", 2 oStream.Close End If End Sub ``` The file is now getting downloaded. But it appears to be empty.

Original source