I want to download a zip file from OneDrive using VBA.(UserData folder zipped as UserData.zip and uploaded to the OneDrive location; size: 200KB) For that, I used the following VBA script.(sensitive data altered in the 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://MYCOMPANYNAME-my.sharepoint.com/personal/MYUSERNAME_MYCOMPANYNAME_com/Documents/FOLDERNAME/UsersData.zip"
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:\Users\MYUSERNAME\Downloads\UsersData.zip", 2
oStream.Close
MsgBox "File downloaded"
Else
MsgBox "Download failed"
End If
End Sub
When I run it, it downloads the zip file and gives me the "File downloaded" message as well. But when I try to open the downloaded file, unzipping application fails to do that and it seems like the file is corrupted.
This works fine for pdf, jpg, txt and other similar files. Why this happens only in zip files?
What is the issue in my code and is there any other effective ways in VBA to download zip files from OneDrive?
