Populate Spreadsheet with files within a folder

Question

I need to manage a lot of files within folder related to a project, at the moment these files reside in Dropbox. So, I will like to find a way in which I can have a Spreadsheet populate itself with the names for the files within the folder, that way I can add columns with comments, dates, status, etc. related to these files, and can easily manage which have certain conditions which make ready for their next steps.

It might me the case that files are also added, or removed from the folder.

Is there a way to accomplish this through Excel or perhaps Google Spreadsheet? It is not a problem if the file repository is Dropbox, OneDrive, Google Drive, etc. as long as it has sharing option.

Thanks,

Answer 1

Two quick ways to get you started (You'll probably want to expand upon these to allow for easier updating).

Using CMD and the DIR command, you can quickly get yourself a base list of files you can start to work with. Use the following command:

dir /b > output.csv

Alternately (As you want to be able to update the list and work with it) I've put together a quick VBA example. Of course, there may be amendments you'll need to make, but here's a basis:

Sub PopulateRows()
    Dim objFSO, objFolder, colFiles, objFile, FindValue
    Set objFSO = CreateObject("Scripting.FileSystemObject")

    Set objFolder = objFSO.GetFolder("C:\Users\Jonno\Dropbox\Public")
    Set colFiles = objFolder.Files

    Dim curRow

    curRow = FindFirstEmptyRow

    For Each objFile In colFiles
        Set FindValue = Range("A:A").Find(objFile.Name)
        If FindValue Is Nothing Then
            Range("A" & curRow).Value = objFile.Name
            Range("B" & curRow).Value = objFile.Size
            Range("C" & curRow).Value = objFile.DateCreated
            Range("D" & curRow).Value = objFile.DateLastModified
            curRow = curRow + 1
        End If
    Next
End Sub

Function FindFirstEmptyRow()
    Dim curRow
    curRow = 1
    Do
        If IsEmpty(Range("A" & curRow).Value) Then
            FindFirstEmptyRow = curRow
            Exit Function
        End If
        curRow = curRow + 1
    Loop
End Function

That's the file name, size, creation date and modified date.

Note the VBA will add new files, but currently has no logic to remove items, so you may need to amend it if you use it.

Not sure if this will be of use to you, but hopefully gives you some ideas.