How to create dynamic object list with files properties in excel in VBA

Question

I created documentary list in Excel 2016 for a personal project, it can sort files and remove old versions, create a clickable link to the new version... I want to update code for better reliability and visibility.

I don't how to create a dynamic list of objects with files properties (size, name, path, creation date...), ex : have these properties(size, name, path...) I add a piece of my old code.

I tried these tutorials but isn't working or I don't understand :

Dim TblFichiers() As String
Dim File As String
Dim I As Integer

If Right(Chemin, Len(Chemin)) <> "\" Then Chemin = Chemin & "\"
File = Dir(Chemin & "*" & Ext & "*")

Do While (Len(File ) > 0)
    I = I + 1
    ReDim Preserve TblFichiers(1 To I)
    TblFichiers(I) = File 
    File = Dir()
Loop

Getfiles= TblFichiers() 'Getfiles is main function

I know how to do this in C ++ or C# but I have difficulty understanding how it works in VBA.

You need to define a Class which groups the variables you want to record. You need to create new instances of the class for each record. Add the record to a Collection or Scripting.Dictionary. Scripting Dictionaries offer a number of advantages to Collections. — freeflow
– freeflow, Commented Apr 8, 2019 at 9:17
What’s “weight” in relation to the file? What does that attribute represent? Are you meaning size? — Skin
– Skin, Commented Apr 8, 2019 at 9:55

Skin · Accepted Answer · 2019-04-08 11:28:26Z

1

I think you'd be best to look at the Microsoft Scripting Runtime library ..

Public Sub TraverseFiles()
    Dim strFolder As String

    Dim objFSO As Scripting.FileSystemObject
    Dim objFolder As Scripting.Folder
    Dim objFile As Scripting.File

    strFolder = "c:\temp"

    Set objFSO = New Scripting.FileSystemObject
    Set objFolder = objFSO.GetFolder(strFolder)

    For Each objFile In objFolder.Files
        Debug.Print objFile.Name
        Debug.Print objFile.Path
        Debug.Print objFile.Size
        Debug.Print objFile.DateCreated
    Next
End Sub

... it'll give you what you want regarding attributes about files and operations to manage those files.

You just need to adapt it your project.

I hope that works for you.

edited Apr 8, 2019 at 11:28

answered Apr 8, 2019 at 11:14

Skin

11.7k3 gold badges16 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bensuperpc Over a year ago

Thank you ! I have a last question : how i can access to attributes per files, ex : "MsgBox objFolder.Files.Item(0).Path" without For Each ?

Skin Over a year ago

@Bensuperpc, using a for loop doesn't work too well. For some reason using the index of the file in the collection produces an error, it's obviously just the way Microsoft have written the library. If you need some sort of index, just increment a variable through each loop, does that help?

Collectives™ on Stack Overflow

How to create dynamic object list with files properties in excel in VBA

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related