How to traverse folders and files on SharePoint with Excel VBA

[pczegle]

I have done many searches on how to traverse all files in SharePoint folders using Excel VBA and have not come across an answer I could use because, by configuration design, I cannot map a SharePoint URL to a drive letter. After much trial and error, I came up with a very short solution that works for me. I'd like to share it in thanks for all the help I have gotten by searching forums like this one. I hope it helps you get home to your kids a little sooner. Here it is:

Sub SPDir()
Dim wb As Workbook
Dim dummyFile As String
'
' The file specified by dummyFile must reside in SharePoint in order to use SharedWorkspace
' The way the code is set up, the path and filename in dummyFile should NOT substitute %20 instead of spaces
' There is logic later to ignore dummyFile on output
' Substitute your own dummy file name below
' If you don't want to create a dummy file on SharePoint, pick any file that's already there
' and get rid of the logic below that ignores it.
'
dummyFile = "https://........"
Set wb = Workbooks.Open(dummyFile)
Application.Wait Now + TimeValue("00:00:01") ' if this code terminates abnormally, try a longer wait value
Set swsFiles = wb.SharedWorkspace.Files
For Each c In swsFiles
'
' The Path logic below limits traversing to just the folder that contains
' dummyFile and all its sub-folders. This can be changed to suit needs.
'
If Replace(c.URL, "%20", " ") <> dummyFile And _
Left(Replace(c.URL, "%20", " ") , Len(wb.Path)) = wb.Path Then MsgBox (c.URL)
Next c
End Sub

 

[MikeDBMan]

I always set Option Explicit.
What should swsFiles be declared as? And what should C be declared as? If I declare swsFiles as variant I get this error:
Object Variable or With block variable not set

Thanks for your help.

 

[pczegle]

I'm not a VB expert ... I got this to work by alot of trial and error and by alot of adapting the good work of others that I found on the web. It seems to me that the following might do the trick for you:

Dim swsFiles As SharedWorkspaceFiles
Dim c As SharedWorkspaceFile

 

[pczegle]

I always set Option Explicit.
What should swsFiles be declared as? And what should C be declared as? If I declare swsFiles as variant I get this error:
Object Variable or With block variable not set

Thanks for your help.

I'm not a VB expert ... I got this to work by alot of trial and error and by alot of adapting the good work of others that I found on the web. It seems to me that the following might do the trick for you:

Dim swsFiles As SharedWorkspaceFiles
Dim c As SharedWorkspaceFile

 

[pczegle]

Please NOTE:
I just discovered that the SharedWorkspace.Files method will return up to 99 aggregate files and folders on a Sharepoint site. If there are 100 or more, it will return NOTHING instead of the first 99. Inasmuch as I have sites that have 100 or more I will search for another method that works in all cases.

 

[pczegle]

The following VBA code produces a folder/file tree of the specified Sharepoint Site. Unlike the SharedWorkspace.Files method, it does NOT restrict the number of files & folders to 99 in order to produce a list. It requires using the Microsoft ActiveX Data Objects 2.x Library (I used 2.8) . If it's NOT installed, research the web on how to install it. If it is installed, activate it by selecting "Tools" and then "References" in the VB editor/debugger. Then click on the checkbox for this library.

Code was borrowed and adapted from several places:
1 - http://blog.itwarlocks.com/2009/04/28/accessing-webdav-in-microsoft-word-visual-basic/
author Jeffrey Ridout provided the basic code to use the webDav methodology
2 - http://vbautomation.110mb.com/ACCESS/ADO/ADO_GetChildren Method.htm
provided code to restrict the contents of subfolders to where davDir.RecordType = adCollectionRecord to potentially avoid error code 3001 when using the GetChildren method.

The only thing I did was to modify the code to produce the tree listing in recursive fashion to handle all the nested subfolders.

Please note that I am not an Excel VBA guru. If you want to improve this code, that's great!

I hope this helps you.

Public Stack As New Collection
Public PrintLine As String
Public Spaces As String
Public fnum As Integer
Public outputFile As String
'
' Stack provides a means of storing the folder/file tree for followup processing, if necessary.
' It's not used actively by this program. Please note that the VB editor/debugger will only
' display the first 256 Stack entries even though there are potentially many more.
'
Sub NavigateSharepointSite()

