Usually I use this piece of code to retrieve the content of a folder in VBA. But this doesn't work in the case of a sharepoint. How can I do ?
Dim folder As folder
Dim f As File
Dim fs As New FileSystemObject
Set folder = fs.GetFolder("//sharepoint.address/path/to/folder")
For Each f In folder.Files
'Do something
Next f
EDIT (after a good comment by shahkalpesh) :
I can access to the sharepoint if I enter the address in Windows Explorer. Access to the sharepoint needs an authentification, but it's transparent, because it relies on the Windows login.
The only way I've found to work with files on SharePoint while having to server rights is to map the WebDAV folder to a drive letter. Here's an example for the implementation.
Add references to the following ActiveX libraries in VBA:
wshom.ocx
) - for WshNetworkscrrun.dll
) - for FileSystemObjectCreate a new class module, call it DriveMapper
and add the following code:
Option Explicit
Private oMappedDrive As Scripting.Drive
Private oFSO As New Scripting.FileSystemObject
Private oNetwork As New WshNetwork
Private Sub Class_Terminate()
UnmapDrive
End Sub
Public Function MapDrive(NetworkPath As String) As Scripting.Folder
Dim DriveLetter As String, i As Integer
UnmapDrive
For i = Asc("Z") To Asc("A") Step -1
DriveLetter = Chr(i)
If Not oFSO.DriveExists(DriveLetter) Then
oNetwork.MapNetworkDrive DriveLetter & ":", NetworkPath
Set oMappedDrive = oFSO.GetDrive(DriveLetter)
Set MapDrive = oMappedDrive.RootFolder
Exit For
End If
Next i
End Function
Private Sub UnmapDrive()
If Not oMappedDrive Is Nothing Then
If oMappedDrive.IsReady Then
oNetwork.RemoveNetworkDrive oMappedDrive.DriveLetter & ":"
End If
Set oMappedDrive = Nothing
End If
End Sub
Then you can implement it in your code:
Sub test()
Dim dm As New DriveMapper
Dim sharepointFolder As Scripting.Folder
Set sharepointFolder = dm.MapDrive("http://your/sharepoint/path")
Debug.Print sharepointFolder.Path
End Sub