Populate listbox with all .xls filenames in a specified folder? 1

[XP2000]

Any suggestions as to how to go about populating a listbox with the names of all the files in a specified folder:
("C:\Dropbox&quot that have the file extension .xls ?

Many Thanks,
Neil

 

[TrojanRabbit]

Here's one method:

(Requires Microsoft Scripting Runtime reference.)

Dim fso As FileSystemObject
Dim fl As File
Dim str as String
Set fso = CreateObject("Scripting.FileSystemObject&quot
For Each fl In fso.GetFolder("C:\Dropbox&quot.Files
If Right(fl.Name, 3) = "xls" Then
str = Left(fl.Name, InStr(1, fl.Name, ".&quot - 1)
If InStr(1, Form_what.Combo9.RowSource, str) Then
Else
Form_<frmname>.<listboxctrl>.RowSource = Form_<frmname>.<listboxctrl>.RowSource & &quot;;&quot;&quot;&quot; & str & &quot;&quot;&quot;&quot;
End If
End If
Next fl

'If Not Instr(...) Then' didn't work. That's why I said 'If Instr(...) Then Else'.

 

[oharab]

FWIW: The reason the not instr() didn't work is because instr returns an integer of the position of the searched for text. If it doesn't find it then it returns 0.

Ben ----------------------------------
Ben O'Hara
bo104@westyorkshire.pnn.police.uk
----------------------------------

 

[XP2000]

Great help thanks,
Neil

 

[TrojanRabbit]

I just realized a potential bug. The code adds the filename to the source only if the name isn't already somewhere in the source. If one filename exists in other filenames, it may not be added. For example, consider this.xls and thisone.xls. If &quot;thisone&quot; is already in the source, &quot;this&quot; won't be added. Potential problem depending on filenames. Just letting you know.

 

[TrojanRabbit]

This should avoid the problem:
If InStr(1, Form_<frmname>.<listboxctrl>.RowSource, &quot;&quot;&quot;&quot; & str & &quot;&quot;&quot;&quot Then
Else

 

[GsyDonkey]

This code is a little more cunning, and easier to use.
rootPath is just used in order to use the method in conjunction with the 'OnClick' method of the listbox...

Dim rootPath as string

Private Sub UserForm_Activate()
rootPath = &quot;C:\Dropbox\&quot;
dirName = Dir(rootPath & &quot;*.xls&quot;, vbNormal)
While dirName <> &quot;&quot;
Me.listBox1.AddItem dirName
dirName = Dir()
Wend
End Sub

Hope this helps (and you're still looking at this post!)