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]

In continuation to the above, there are two challenges with the existing code that needs to be sorted out:-

i, Accessing sharepoint folder just like local folder
ii, The above code was meant for including sub-folders also, but it's not working in that way. In case of sharepoint - under one main function there are several folders under which the required file lies. Hence if we select the main function folder,the code should also be need to search all subfolders and provide the required details.

e.g. Main Folder>Sub Folder1>File1.xlsm
>Sub Folder2>File2.xlsm
etc...

 

[selvendran]

Hello Experts,

Anyone help me up with the solution.

 

[kkazinski]

Sharepoint is like a URL. You access sharepoint via \\<path>\<folder>. When you download the slashes are reversed https://<path>/<folder>.

 

[selvendran]

Hi kkazinski

By that way I can able to map the network drive as folder but still the same error comes while running macro

 

[Kyle123]

What's the actual path you are using in the getFolder function?

 

[selvendran]

Hi Kyle

It works fine when earlier tried to access the local shared folder - \\aaaprod.abcd.bbb.net\userdata\MYNAME\Home\Documents\Test

But unable to do the same when trying to access sharepoint folder -\\teams.xx.cnet\sites\process\Development

<tbody>
</tbody>

 

[Kyle123]

Do you get the error of you map the path and use the local file name?

 

[Derek Brown]

I would map the SharePoint folder as a network drive (perhaps giving it the name "S:\").
Then I would use "FileCopy" to copy the workbook from the mapped drive to a (temporary) folder on my C: drive, from which I could open and process it.

 

[selvendran]

Hi Kyle

I could able to map the sharepoint folder as a network drive/folder without any errors. Even after the folder dialog opens, I could able to see the mapped folder for selection. But once selected, the above said error encounters.- Path Not Found - Runtime Error 76

Thanks,Selva