About Us Tutorials Downloads Twitter Contact Us
{Search}
{Cart}

Listing Directory contents in a listbox / combo list

        9 votes: *****     31,925 views      2 comments
by Allen Browne, 20 April 2005    (for ALL VERSIONS of Access)

Microsoft Access: VBA Programming Code

Provided by allenbrowne.com, updated June 2006

DirListBox() function

This article describes an old technique of filling a list box via a callback function.
In Access 2000 and later, there is a newer technique that is more efficient and flexible.

To use the callback function:

  1. Create a new module, by clicking the Modules tab of the Database window, and clicking New.
  2. Paste in the code below.
  3. Check that Access understands the code by choosing Compile on the Debug menu.
  4. Save the module with a name such as Module1.
  5. Set the Row Source Type property of your list box to just:
        DirListBox
    Do not use the equal sign or function brackets, and leave the Row Source property blank.

The code

Function DirListBox (fld As Control, ID, row, col, code)
    ' Purpose:    To read the contents of a directory into a ListBox.
    ' Usage:      Create a ListBox. Set its RowSourceType to "DirListBox"
    ' Parameters: The arguments are provided by Access itself.
    ' Notes:      You could read a FileSpec from an underlying form.
    '             Error handling not shown. More than 512 files not handled.
    Dim StrFileName As String
    Static StrFiles(0 To 511) As String ' Array to hold File Names
    Static IntCount As Integer          ' Number of Files in list

    Select Case code
        Case 0                          ' Initialize
            DirListBox = True

        Case 1                          ' Open: load file names into array
            DirListBox = Timer
            StrFileName = Dir$("C:\")   ' Read filespec from a form here???
            Do While Len(StrFileName) > 0
                StrFiles(IntCount) = StrFileName
                StrFileName = Dir
                IntCount = IntCount + 1
            Loop

        Case 3                          ' Rows
            DirListBox = IntCount

        Case 4                          ' Columns
            DirListBox = 1

        Case 5                          ' Column width in twips
            DirListBox = 1440

        Case 6                          ' Supply data
            DirListBox = StrFiles(row)

    End Select
End Function
Home Index of tips Top

Rate this article:  Your rating: PoorYour rating: Not so goodYour rating: AverageYour rating: GoodYour rating: Excellent

Give me more tutorials!

This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Listing Directory contents in a listbox / combo list'?


1.

Cate says...

01 Apr 2008

 
Nice, simple piece of code.
Any suggestions for sorting these files?

2.

Wayne Phillips says...

01 Apr 2008

 
Cate,

This is not particularly efficient, but this code will do the trick:

'This Code Developed by Chris Vann
Public Function SortArray(ByRef TheArray As Variant, ByVal NumItems As Integer)
    Dim Temp as Variant, X as Integer
    Sorted = False
    Do While Not Sorted
        Sorted = True
        For X = 0 To NumItems - 1
            If TheArray(X) > TheArray(X + 1) Then
                Temp = TheArray(X + 1)
                TheArray(X + 1) = TheArray(X)
                TheArray(X) = Temp
                Sorted = False
            End If
        Next X
    Loop
End Function

(code adapted from http://www.freevbcode.com/ShowCode.asp?ID=3197)

Then, just after the do loop in Allen's code, put:

Call SortArray(StrFiles, IntCount-1)

Have your say...

Name
E-mail (e-mail address will be kept private)
Comments

Comments require approval before being displayed on this page (allow 24 hours).

iTech Masters    |    VAT: GB202994606    |    Terms    |    Sitemap     |    Newsletter