Get the content of a sharepoint folder with Excel VBA

Question

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.

Answer 1

Your slashes are around the wrong way. This code works:

Public Sub ListFiles()
    Dim folder As folder
    Dim f As File
    Dim fs As New FileSystemObject
    Dim RowCtr As Integer

    RowCtr = 1
    Set folder = fs.GetFolder("\\SharePointServer\Path\MorePath\DocumentLibrary\Folder")
    For Each f In folder.Files
       Cells(RowCtr, 1).Value = f.Name
       RowCtr = RowCtr + 1
    Next f
End Sub

To get the path to use, go into the folder in the document library, drop down the Actions menu and choose Open in Windows Explorer. Copy the path you see there and use that.

Answer 2

Try mapping the sharepoint library to a drive letter in windows. Then select the drive and path in your code.

Answer 3

IMHO the coolest way is to go via WebDAV (without Network Folder, as this is often not permitted). This can be accomplished via ActiveX Data Objects as layed out in this [excellent article] excellent article (code can be used directly in Excel, used the concept recently).

Hope this helps!

http://blog.itwarlocks.com/2009/04/28/accessing-webdav-in-microsoft-word-visual-basic/

Answer 4

The only way I've found to work with files on SharePoint while having to server rights is to map the drive. Here's an example class for implementing a mapped SharePoint drive and disposing of it. Create a new 'Class' module and add the following code:

Option Explicit

Private m_SPPath As String
Private m_MapPath As String
Private m_NewDriveLetter As String

Property Let SPPath(v As String)
m_SPPath = v
End Property

Property Get SPPath() As String
SPPath = m_SPPath
End Property

Property Get MapPath() As String

If m_MapPath <> "" Then
    MapPath = m_MapPath
Else
    MsgBox prompt:="Path has not been mapped." & vbCrLf & vbCrLf & _
                   "Try executing the 'Map' meathod if you have not already done so.", _
           Title:="CS Automation", _
           Buttons:=vbInformation
End If

End Property

Public Function Map() As Boolean
Const Alphabete As String = "A B C D E F G H I J K L M N O P Q R S T U V W X W Z"
Dim i As Long ' counter for looping alphabete
Dim oFSO As Scripting.FileSystemObject
Dim oDrive As Scripting.Drive
Dim alpha
Dim WshNetwork

Set oFSO = New FileSystemObject
alpha = Split(Alphabete)

For Each oDrive In oFSO.Drives
    For i = LBound(alpha) To UBound(alpha)
    If oDrive.DriveLetter = alpha(i) Then alpha(i) = "0"
    Next i
Next oDrive

For i = LBound(alpha) To UBound(alpha)
    If alpha(i) <> "0" Then
        m_NewDriveLetter = alpha(i)
        Exit For
    End If
Next i

Set WshNetwork = CreateObject("WScript.Network")
WshNetwork.MapNetworkDrive m_NewDriveLetter & ":", m_SPPath
Set WshNetwork = Nothing
m_MapPath = m_NewDriveLetter & ":\"
End Function


Public Function Dispose() As Boolean
Dim WshNetwork
Set WshNetwork = CreateObject("WScript.Network")
On Error Resume Next
WshNetwork.RemoveNetworkDrive m_NewDriveLetter & ":"
Set WshNetwork = Nothing
End Function

Then you can implement it in your code:

Private Sub testclstempmapsp()
'test passed 10-29-09
'by Chris Hayes
Dim c As cls_TempMapSP
Set c = New cls_TempMapSP

'you can view the list in explorer view
'and copy the path
c.SPPath = "http://your/sharepoint/path"
c.Map

'Do your code on your files here
'you can use Scripting.FileScriptingObject
'reference and program against it


Debug.Print c.MapPath

'when you're done, you can dispose the mapping
'so the user doesn't see it
c.Dispose

Set c = Nothing
End Sub

Answer 5

cls_TempMapSP is not defined.