Populating a User Form Text Box with a list of file names

[TheShaunMichael]

Hello All,

What is the best way to populate a text box with a comma separated list of file names at a directory?

Can someone show me what an array might look like for an in determinant number of file names (1 to x) perhaps?)

Shaun

 

[Domenic]

To list the filenames within a specified directory, I'd suggest using either a ComboBox or ListBox. For example, to list them in a ComboBox, assuming that you'd like the code to be used as the event handler for a CommandButton, maybe something like this...

[font=Courier New][color=darkblue]Private[/color] [color=darkblue]Sub[/color] CommandButton1_Click()

    [color=darkblue]Dim[/color] MyPath [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] MyFile [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] FileNames() [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] Cnt [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    MyPath = "C:\Users\Domenic\Desktop\" [color=green]'change the path accordingly[/color]
    
    [color=darkblue]If[/color] Right(MyPath, 1) <> "\" [color=darkblue]Then[/color] MyPath = MyPath & "\"
    
    MyFile = Dir(MyPath & "*.xlsm", vbNormal) [color=green]'filter for .xlsm files[/color]
    
    Cnt = 0
    [color=darkblue]Do[/color] [color=darkblue]While[/color] Len(MyFile) > 0
        Cnt = Cnt + 1
        [color=darkblue]ReDim[/color] [color=darkblue]Preserve[/color] FileNames(1 [color=darkblue]To[/color] Cnt)
        FileNames(Cnt) = MyFile
        MyFile = Dir
    [color=darkblue]Loop[/color]
    
    [color=darkblue]If[/color] Cnt > 0 [color=darkblue]Then[/color]
        Me.ComboBox1.List = FileNames
    [color=darkblue]Else[/color]
        MsgBox "No files were found...", vbInformation
    [color=darkblue]End[/color] [color=darkblue]If[/color]

End [color=darkblue]Sub[/color]
[/font]