VBA code to display directory files with FSO

[cmmrfrds]

Does anybody have a snippet of vba code to return the file names in a directory to a recordset object. Is there a way to use FSO (file scripting object) to create a recordset of file names. I ultimately want to display the names in a listbox or combobox.

 

[susanhawk]

Try this out.

Dim stNameArray() As String, Name as String
Dim Total as Integer, i as Integer
Total = 0

Name = G:\Applications\ 'whatever directory

'Get total number of files to set array upper bound
FileName = Dir(Name)
Do
Total = Total + 1
FileName = Dir()
Loop Until FileName = ""
ReDim stNameArray(1 To Total)
FileName = Dir(Name)
'Load Array
For i = 1 To Total
If UBound(stNameArray) >= LBound(stNameArray) Then
ReDim Preserve stNameArray(1 To i)
stNameArray(i) = FileName
FileName = Dir()
End If
Next
Dim putname As String
'used to demo - you will put into your recordset field
For i = 1 To Total
putname = stNameArray(i)
Next

Hope this will help.

 

[cmmrfrds]

Thank you susanhawk, my main problem is loading from the recordset into the listbox. I do not want to be constrained by the length of the string data that can be used as a source for the listbox, which is slightly over 2000 characters. The recordset can be opened ended and I prefer that as the solution, but do not know how to load into the listbox.

What is the source for the listbox using the following recordset.
Dim rs new adodb.recordset

list0.rowsourcetype = ???? '- for a recordset
list0.rowsource = ????

 

[BillPower]

This is one way to add a Recordset to a Combo or List Box, by creating a temporary table, in the following example all files in c:\my documents and sub folders will be added to a combo/list box called MyList.

Paste the following into your form's declaration section:

Dim strMyCombo As String

Paste this into the form's module:

Sub CreateMyRowsource()
Dim dbs As Database, tdf As TableDef, Fld As Field
Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef(strMyCombo)
With tdf
.Fields.Append .CreateField("Field1", dbText)
End With
dbs.TableDefs.Append tdf
dbs.Close
End Sub

This into the form's unload event:

On Error Resume Next
DoCmd.DeleteObject acTable, strMyCombo 'delete temp table

This into a button's on click event on your form to test it:

Dim dbs As Database, rst As Recordset
Dim i As Long, strFoundRef As String
On Error Resume Next
strMyCombo = Me.Name & Me!MyList.Name 'unique name for temp table
Me!MyList.RowSource = "" 'set your list / combo rowsourcw to nothing
DoCmd.DeleteObject acTable, strMyCombo 'delete temp table
CreateMyRowsource 'create temp table
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strMyCombo, dbOpenDynaset) 'set recordset = temp table
With Application.FileSearch
.LookIn = "c:\My Documents"
.Filename = "*.*"
.SearchSubFolders = True
If .Execute() > 0 Then
For i = 1 To .foundfiles.Count
rst.AddNew
rst!Field1 = .foundfiles(i)
rst.Update
Next i
End If
End With
Me!MyList.RowSourceType = "Table/Query"
'set your list / combo to temp table sorted by Field1
Me!MyList.RowSource = "SELECT Field1 FROM " & strMyCombo & " ORDER BY Field1;"
rst.Close

Obviously, rename MyList with your Combo/List name. In Access this has been the only way I've found to get around the 2048 character limit using a Value List with a recordset.