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
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
