put all files in directory in listbox on userform

birdman

Board Regular
Joined
Oct 11, 2005
Messages
187
i have a macro which opens a userform. when the userform appears i would like all the files in a certain directory to appear in a listbox that is on the userform. does anyone know the code, and where to put the code to do this?? thanks for any help in advance.
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
Steve

This is certainly possible, and I'll try and find some appropriate code, but have you considered using the GetOpenFileName method?

Here's some code.
Code:
Private Sub UserForm_Initialize()
Dim I As Long

    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\"
        .FileType = msoFileTypeExcelWorkbooks
        .Execute
        For I = 1 To .FoundFiles.Count
            ListBox1.AddItem .FoundFiles(I)
        Next I
    End With
End Sub
 

birdman

Board Regular
Joined
Oct 11, 2005
Messages
187
i am currently using the GetOpenFileName method. i wish to change to this so that when the userform opens, all the files in the folder are shown in the listbox without having to deal with selecting the files.
 

birdman

Board Regular
Joined
Oct 11, 2005
Messages
187
i notice it says
Code:
.FileType = msoFileTypeExcelWorkbooks
every file in the folder is going to be a text file. i am not sure to put in replace of this line.

thanks for all your help by the way
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
Maybe this?
Code:
Private Sub UserForm_Initialize()
Dim I As Long

    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\"
        .FileType = msoFileTypeAllFiles
        .Filename = "*.txt" ' assumes the text files have the txt extension
        .Execute
        For I = 1 To .FoundFiles.Count
            ListBox1.AddItem .FoundFiles(I)
        Next I
    End With
End Sub
By the way why do you not want to use the GetOpenFileName method?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,037
Messages
5,569,791
Members
412,292
Latest member
The Bear named Joe
Top