'
' outputFile is where the folder/file tree is written
' spSite is the Sharepoint Site for which the folder/file tree is produced
'
On Error Resume Next
Dim spSite As String, spDir As String, spFile As String, url As String
fnum = FreeFile()
outputFile = "c:\your directory\Tree.txt"
Open outputFile For Output As fnum
Spaces = " "
spSite = "https://your Sharepoint site/"
spDir = ""
spFile = ""
url = spSite & spDir & spFile
Stack.Add (Array(spSite, spDir, spFile, url, "d", 0))
Print #fnum, spSite
Print #fnum, Spaces & spDir
NavigateFolder spSite, spDir, url, 0
Close #fnum
​

End Sub

Sub NavigateFolder(spSite As String, spDir As String, url As String, level As Integer)

Dim davDir As New ADODB.Record
Dim davFile As New ADODB.Record
Dim davFiles As New ADODB.Recordset
Dim isDir As Boolean
Dim tempURL As String
On Error GoTo showErr
tempURL = "URL=" & url
davDir.Open "", _

tempURL, _
adModeReadWrite, _
adFailIfNotExists, _
adDelayFetchStream ', _
' "username", _
' "password"
​

If davDir.RecordType = adCollectionRecord Then

Set davFiles = davDir.GetChildren()
Do While Not davFiles.EOF

' davFile.Open davFiles, , adModeReadWrite
davFile.Open davFiles, , adModeRead
isDir = davFile.Fields("RESOURCE_ISCOLLECTION").Value
If Not isDir Then

spFile = Replace(davFile.Fields("RESOURCE_PARSENAME").Value, "%20", " ")
url = spSite & spDir & "/" & spFile
Stack.Add (Array(spSite, spDir, spFile, url, "f", level))
PrintLine = ""
For i = 1 To level + 1

PrintLine = PrintLine & Spaces
​

Next i
Print #fnum, PrintLine & spFile
​

Else

level = level + 1
url = Replace(davFile.Fields("RESOURCE_ABSOLUTEPARSENAME").Value, "%20", " ")
spDir = Right(url, Len(url) - Len(spSite))
Stack.Add (Array(spSite, spDir, "", url, "d", level))
PrintLine = ""
For i = 1 To level

PrintLine = PrintLine & Spaces
​

Next i
Print #fnum, PrintLine & "/" & spDir
NavigateFolder spSite, spDir, url, level
level = level - 1
​

End If
davFile.Close
davFiles.MoveNext
​

Loop
​

End If
Set davFiles = Nothing
davDir.Close
Set davDir = Nothing
' Do something with files here...

GoTo noErr
showErr:
Call MsgBox(Err.Number & ": " & Err.Description & Chr(10) _
& "spSite=" & spSite & Chr(10) _
& "spDir= " & spDir & Chr(10) _
& "spFile=" & spFile, vbOKOnly, "Error")

noErr:
' No errors
​

End Sub


Sorry about the poor formatting, but I'm not an expert on using this site either.

 

[MikeDBMan]

Wow, dude. That is really awesome! You can't imagine how this will help me. I owe you big time!

 

[pczegle]

Wow, dude. That is really awesome! You can't imagine how this will help me. I owe you big time!

Wow, dude too! You made my day by letting me know.

 

[pczegle]

The following VBA code produces a folder/file tree of the specified Sharepoint Site. Unlike the SharedWorkspace.Files method, it does NOT restrict the number of files & folders to 99 in order to produce a list. It requires using the Microsoft ActiveX Data Objects 2.x Library (I used 2.8) . If it's NOT installed, research the web on how to install it. If it is installed, activate it by selecting "Tools" and then "References" in the VB editor/debugger. Then click on the checkbox for this library.

Code was borrowed and adapted from several places:
1 - http://blog.itwarlocks.com/2009/04/28/accessing-webdav-in-microsoft-word-visual-basic/
author Jeffrey Ridout provided the basic code to use the webDav methodology
2 - http://vbautomation.110mb.com/ACCESS/ADO/ADO_GetChildren Method.htm
provided code to restrict the contents of subfolders to where davDir.RecordType = adCollectionRecord to potentially avoid error code 3001 when using the GetChildren method.

