1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Code to Download Files from sharepoint to Local Network drive

Discussion in 'VBA Macros' started by xlsvba87, Aug 31, 2015.

  1. xlsvba87

    xlsvba87 New Member

    Messages:
    15
    Hi all

    I need all experts your help/suppport in this. I need a macro to access sharepoint(https) folder/sub-folder and download the (.xlsm)files into the designated local drive as it is.

    SharePoint Scenario :

    Main Folder > SubFolder1 >File1.xlsm
    > SubFolder2 >File2.xlsm
    > etc...
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,115
  3. xlsvba87

    xlsvba87 New Member

    Messages:
    15
    Hi Chihiro

    Thanks for the reply. For 2nd option, I do not have admin access. For 1st link, I couldn't see any vba code posted in it. Could u pls provide?
  4. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,115
    Here you go.
    [​IMG]

    Code (vb):
    Option Explicit
    Sub btnSharePointFolder()
        Dim sht As Worksheet
         
        Set sht = ThisWorkbook.Sheets("SharePoint Download")
     
        If sht.Range("SharePointPath") = "" Then
            MsgBox "Please enter a sharepoint path first", vbCritical
            Exit Sub
        End If
     
        If Right(sht.Range("SharePointPath"), 1) <> "/" Then
            'SharePointPath: http://testdrive.sharepoint.ckannan.blogspot.com/teams/YourTeam/
           sht.Range("SharePointPath") = sht.Range("SharePointPath") & "/"
        End If
     
        With Application.FileDialog(msoFileDialogFolderPicker)
            .InitialFileName = sht.Range("SharePointPath")
            .Title = "Please select a location of input files"
            .Show
            If Not .SelectedItems.Count = 0 Then
                sht.Range("SharepointFolder") = .SelectedItems(1)
            Else
                Exit Sub
            End If
        End With
     
    '    'To Remove Drive
    '    Shell "net use Q: /delete"
         
        If Dir("Q:\", vbDirectory) = "" Then
            Shell "net use Q: " & sht.Range("SharePointPath").Value  '/user:MyDomain\MyUserName MyPassword
       End If
     
    End Sub
    '_________________________________________________________________________________
    Sub MapNetworkDrive()
        If Dir("Q:\", vbDirectory) = "" Then
        'SharePointPath: http://testdrive.sharepoint.ckannan.blogspot.com/teams/YourTeam/
           Shell "net use Q: " & ThisWorkbook.Sheets("SharePoint Download").Range("SharePointPath").Value  '/user:MyDomain\MyUserName MyPassword
           MsgBox "The sharepoint path is mapped as network drive.", vbInformation
        Else
            MsgBox "The mapped network drive already exists.", vbInformation
        End If
    End Sub
    '_________________________________________________________________________________
    Sub DownloadFiles()
        Dim Directory As String
        Dim file As String
        Dim i As Long
        Dim fso As FileSystemObject
     
        Application.ScreenUpdating = False
     
        If Dir("Q:\", vbDirectory) = "" Then
            MsgBox "There is no mapped network drive", vbCritical
            Exit Sub
        End If
     
        'DownloadFolder: http://testdrive.sharepoint.ckannan.blogspot.com/teams/YourTeam/Shared Documents/PDW Status
       Directory = "Q:\" & ThisWorkbook.Sheets("SharePoint Download").Range("DownloadFolder").Value & "\"
        Set fso = CreateObject("Scripting.FileSystemObject")
         
    '  Get first file
       file = Dir(Directory, vbReadOnly + vbHidden + vbSystem)
     
        If file = "" Then
            MsgBox "No files found in the sharepoint folder.", vbCritical
            Exit Sub
        End If
     
        Do While file <> ""
            fso.CopyFile Directory & file, "C:\", True
            file = Dir()
        Loop
     
        Application.StatusBar = False
     
        MsgBox "Downloaded all files to the local folder.", vbInformation
    End Sub
    '_________________________________________________________________________________
    Sub btnLocalFolder_Click()
        With Application.FileDialog(msoFileDialogFolderPicker)
            .InitialFileName = "C:\"
            .Title = "Please select a location to download files"
            .Show
            If Not .SelectedItems.Count = 0 Then
                ThisWorkbook.Sheets("SharePoint Download").Range("LocalFolder") = .SelectedItems(1)
            End If
        End With
    End Sub
    xlsvba87 likes this.
  5. Thangavel

    Thangavel Active Member

    Messages:
    103
    @Chihiro I tried to use above code but failed to execute it.
    Getting error that, file not available in sharepoint folder.
    Could you please share the excel with this or working code to download file from sharepoint.
  6. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,115
    So, likely you've got the wrong path to the file.

    First, you'll need to map network drive in order to use the code.

    It will also depend on your SharePoint version. That code works for local server instance of SharePoint 2010, 13 (and likely older version). It will not work for SharePoint Online.

    Without details of your set up, and how you used code. There isn't much I can help you with.
  7. Thangavel

    Thangavel Active Member

    Messages:
    103
    So, Is it possible to download from Sharepoint online?
  8. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,115
    Thangavel likes this.
  9. Thangavel

    Thangavel Active Member

    Messages:
    103

Share This Page