CODEWITHSUNDEEP
excelvbasharepoint
JP1
JP1

Reputation: 127

How to download a file from SharePoint with VBA?

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

Upvotes: 4

Views: 50345

Answers (2)

Víctor
Víctor

Reputation: 1

The URLDownloadToFile function didn't work for me. It downloaded some html code inestead of the Excel file

For Excel files only, I used the Excel.Application object to open the file in the background and then save it in a local folder:

Public Sub DownloadExcelFileFromSharepoint(ByVal SharepointFileRoute As String, ByVal TargetDestinationFile As String)
    'No neccesary reference to Microsoft Excel Object Library, using late binding

    Dim oExcelApplication As Object 'Excel.Application
    Dim oExcelWorkbook As Object 'Excel.Workbook

    Set oExcelApplication = CreateObject("Excel.Application")
    Set oExcelWorkbook = oExcelApplication.Workbooks.Open(SharepointFileRoute, ReadOnly:=True)

    ' Don't display alerts on saving, so if TargetDestinationFile exists will be overwrited
    oExcelApplication.DisplayAlerts = False
    oExcelWorkbook.SaveAs TargetDestinationFile
    oExcelApplication.DisplayAlerts = True

    oExcelWorkbook.Close
    Set oExcelWorkbook = Nothing
    
    oExcelApplication.Quit
    Set oExcelApplication = Nothing
End Sub

Getting the correct URL was not obvious for me. Just copying the Sharepoint's URL while the file is opened in the browser and use it with this funcion won't work. I had to open the file in Excel with the open file dialog, write the shareponit folder and navigate to the file subfolder. I got something like this :

https://companygroup.sharepoint.com/sites/TestSite/Shared documents/TestFile.xlsx

Upvotes: 0

Tragamor
Tragamor

Reputation: 3634

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.

Upvotes: 9

Related Questions