VBA Code to Download Files from sharepoint to Local Network drive

[selvendran]

Hello Experts,
I have a VBA code to copy multiple excel files placed in a particular folder and paste all tabs in active workbook and it works fine. Now the challenge is that I need to access sharepoint folder instead of local folder. I can able to map that sharepoint folder into my network drive but still the code is not capable to access sharepoint folder(https). I am sure that there might be some little modifications required in the existing code and your expertise would be deeply appreciated in this context.

Code :

Private Sub ListFolders()
'''''''''''''''''''''''''''''''''''''''Makes List of Folders and Subfolders with paths for copying'''''''''''''''
Dim fs, f, f1, s, sf
Dim iRow As Long
Dim fd As FileDialog
Dim FolderName1 As String
ExtraSlash = "\"
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
With fd
.AllowMultiSelect = True
If .Show Then

For Each myFolder In .SelectedItems

FolderName1 = myFolder & ExtraSlash

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(FolderName1)
Set sf = f.SubFolders

Sheets("Load Path").Select '''change the name of the worksheet selected'''
Columns("A:B").ClearContents
Range("A2").Select
For Each f1 In sf
ActiveCell.Value = f1.Name
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = f1.Path ''''''''''''''''''''''''''''''''''''''''''''''''
ActiveCell.Offset(0, -1).Activate
ActiveCell.Offset(1, 0).Activate
iRow = iRow + 1
Next

Next
End If
End With
ListFiles FolderName1
End Sub
---------------------------------------------------------------------------------------------------------

Sub ListFiles(FolderName1 As String)
'''''''''''''''''''''''''''''''''''''''Makes List of Folders and Subfolders with paths for copying'''''''''''''''
Dim fs As Object
Dim objFolder As Object
Dim objFile As Object

Set fs = CreateObject("Scripting.FileSystemObject")
Set objFolder = fs.GetFolder(FolderName1)

Sheets("Load Path").Select '''change the name of the worksheet selected'''
Range("A1").Value = "Sheet Name"
Range("B1").Value = "File Path"
Range("a2").Activate
r = Range("A2").CurrentRegion.Rows.Count

If r = 1 Then

For Each objFile In objFolder.Files
ActiveCell.Select
Selection.Formula = objFile.Name
ActiveCell.Offset(0, 1).Select
Selection.Formula = objFile.Path
ActiveCell.Offset(0, -1).Select
ActiveCell.Offset(1, 0).Select

Next

Else

'Selection.End(xlDown).Select
ActiveCell.Offset(0, 0).Select

For Each objFile In objFolder.Files
ActiveCell.Select
Selection.Formula = objFile.Name
ActiveCell.Offset(0, 1).Select
Selection.Formula = objFile.Path
ActiveCell.Offset(0, -1).Select
ActiveCell.Offset(1, 0).Select

Next

End If

Columns("A").Select
Selection.Columns.AutoFit
Range("A2").Select

''''''''''''''''''''''''''''''''''''''''''''''''

End Sub

Error : Set f = fs.GetFolder(FolderName1)

Error Msg : Path Not Found - Runtime Error 76

 

[selvendran]

Hi Derek

Currently we are managing in a similar way like using one macro to download files from sharepoint and after then use the above macro to process the same files.

But it seems dual activity and also to d/l files, I need to maintain the file name and file location base somewhere in the file to make it done

Thanks,Selva

 

[Derek Brown]

I get that same error (Path Name not found - Error 76) if I have not signed in to SharePoint.
In explorer, the mapping of the network drive has a 'red cross' at the left.
The solution for me is to click on the drive in explorer and sign in to SharePoint when prompted.
After a short wait, the content of that 'drive' is displayed in explorer.
I can then run my code that no longer fails with that error.