'Serving the Microsoft® Access
Community since 1997'

Free Samples
About Us



Choosing a File from a List

I built an application recently that needed to present the user with a list of Excel file templates to choose from. The program then filled in the necessary data, using techniques from my "Sending Data to Excel" series (coming soon!).

(article continues after sponsor spot)

The list of available files was presented in a list box, and the user clicked on the one they wanted.

The steps I took were:

  • create a temporary table to hold the list of files (one time process)
  • empty the temp table
  • fill the table using the Dir function
  • requery the Listbox that shows the files

The first time you use the Dir function in Access VBA, it requires a file name (or wildcard version) as well as the folder (directory) path. If using a wildcard, then the subsequent calls to Dir will return the next file that matches. When there are no more files, the function returns an empty string.

Here is the code that I used.

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

Dim MyFile As String
Dim rst As Recordset
Dim db As Database

Set db = CurrentDb
Set rst = db.OpenRecordset("select * from [_tmpFileNames]")

db.Execute ("delete * from [_tmpFileNames]")

'--- Returns first file from list of available files
MyFile = Dir("C:\MyExcelTemplates\*.xls")

'--- add it to the temp table
rst!Filename = MyFile

'--- Call Dir again without arguments to return the next file
Do While MyFile <> ""     '--- do until no more files
   MyFile = Dir              '--- Get next entry.
   If MyFile <> "" Then
      rst!Filename = MyFile
   End If

Me.lstFileNames.Requery    '--- refresh list of file names in list box

Further Suggestions:

In the original application that I created, the templates are actually in the same folder as the database, so I have VBA code that determines the name of the folder and use that instead of the hard-coded "C:\MyExcelTemplates\".

Happy Coding

Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.


© 2006, 2007, 2008 Richard W. Killey. All Rights Reserved. - Privacy Policy