Cart    Contact Us    Tutorials    Downloads    About Us    News

MDE to MDB Conversion Service
(plus ACCDE to ACCDB Conversion)

- convert compiled Access files back to their original formats including VBA code

Database Repair Service

- a trusted service for repairing corrupt Microsoft Access files

vbWatchdog   [Code Library]

- global error handling library for VBA & VB6 projects

vbMAPI   [Code Library]

- Outlook/MAPI library for VBA, .NET and C# projects

Code Protector   [Software]

- strengthens protection against reverse engineering of MDE/ACCDE files


Listing Directory contents in a listbox / combo list

        9 votes: ***       28,716 views      2 comments
by Allen Browne, 20 April 2005    (All versions of Access)

Microsoft Access: VBA Programming Code

Provided by Allen Browne, 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

<< Back to tutorials index




Please Note: This is a cached tutorial (reproduced with permission).
To view the tutorial at its original location click here.



Rate this article:   Your rating: Poor Your rating: Not so good Your rating: Average Your rating: Good Your rating: Excellent

Have your say - comment on this article.

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


1.

Cate says...

01 April 2008

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

2.

Wayne Phillips says...

01 April 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
Verify Code
Verification Code
Please note: It may take 30 seconds after clicking submit - please be patient to avoid duplicate submissions.   All comments must be approved before being displayed on this web page.  This process may take up to 24 hours.


Site tools