3

I am trying to download a file from SharePoint with VBA.

The file is a picture but the picture isn't view-able once it gets onto the system.

I think that I'm downloading it in the wrong format.

Sub DownloadFromSharepoint()
    Dim myURL As String
    myURL = "https://MYSHAREPOINTSITE"

    Dim WinHttpReq As Object
    Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
    WinHttpReq.Open "GET", myURL, False
    WinHttpReq.Send

    If WinHttpReq.Status = 200 Then
        Set oStream = CreateObject("ADODB.Stream")
        oStream.Open
        oStream.Type = 1
        oStream.Write WinHttpReq.ResponseBody
        oStream.SaveToFile ("C:\Users\DOMAIN\temp.jpg")
        oStream.Close
    End If
End Sub
Community
  • 1
  • 1
JP1
  • 117
  • 1
  • 1
  • 6
  • 2
    you can map the sharepoint site to a network drive and then use FileCopy method. Probably much easier. – Scott Holtzman Feb 23 '17 at 15:13
  • 1
    Have you the full path to the image in myURL? Plus, the line `myURL = WinHttpReq.ResponseBody` makes no sense to me. – FunThomas Feb 23 '17 at 15:20
  • 1
    I agree with @ScottHoltzman here. That's what I'd do. An alternative is using the UrlDownloadToFile api. See: http://stackoverflow.com/questions/34923409/excel-vba-urldownloadtofile-error-for-https-ressource/34989402 – Ryan Wildry Feb 23 '17 at 16:28

1 Answers1

7

Here is the wrapper code I currently use to download files from our Sharepoint site:

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

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

The function DownloadFileFromWeb returns 0 if the download was successful.

Tragamor
  • 3,594
  • 3
  • 15
  • 32
  • This is great! Thank you so much! – JP1 Feb 23 '17 at 20:33
  • why use Alias "URLDownloadToFileA"? – Jing He May 07 '19 at 07:05
  • The Alias clause is used to indicate that the defined function has another name within its dll (in this case 'urlmon.dll'). https://learn.microsoft.com/en-us/office/client-developer/excel/how-to-access-dlls-in-excel – Tragamor May 07 '19 at 17:05
  • 1
    May go without saying, but if you use the "Get Link" on sharepoint it will have a bunch of other stuff in the direct file URL. You may have to remove everything after the .xlsx for it to download properly. – michaelf May 06 '20 at 20:19