PDA

View Full Version : Solved: Populate listbox based on folder contents



ukdane
02-26-2010, 03:59 AM
Hi,
I'm looking for a quik fix code that will populate a listbox (called LSTFiles) with the names of all EXCEL files within three different folders.
I'm guessing I can just loop the same code for each of the folders, but what do I use to get the names of each excel file, and then populate it to the listbox.

Thanks in advance for your help.

Edit: When the user then selects one of the files in the listbox, I then need to code so that another listbox shows the names of all the worksheets within the selected workbook.

ukdane
02-26-2010, 04:15 AM
Sorry for the trouble, I've solved this.
Private Sub UserForm_Activate()
Dim DIRECTORY As String
'Clear Listboxes
LstFiles.Clear
LstWorksheets.Clear

'list directories
DIRECTORY = Dir("c:\myfolder\*.xls", vbNormal)
Do Until DIRECTORY = ""
'add filename to listbox
LstFiles.AddItem DIRECTORY
DIRECTORY = Dir()
Loop

End Sub

ukdane
02-26-2010, 05:07 AM
Following on from this, although the Listbox is correctly populated with the file names, I need someway to store the correct directory for each of the workbook names, so that it can later open the workbook to get the worksheet names. The code above does not provide this. Any ideas how to achive this?
Is it possible to trace the directory of a file, based only on it's filename, without searching all folders for it?

SamT
02-26-2010, 07:16 AM
Add a public Variable, MyFile to the declarations of your Form Code Page, then set its value with


Private Sub lstFiles_Click()
MyFile = lstFiles.Value
End Sub