The only thing I did was to modify the code to produce the tree listing in recursive fashion to handle all the nested subfolders.

Please note that I am not an Excel VBA guru. If you want to improve this code, that's great!

I hope this helps you.

Public Stack As New Collection
Public PrintLine As String
Public Spaces As String
Public fnum As Integer
Public outputFile As String
'
' Stack provides a means of storing the folder/file tree for followup processing, if necessary.
' It's not used actively by this program. Please note that the VB editor/debugger will only
' display the first 256 Stack entries even though there are potentially many more.
'
Sub NavigateSharepointSite()

'
' outputFile is where the folder/file tree is written
' spSite is the Sharepoint Site for which the folder/file tree is produced
'
On Error Resume Next
Dim spSite As String, spDir As String, spFile As String, url As String
fnum = FreeFile()
outputFile = "c:\your directory\Tree.txt"
Open outputFile For Output As fnum
Spaces = " "
spSite = "https://your Sharepoint site/"
spDir = ""
spFile = ""
url = spSite & spDir & spFile
Stack.Add (Array(spSite, spDir, spFile, url, "d", 0))
Print #fnum, spSite
Print #fnum, Spaces & spDir
NavigateFolder spSite, spDir, url, 0
Close #fnum
​

End Sub

Sub NavigateFolder(spSite As String, spDir As String, url As String, level As Integer)

Dim davDir As New ADODB.Record
Dim davFile As New ADODB.Record
Dim davFiles As New ADODB.Recordset
Dim isDir As Boolean
Dim tempURL As String
On Error GoTo showErr
tempURL = "URL=" & url
davDir.Open "", _

tempURL, _
adModeReadWrite, _
adFailIfNotExists, _
adDelayFetchStream ', _
' "username", _
' "password"
​

If davDir.RecordType = adCollectionRecord Then

Set davFiles = davDir.GetChildren()
Do While Not davFiles.EOF

' davFile.Open davFiles, , adModeReadWrite
davFile.Open davFiles, , adModeRead
isDir = davFile.Fields("RESOURCE_ISCOLLECTION").Value
If Not isDir Then

spFile = Replace(davFile.Fields("RESOURCE_PARSENAME").Value, "%20", " ")
url = spSite & spDir & "/" & spFile
Stack.Add (Array(spSite, spDir, spFile, url, "f", level))
PrintLine = ""
For i = 1 To level + 1

PrintLine = PrintLine & Spaces
​

Next i
Print #fnum, PrintLine & spFile
​

Else

level = level + 1
url = Replace(davFile.Fields("RESOURCE_ABSOLUTEPARSENAME").Value, "%20", " ")
spDir = Right(url, Len(url) - Len(spSite))
Stack.Add (Array(spSite, spDir, "", url, "d", level))
PrintLine = ""
For i = 1 To level

PrintLine = PrintLine & Spaces
​

Next i
Print #fnum, PrintLine & "/" & spDir
NavigateFolder spSite, spDir, url, level
level = level - 1
​

End If
davFile.Close
davFiles.MoveNext
​

Loop
​

End If
Set davFiles = Nothing
davDir.Close
Set davDir = Nothing
' Do something with files here...

GoTo noErr
showErr:
Call MsgBox(Err.Number & ": " & Err.Description & Chr(10) _
& "spSite=" & spSite & Chr(10) _
& "spDir= " & spDir & Chr(10) _
& "spFile=" & spFile, vbOKOnly, "Error")

noErr:
' No errors
​

End Sub


Sorry about the poor formatting, but I'm not an expert on using this site either.

Please be aware that depending on how you implement this code, you might experience error codes or silently omitted subfolders if you have entitlement issues with any Sharepoint subfolder in the hierarchy. To avoid these errors, make sure that you have the proper entitlements to ALL subfolders or else use appropriate error handling to ignore them.

 

[KenCriss]

For some reason I am getting an "Object variable or With block variable not set" error on this line.

Set swsFiles = wb.SharedWorkspace.Files

Anyone know why?