• 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...

  • 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

xlsvba87

New Member
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...
 

xlsvba87

New Member
See link for very handy code.
http://ckannan.blogspot.ca/2012/09/vba-download-files-from-sharepoint.html

If you have Admin access to the server and SharePoint Central Administration, below link may be easier. I use small batch file to fire Windows PowerShell script on schedule.

https://technet.microsoft.com/en-us/library/ee428301.aspx
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?
 

Chihiro

Excel Ninja
Here you go.


Code:
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
 

Thangavel

Active Member
@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.
 

Chihiro

Excel Ninja
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.
 
Top