Consulting

Results 1 to 1 of 1

Thread: Excel: Creating a detailed list of all files in a SharePoint folder

  1. #1
    VBAX Regular
    Joined
    Feb 2021
    Posts
    21
    Location

    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:

    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
    Last edited by Aussiebear; 12-28-2024 at 12:08 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •