How to traverse folders and files on SharePoint with Excel VBA

pczegle

New Member
Joined
Nov 24, 2010
Messages
24
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

MikeDBMan

Well-known Member
Joined
Nov 10, 2010
Messages
591
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

New Member
Joined
Nov 24, 2010
Messages
24
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

New Member
Joined
Nov 24, 2010
Messages
24
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

New Member
Joined
Nov 24, 2010
Messages
24

ADVERTISEMENT

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

New Member
Joined
Nov 24, 2010
Messages
24
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

Well-known Member
Joined
Nov 10, 2010
Messages
591

ADVERTISEMENT

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

pczegle

New Member
Joined
Nov 24, 2010
Messages
24
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

Active Member
Joined
Jun 6, 2005
Messages
326
For some reason I am getting an "Object variable or With block variable not set" error on this line.

Code:
Set swsFiles = wb.SharedWorkspace.Files

Anyone know why?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,816
Messages
5,638,503
Members
417,030
Latest member
baqer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top