Excel: Creating a detailed list of all files in a SharePoint folder
Hi everyone.
I need a code that can go into a specific folder in SharePoint and then create a list of all files in this folder, together with a direct link or Path to that file. I also need the date and time for when it last was modified.
So I need an output like this:
| FileName |
FilePath (or Link) |
Date and Time |
| Example.xlsx |
https://company.sharepoint.com/...etc. |
12-06-2021 17:39:40 |
| Example2.xlsx |
https://company.sharepoint.com/...etc. |
13-06-2021 15:09:12 |
Today (not in SharePoint) I use the following code to create the list:
Code:
Sub ListFiles()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer
List.Activate 'My sheet for the list
Range("AndreFiler").Activate 'Where I have my list where I check for postings.
A = 0
For A = 1 To 100
If ActiveCell.Value = "" Then Exit For
With Xmarked(A)
.Navn = ActiveCell.Value
.Bogfør = ActiveCell.Offset(0, -1).Value
.Bogført = ActiveCell.Offset(0, 3).Value
End With
ActiveCell.Offset(1, 0).Activate
Next A
Range("OtherArea").ClearContents 'clear the list area
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(Range("FilePath"))
i = 1
For Each objFile In objFolder.Files
If objFile.Name = "Thumbs.db" Then GoTo NextIteration
Cells(i + 4, 13) = objFile.Name
Cells(i + 4, 14) = objFile.Path
Cells(i + 4, 15) = FileDateTime(objFile.Path)
i = i + 1
NextIteration:
Next objFile
Range("AndreFiler").Activate 'recreate the list (to check if the files have been updated/posted)
c = 0
For c = 1 To 100
If ActiveCell.Value = "" Then Exit For
b = 0
For b = 1 To 100
If Xmarked(b).Bogfør = "x" Or Xmarked(b).Bogført = "x" Then
If ActiveCell.Value = Xmarked(b).Navn Then
ActiveCell.Offset(0, -1).Value = Xmarked(b).Bogfør
ActiveCell.Offset(0, 3).Value = Xmarked(b).Bogført
End If
End If
Next b
ActiveCell.Offset(1, 0).Activate
Next c
End Sub
Does anyone have a code that can do the same thing with folders in SharePoint?
Best regards
Rasmus
